mysql - 使用mysql在一个查询中查询计数，总和，带条件

Question

我在 mysql 数据库中有这个表:

TABLE rating
| id         (int, primary)
| id_company (int, index)
| state      (enum: 'waiting','done','refuse')
| rating     (int between 1 and 5)

我想获取一个 id_company 的统计数据:

• 数行
• state=done 的行数
• 综合评分
• 总评分为 state=done

为此我有 2 个查询(例如 id_company=2):

SELECT COUNT(1) as `nbr`, SUM(`rating`) as `total` FROM `rating` WHERE `id_company`=2
SELECT COUNT(1) as `nbr`, SUM(`rating`) as `total` FROM `rating` WHERE `id_company`=2 AND `state`='done'

但是可以进行唯一查询来获取此统计信息吗？

Answer 1

你可以尝试这样的事情:

SELECT COUNT(*) as `nbr1`, 
       SUM(`rating`) as `total1`,
       SUM(`state`='done') as `nbr2`, 
       SUM(CASE 
              WHEN `state`='done' THEN `rating` 
              ELSE 0 
           END) as `total2`  
FROM `rating` 
WHERE `id_company`=2