mysql - 在一行上显示一等奖，二等奖和三等奖

我必须使用SQL子查询来回答以下问题。

原来的桌子是
奖

event_id    place     money
101         1          120
101         2          60
101         3          30
102         1          10
102         2          5
102         3          2
103         1          100
103         2          60
103         3          40
401         1          1000
401         2          500
401         3          250
401         4          100
401         5          50

问题是：对于每个事件，在一行上列出可用于一等奖，二等奖和三等奖的奖金。按event_id排序。表格的第一行是这样的：
event_id第一，第二，第三
101120 60 30
.....

我的回答是：
使用horsedb;

SELECT event_id,
(SELECT money FROM prize WHERE place = '1' AND event_id = prize.event_id) AS 'First',
(SELECT money FROM prize WHERE place = '2' AND event_id = prize.event_id) AS 'Second',
(SELECT money FROM prize WHERE place = '3' AND event_id = prize.event_id) AS 'Third'
FROM prize
ORDER BY event_id;

但答案有1024错误（返回多行）。我尝试了IN，任何结果都不好。
请帮助，谢谢

最佳答案

这是查询：

select event_id,
       max(first) as first,
       max(second) as second,
       max(third) as third
  from (select event_id,
               case when place = 1 then money else null end as first,
               case when place = 2 then money else null end as second,
               case when place = 3 then money else null end as third
          from prize
       )t
 group by event_id;

和sqlfiddle，如果您需要它：
http://sqlfiddle.com/#!2/aa691/1/0

关于mysql - 在一行上显示一等奖，二等奖和三等奖，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27011171/