MySQL Select 生成新列

Question

我有这样一个 MySQL:

+----+----------+---------+---------------------+
| id | event    | seconds | date                |
+----+----------+---------+---------------------+
|  5 | open     |      0  | 2014-10-20 16:00:00 |
|  5 | assign   |      5  | 2014-10-20 16:00:05 |
|  5 | complete |     10  | 2014-10-20 16:00:15 |
|  5 | close    |     22  | 2014-10-20 16:00:37 |
|  6 | open     |      0  | 2014-10-20 17:00:00 |
|  6 | assign   |      7  | 2014-10-20 17:00:07 |
+----+----------+---------+---------------------+

有没有不用写程序逻辑就返回这个的select语句？

+----+----------+---------+-----------+---------+
| id | open     | assign  | complete  | close   |
+----+----------+---------+-----------+---------+
|  5 |    0     |    5    |    10     |   22    |
|  6 |    0     |    7    |           |         |
+----+----------+---------+-----------+---------+

Answer 1

SELECT id,
SUM(CASE WHEN event='open' THEN seconds ELSE 0 END) as open,
SUM(CASE WHEN event='assign' THEN seconds ELSE 0 END) as assign,
SUM(CASE WHEN event='complete' THEN seconds ELSE 0 END) as complete,
SUM(CASE WHEN event='close' THEN seconds ELSE 0 END) as close
FROM t
GROUP BY id

我假设你需要每个 id 的秒数总和，它可以是最大值、最小值等。