MySQL 聚合？

Question

我有以下两张表

Table: items
ID  |  TITLE
249 | One
250 | Two
251 | Three

我投票给这些:

Table: votes
VID | IID | userid | votes
01  | 249 |    6   | 5
02  | 249 |    7   | -5
03  | 249 |    8   | 5
04  | 249 |    9   | 5
05  | 250 |    6   | -5
06  | 250 |    7   | -5
07  | 250 |    8   | 5

-5 表示反对，+5 表示赞成。假设我以用户 6 身份登录，SQL 查询将给我什么:

Table: result
ID  |  TITLE | TOTALVOTES | UPVOTES | DOWNVOTES | CURRENTUSERVOTED
249 | One    | 4          | 3       | 1         | 1
250 | Two    | 3          | 1       | 2         | 1
251 | Three  | 0          | 0       | 0         | 0

Answer 1

在聚合函数中使用 CASE 表达式:

SELECT    a.ID,
          a.TITLE,
          COUNT(b.IID) AS TOTALVOTES,
          COUNT(CASE WHEN b.votes =  5 THEN 1 END) AS UPVOTES,
          COUNT(CASE WHEN b.votes = -5 THEN 1 END) AS DOWNVOTES,
          COUNT(CASE WHEN b.userid = 6 THEN 1 END) AS CURRENTUSERVOTED
FROM      items a
LEFT JOIN votes b ON a.ID = b.IID
GROUP BY  a.ID,
          a.TITLE