3
It seems you need in simple
SELECT *, SUM(debit_balance - credit_balance)
OVER (PARTITION BY account_no ORDER BY id) balance
FROM acc_account_transactions
fiddle
3
SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID
UPDATED: index on ArticleID may help to improve query performance
3
Another solution is one table per quantity.
+-----------+------------+-----------+
| device ID | date | quantity1 |
+-----------+------------+-----------+
| A | 2020/06/03 | 10 |
+-----------+------------+-----------+
+-----------+------------+-----------+
| device ID | date | quantity2 |
+-----------+------------+-----------+
| ...
2
Short answer: It's 2020, the case for spinning rust is super-weak.
Long answer: It depends on how much memory you have and what your read:write ratio is.
If the workload is mostly reads, and you have enough buffer pool to cache all of the even remotely hot data, disk speed won't matter as much because once the buffer pool is warmed up, not much data will ...
2
Note: Try to install MySQL same version, if your crashed MySQL version was 5.6, please install MySQL 5.6. (brew install mysql@5.6). If following process fails try the same things with MariaDB. (In my case, by MariaDB, I get back all database)
Process 1
Before start, must stop MySQL or MariaDB. Use this command (brew services stop mysql), For MariaDB brew ...
2
If I understand correctly, you have hundreds of measuring devices, and you're contemplating having a table for each one, or one table for all -- is that correct?
And for the one-table solution, you're thinking of a design something like this:
+-----------+------------+-----------+-----------+-----------+-----------+-----------+
| device ID | date | ...
1
Database schema and data
CREATE TABLE `Students` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(32) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Students` (`ID`, `Name`) VALUES
(1, 'Ashley'),
(2, 'Samantha'),
(3, 'Julia'),
(4, 'Scarlet');
CREATE TABLE `Friends` (
`ID` int(10) unsigned NOT NULL ...
1
WITH
cte1 AS ( SELECT UDF1(0, a, b, c, d) AS Foo1,
UDF1(1, a, b, c, d) AS Foo2,
UDF1(2, a, b, c, d) AS Foo3,
UDF1(3, a, b, c, d) AS Foo4,
x, y, z
FROM MyTable ),
cte2 AS ( SELECT Foo1, Foo2, Foo3, Foo4, x, y, z,
UDF2(Foo1, Foo2, Foo3, Foo4) AS Foo5,
...
1
1 IN (a,b) won't scale. It will do a full table scan. This will make use of indexes:
( SELECT id, receiver_id FROM messages WHERE sender_id = 1 LIMIT 3 )
UNION ALL
( SELECT id, sender_id FROM messages WHERE receiver_id = 1 LIMIT 3 )
LIMIT 3
together with both
INDEX(sender_id, receiver_id, id),
INDEX(receiver_id, sender_id, id)
Now, assuming you ...
1
Your query was only missing the ability to reset @running_balance. You can only reset @running_balance when the row has a different account_no from the previous row.
With this in mind, I give you (drum roll please) ...
PROPOSED QUERY
SELECT account_no,debits,credits,balance FROM
(SELECT
account_no
,COALESCE(debit_balance) as debits
,COALESCE(...
1
I notice that when I accidentally put ' in it ... the console seems to stuck there
It's not "stuck" at all.
It's patiently waiting for you to supply the closing quote for the string (or date) literal that you started when you typed in the opening quote! It even goes as far as to give you a "hint" that it's doing so - notice the different prompt it ...
1
How about this :
select t1.key
from table1 t1
inner join table1 t2
on t1.key = t2.key || 'P'
where t1.key like '%P'
order by t1.key ;
Querying t1 gives you a list of all of the rows with keys that end in 'P'.
The join to t2 is based on rows where t2's key, plus a trailing 'P', matches the key in t1 (or, alternatively, the original key ...
1
What you want is called an UPDATE JOIN
First you must create a query that has the MAX(b_foo) for every a_uid:
SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid;
Making this a subquery, you can perform the UPDATE JOIN as follows:
UPDATE A INNER JOIN
(SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
ON A.id = C.a_uid SET A.foo = C.max_b_foo;...
1
The syntax for CAST() is CAST(expr AS type [ARRAY]).
You got the syntax right but you were unable to do the cast because TINYINT is not among the list of permitted values for 'type' in MySQL.
Casting to INT works in MariaDB but it doesn't in MySQL.
MySQL only allow any one of the following to be used as value for 'type':
- BINARY.
- CHAR
- DATE
- DATETIME
- ...
1
For a closer analysis we would need the table definitions for both tables and indexes as well as the row counts, but I think the problem is that you use key2 for joining and mysql can then not use it for filtering of the datetime (since the first part of the index, the key2, is different for the rows in the join result).
Can you try to create an index on ...
1
SELECT t1.run_name, hardware_used, COUNT(*) times_used
FROM hardware t1
JOIN hardware t2 USING (hardware_used)
WHERE t1.run_name >= t2.run_name
GROUP BY t1.run_name, hardware_used
ORDER BY t1.run_name;
fiddle
1
Testing out $tompave's Comment; it seems to work:
First, create and populate:
mysql> CREATE TABLE test_uuid (
-> my_uuid BINARY(16)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO test_uuid (my_uuid) VALUES (UUID_TO_BIN(UUID()));
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test_uuid (...
1
In SQL Server, I would store this as an int, and use CRYPT_GEN_RANDOM(4) to generate cryptographically random numbers for each row.
Here's an example:
DECLARE @c int;
DECLARE @c_base64 varchar(8);
DECLARE @c_converted int;
--get the cryptographically random integer
SELECT @c = CONVERT(int, crypt_gen_random(4));
--convert it into a base-64 string
DECLARE ...
1
You use a subselect query as basis for t1 and t2
Like
SELECT
t1.TagID AS TagID1, t2.TagID AS TagID2, COUNT(1)
FROM
(SELECT
*
FROM
TagMap
WHERE
TagID IN (SELECT
TagID
FROM
Tags
WHERE
Articles > 50)) AS t1
JOIN
(SELECT
*
...
1
You could use the window function row_number, like so:
select id, collection, name from (
select id, collection, name,
row_number() over (partition by collection order by id) rn
from stac_item
) t
where rn < 3
order by id
1
Unclear.
What does drink_no signify?
You should not try to maintain a "running count" of how many drinks a person has consumed on a given day. Instead, simply store that they have consumed another drink and let your queries do the "adding up":
select
drinker
, odate
, count( odate ) total
from table1
group by drinker, odate
order by drinker, odate ; ...
Only top voted, non community-wiki answers of a minimum length are eligible
