gpt4 book ai didi

mysql - MySQL中如何对子查询求和

转载 作者:行者123 更新时间:2023-11-29 09:27:04 25 4
gpt4 key购买 nike

我有以下完美运行的 MySQL 语句。不过,我现在需要如下粗体方括号/圆括号中指定的总数。

SELECT f.fid, r.tid, r.tbd, r.tbc, r.tc, r.tname, r.category,
SUM(`time`) AS `totaltime`,
SUM(`points`) AS `points`,
(SELECT IFNULL(SUM(points),0) FROM Primes WHERE zid = r.zid AND wid = r.wid AND s_id = 38) AS `cl`,
(SELECT IFNULL(SUM(points),0) FROM Primes WHERE zid = r.zid AND wid = r.wid AND s_id = 34) AS `sp`,

**[sum points + climb + sprint] AS pointstotal**

FROM teams f
JOIN results r
ON f.wid = r.wid
WHERE r.rank <= 3 AND r.id = '254293' AND r.category = 'C'
AND r.fin = '1'
GROUP BY f.fid
ORDER BY pointstotal DESC

我尝试了许多不同的排列等以及其他问题中的多个引用,但我就是无法让它发挥作用。

最佳答案

最简单的方法是将查询移动到子查询中,然后添加这些列。

SELECT fid, tid, tbd, tbc, tc, tname, category, totaltime, 
points, cl, sp, points + cl + sp AS pointstotal
FROM (
SELECT f.fid, r.tid, r.tbd, r.tbc, r.tc, r.tname, r.category,
SUM(`time`) AS `totaltime`,
SUM(`points`) AS `points`,
(SELECT SUM(points) FROM Primes WHERE zid = r.zid AND wid = r.wid AND s_id = 38) AS `cl`,
(SELECT SUM(points) FROM Primes WHERE zid = r.zid AND wid = r.wid AND s_id = 34) AS `sp`
FROM teams f
JOIN results r
ON f.wid = r.wid
WHERE r.rank <= 3 AND r.id = '254293' AND r.category = 'C'
AND r.fin = '1'
GROUP BY f.fid
) AS x
ORDER BY pointstotal DESC

但我通常不喜欢相关子查询,我更喜欢连接,然后你就不需要另一个级别了

SELECT SELECT f.fid, r.tid, r.tbd, r.tbc, r.tc, r.tname, r.category,
SUM(time) AS totaltime, SUM(points) AS points,
cl, sp, SUM(POINTS) + cl + sp AS totalpoints
FROM teams AS f
JOIN results AS r ON f.wid = r.wid
JOIN (
SELECT zid, wid, SUM(IF(s_id = 38, points, 0)) AS cl, SUM(IF(s_id = 34, points, 0)) AS sp
FROM Primes
GROUP BY zid, wid
) AS p ON p.zid = r.zid AND p.wid = r.wid
WHERE r.rank <= 3 AND r.id = '24293' AND r.category = 'C' AND r.fin = '1'
GROUP BY f.fid
ORDER BY pointstotal DESC

关于mysql - MySQL中如何对子查询求和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59403316/

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