gpt4 book ai didi

mysql - 获取所有行的总和

转载 作者:行者123 更新时间:2023-11-30 23:07:12 25 4
gpt4 key购买 nike

我有一个类似于下面的查询。我的目标是添加所有“total_points”并检索所有行总和的单个结果。

SELECT
SUM(
COALESCE(
CASE
WHEN COUNT(DISTINCT `table_1`.views) > 50 OR COUNT(DISTINCT `table_1`.views) = 50 AND COUNT(DISTINCT `table_1`.views) < 100 THEN COUNT(DISTINCT `table_1`.views) +5
ELSE COUNT(DISTINCT `table_1`.views)
END,0)
+

CASE
WHEN COUNT(DISTINCT `table_2`.views) > 50 OR COUNT(DISTINCT `table_2`.views) = 50 AND COUNT(DISTINCT `table_2`.views) < 100 THEN COUNT(DISTINCT `table_2`.views) +5
ELSE COUNT(DISTINCT `table_2`.views)
END,0)) AS sum

FROM `table_1`
LEFT JOIN `table_2`
ON `table_1`.id = `table_2`.id
GROUP BY `table_1`.primary_id

这会给我这样的结果

rank  | total_points
1 321
2 111
3 100
4 90
5 72
6 60
7 45
8 23
9 11
10 5

这是我想要的结果:

   sum   |
838

最佳答案

试试这个:

SELECT IFNULL(rank, 'Sum') rank, total_points
FROM (SELECT @rank := @rank + 1 AS rank, T1.total_points
FROM (SELECT COALESCE((CASE WHEN COUNT(DISTINCT t1.views) betwnn 50 AND 100 THEN COUNT(DISTINCT t1.views) + 5 ELSE COUNT(DISTINCT t1.views) END) +
(CASE WHEN COUNT(DISTINCT t2.views) betwnn 50 AND 100 THEN COUNT(DISTINCT t2.views) + 5 ELSE COUNT(DISTINCT t2.views) END), 0) AS total_points
FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.id = t2.id
GROUP BY t1.primary_id
ORDER BY total_points
) AS T1, (SELECT @rank := 0) AS r
ORDER BY total_points DESC
) AS A
GROUP BY rank WITH ROLLUP

关于mysql - 获取所有行的总和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21347808/

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