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

Question

数据库表请求......

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

Answer 1

试试这个:

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)