mysql - 通过按日期从多列中选择来在一列中显示

数据库表Request。。。。。。。

user_name   sim_type    mobile_no   sim_id  request_amount   request_date
Rahim       Prepaid     01913644900     3   98      01/08/2014 13:45
Rahim       Prepaid     01790117748     1   50      01/08/2014 13:55
Rahim       Prepaid     01836650415     2   100         01/09/2014 15:29
Rahim       Prepaid     01553698741     3   100         01/09/2014 15:29
Rahim       Prepaid     01736005131     1   100         01/09/2014 15:30

我的输出是。。。。。。。。

user_name    sim_id1    sim_id2    sim_id3    total_amount    request_date
Rahim           50         0          98          148          01/08/2014
Rahim           100        100        100         300          01/09/2014

最佳答案

试试这个：

SELECT user_name, DATE(request_date) request_date,
       SUM(IF(sim_id = 1, request_amount, 0)) sim_id1,
       SUM(IF(sim_id = 2, request_amount, 0)) sim_id2,
       SUM(IF(sim_id = 3, request_amount, 0)) sim_id3,
       SUM(request_amount) total_amount
FROM Request
GROUP BY user_name, DATE(request_date)