gpt4 book ai didi

mysql order by SUM(column) group'd by another column

转载 作者:行者123 更新时间:2023-11-29 05:53:18 26 4
gpt4 key购买 nike

我有以下数据库表:

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)

我想创建一个查询,按 SUM(col3) GROUP 在 col1 上对行进行排序。然后我需要订购 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+ 或更高版本,那么我们可以尝试在 ORDER BY 子句中使用 SUM 作为解析函数:

SELECT *
FROM table1
ORDER BY
SUM(col3) OVER (PARTITION BY col1) DESC,
col1,
col3 DESC;

enter image description here

Demo

关于mysql order by SUM(column) group'd by another column,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52151970/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com