sql语句包含子查询怎么进行分组
select date(orderTime) as '日期', count(*) as '总笔数',
(select count(*) from morder where orderTime between '2011-05-10' and '2011-05-20' and
payStatus=1 ) as '成功笔数',
(select count(*) from morder where orderTime between '2011-05-10' and '2011-05-20' and
payStatus=0) as '未支付笔数',
(select count(*) from morder where orderTime between '2011-05-10' and '2011-05-20' and
payStatus=3) as '冲正笔数',
(select sum(amount)*0.01 from morder where orderTime between '2011-05-10' and '2011-05-20'
and payStatus=1) as '成功总金额'
from morder where orderTime between '2011-05-10' and '2011-05-20' GROUP by date(orderTime)
这是我的sql语句
我想实现成这样的
日期 总笔数 成功笔数 未支付笔数 冲正笔数 成功总金额
5.10 33 0 33 0 1.1
5.11 45 2 40 3 3.2
总计 78 2 73 3 4.3
要实现这样的
怎么办
是mysql数据库
前面在http://topic.csdn.net/u/20110803/09/2de3cff4-2aaf-4264-a616-c8e946fed0b4.html?seed=759334189&r=74741972#r_74741972
中看到都说用case when 来
但是不知道怎么用
求解
[解决办法]
SELECT DATE(orderTime) AS 日期, COUNT(*) AS 总笔数,
SUM(CASE WHEN payStatus=1 THEN 1 ELSE 0 END) AS 成功笔数,
SUM(CASE WHEN payStatus=0 THEN 1 ELSE 0 END) AS 未支付笔数,
SUM(CASE WHEN payStatus=3 THEN 1 ELSE 0 END) AS 冲正笔数,
SUM(CASE WHEN payStatus=1 THEN amount ELSE 0 END)*0.01 AS 成功总金额
FROM morder
WHERE orderTime BETWEEN '2011-05-10' AND '2011-05-20'
GROUP BY DATE(orderTime)
在MYSQL 5.1。32下测试没有问题
