我有以下数据库表:
create table table1 (
col1 VARCHAR(5) NOT NULL,
col2 VARCHAR(5) NOT NULL,
col3 TINYINT NOT NULL,
col4 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO table1 VALUES
('b','c',1,NULL),
('b','d',2,NULL),
('a','e',1,NULL),
('a','f',3,NULL);
mysql> select * from table1;
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| b | c | 1 | 1 |
| b | d | 2 | 2 |
| a | e | 1 | 3 |
| a | f | 3 | 4 |
+------+------+------+------+
4 rows in set (0.00 sec)
我想创建一个查询,该查询按col1上的SUM(col3)GROUP排序行。然后,我需要订购col3 DESC。
So final table will look like:
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| a | f | 3 | 4 |
| a | e | 1 | 3 |
| b | d | 2 | 2 |
| b | c | 1 | 1 |
+------+------+------+------+
i can get the SUM(col1):
mysql> select sum(col3) from table1 group by col1;
+-----------+
| sum(col3) |
+-----------+
| 4 |
| 3 |
+-----------+
但不确定如何进行。任何帮助表示赞赏。
一种选择是联接到一个查找总和的子查询:
SELECT t1.*
FROM table1 t1
INNER JOIN
(
SELECT col1, SUM(col3) AS col3_sum
FROM table1
GROUP BY col1
) t2
ON t1.col1 = t2.col1
ORDER BY
t2.col3_sum DESC,
t1.col1,
t1.col3 DESC;
如果您使用的是MySQL 8+或更高版本,则可以SUM
在以下ORDER BY
子句中尝试将其用作分析函数:
SELECT *
FROM table1
ORDER BY
SUM(col3) OVER (PARTITION BY col1) DESC,
col1,
col3 DESC;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句