mysql - 我需要在同一个表中的每个日期查询多个列

Question

我有这个表结构

Date       | Success      | No of success tran.| Failed     | No. of failed tran  
29/05/2015 | Total Amount | count success tran.|total amount| count failed tran

我要

1. “日期”有datetime。
2. “成功”有成功金额的总和。
3. “成功交易数”计算总成功金额。
4. “失败”有失败金额的总和。
5. “失败交易数”计算失败总金额。

Answer 1

create table test999
( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
thedate datetime not null,
status varchar(20) not null,
amount decimal (10,2) not null
)


insert into test999 (thedate,status,amount) values (now(),'success',9283.92);
insert into test999 (thedate,status,amount) values (now(),'failed',283.22);
insert into test999 (thedate,status,amount) values (now(),'success',3283.92);
insert into test999 (thedate,status,amount) values (now(),'failed',26.26);
insert into test999 (thedate,status,amount) values (now(),'success',9083.02);
insert into test999 (thedate,status,amount) values (now(),'failed',20093.12);
insert into test999 (thedate,status,amount) values (DATE_SUB(NOW(), INTERVAL 17 DAY),'failed',26.26);
insert into test999 (thedate,status,amount) values (DATE_SUB(NOW(), INTERVAL 17 DAY),'success',9083.02);
insert into test999 (thedate,status,amount) values (DATE_SUB(NOW(), INTERVAL 17 DAY),'failed',20093.12);

SELECT
    date(thedate),
    SUM( CASE WHEN status='success' then 1 else 0 end
        ) AS success_count,
    SUM( CASE WHEN status='success' then amount else 0 end
        ) AS success_amount,
    SUM( CASE WHEN status='failed' then 1 else 0 end
        ) AS failed_count,
    SUM( CASE WHEN status='failed' then amount else 0 end
        ) AS failed_amount
FROM test999
group by date(thedate)
order by date(thedate)

date | success_count | success_amt | failed_count | failed_amt   
2015-05-11  1   9083.02 2   20119.38
2015-05-28  3   21650.86    3   20402.60