sql语句 group 分组求和求数量

[chuachua]

我有两表结构如下：
t_order(id,orderid,orderdate,status)
t_psger(id,orderid,acny)
两表orderid列相关联，现在我想按orderdate分组列出不同status的t_psger表中的数量及acny求和
我有一语句已经能达到该效果，但是好象太不简明了点，希望大家能帮我简化一下：
select unsell,saled,cnt,ba.orderdate from(select unsell,cnt,aa.orderdate from (select sum(acny) unsell,orderdate from (select acny,convert(nvarchar(10),orderdate,121) orderdate
from t_order a,t_psger b where b.orderid=a.orderid and a.status='未收银') c group by c.orderdate) aa left join
(select count(id) cnt,convert(nvarchar(10),orderdate,121) orderdate from t_order
group by convert(nvarchar(10),orderdate,121)) ab on aa.orderdate=ab.orderdate) ba left join
(select sum(acny) saled,orderdate from (select acny,convert(nvarchar(10),orderdate,121) orderdate
from t_order a,t_psger b where b.orderid=a.orderid and a.status='已收银') c group by c.orderdate) bb
on ba.orderdate=bb.orderdate
语句很结构化，相信看了不会头晕！

[Garnett_KG]

语句很结构化，相信看了不会头晕！
>>

看到这句我就晕了！

[tim_spac]

/*t_order(id,orderid,orderdate,status) 
t_psger(id,orderid,acny) 
两表orderid列相关联，现在我想按orderdate分组列出不同status的t_psger表中的数量及acny求和 
*/
select orderdate, status, count(b.orderid), sum(ancy)
from t_order a
left join (
  select orderid,ancy=sum(acny)
  from t_pager
  group by orderid
) as b on a.order_id=b.order_id
group by orderdate, status

[tim_spac]

/*t_order(id,orderid,orderdate,status) 
t_psger(id,orderid,acny) 
两表orderid列相关联，现在我想按orderdate分组列出不同status的t_psger表中的数量及acny求和 
*/
select orderdate, status, cnt=sum(cnt), ancy=sum(ancy)
from t_order a
left join (
  select orderid,cnt=count(1),ancy=sum(acny)
  from t_psger
  group by orderid
) as b on a.order_id=b.order_id
group by orderdate, status

这回假设在t_psger中每个orderid可能存在多行

[lxuan_025]

dig

[hyde100]

楼主是想知道每一天的未未收银和已收银的条数以及它们对应的总钱数对吧？如是，请参考下面：

create table #t_order(id int identity(1,1),orderid int ,orderdate nchar(8),status nvarchar(50)) 
create table #t_psger(id int identity(1,1),orderid int ,acny decimal(10,2)) 

insert into #t_order(orderid,orderdate,status)
select 1,'20080918','未收银' union all
select 2,'20080919','未收银' union all
select 3,'20080920','未收银' union all
select 4,'20080920','已收银' 

insert into #t_psger(orderid,acny)
select 1,10 union all
select 2,20 union all
select 3,30 union all
select 3,40 union all
select 4,40

select 
	a.orderdate,
	a.status,
	count(a.status) r_count,
	sum(b.acny) sum_money
from #t_order a
	left outer join #t_psger b on a.orderid = b.orderid
group by a.orderdate,
	a.status

drop table #t_order
drop table #t_psger

结果：
20080918	未收银	1	10.00
20080919	未收银	1	20.00
20080920	未收银	2	70.00
20080920	已收银	1	40.00

[chuachua]

晕死，发了贴给忙忘了，感谢各位的回答，但我实际需要的结果却是
select unsell,saled,cnt,ba.orderdate，有点不符