如何根据每个字段的特定条件对多个字段求和

Question

我想把条件加到和上。例如，SUM rates.negative将具有is_active =0的条件

这是链接DB Fiddle

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=cd6fe488fcefcd000274a3c67590dede

这是不带条件的示例查询

SELECT 
    comments.*, 
    SUM(
        rates.score
    ), 
    SUM(
        rates.negative
    )  
FROM comments 
    WHERE rates.comment_id = comments.id
GROUP BY comments.id

输出

​

​

如果我使用WHEN来设置条件，我会得到这个错误。

SELECT 
    comments.*, 
    SUM(
        rates.score
    ), 
    SUM(
        rates.negative WHEN rates.is_active = '0'
    )  
FROM comments 
    WHERE rates.comment_id = comments.id
GROUP BY comments.id

错误

Error syntax near WHEN rates.is_active = '0'

如何解决这个问题？

谢谢

Answer 1

正确的语法是使用CASE .. WHEN表达式，用于条件聚合。此外，对于多表查询，为了代码清晰度和可读性，最好使用Aliasing。

SELECT 
    c.id, 
    SUM(
        r.score
    ) AS score_sum, 
    SUM(
        CASE WHEN r.is_active = '0' THEN r.negative ELSE 0 END
    ) AS negative_sum
FROM comments AS c
LEFT JOIN rates AS r ON (r.comment_id = c.id)
WHERE r.comment_id = c.id
GROUP BY c.id

DB fiddle演示：https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=6b344f7653249c53a38e64a9f81bee19

Answer 2

SELECT 
    CS.id AS comment_id,
    SUM(RS.score) AS score_sum,
    SUM(CASE RS.is_active WHEN 0 THEN RS.negative ELSE 0 END) AS negative_sum
FROM comments AS CS 
LEFT JOIN rates AS RS ON (RS.comment_id = CS.id)
GROUP BY CS.id

DB文件演示https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=696a1648f0d2a9baa8330ed9b686fd81

希望这能对你有所帮助