sql语句包含子查询怎么进行分组

[tangdao999]

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


要实现这样的
怎么办

[AcHerat]

不要用子查询，用 sum(case when .. then .. end)

[AcHerat]

最后加个with rollup

[快溜]

select date(orderTime) as '日期', count(*) as '总笔数',
        成功笔数=sum(case when payStatus=1 then 1 else 0 end),
        未支付笔数=sum(case when payStatus=0 then 1 else 0 end),
        冲正笔数=sum(case when payStatus=3 then 1 else 0 end),
        成功总金额=sum(case when payStatus=1 then amount else 0 end)*0.01,
from morder 
 where orderTime between '2011-05-10' and '2011-05-20' 
 GROUP by date(orderTime)

[tangdao999]

数据库用的是mysql

[xuam]

有必要这么复杂么？
用case when 语句，不需要子查询语句.

[tangdao999]

case when 在mysql中可以使用吗？？

[AcHerat]

MYSQL应该也可以，楼主去问问MYSQL版，在其他数据库那里。

[tangdao999]

哦
谢谢

[--小F--]

[Quote=引用 4 楼 tangdao999 的回复:]
数据库用的是mysql
[/Quote]
建议去MYSQL问问