select rid,gid ,tradedate=max(tradedate),tradecount=count(*) ,status0=sum(case status when 0 then 1 else 0 end) ,status1=sum(case status when 1 then 1 else 0 end) from 表 group by rid,gid
select rid,gid,tradedate=max(tradedate),tradecount=count(*),statuso=sum(case status when 0 then 1 else 0 end ), status1=sum(case status when 1 then 1 else 0 end ) from 表 group by rid,gid order by rid
from table
group by rid,gid
from 表
group by rid,gid
from yourtable
group by rid,gid
大概可以吧?!
rid gid tradedate status
1 1 2004-1-1 0
1 2 2004-1-3 0
1 1 2004-2-2 1
2 1 2004-1-1 1
1 1 2004-1-2 0
2 1 2004-2-2 1
结果:
rid gid tradedate tradecount status0 status1
1 1 2004-2-2 3 2 1
1 2 2004-1-3 1 1 0
2 1 2004-2-2 2 0 2
,tradedate=max(tradedate),tradecount=count(*)
,status0=sum(case status when 0 then 1 else 0 end)
,status1=sum(case status when 1 then 1 else 0 end)
from 表
group by rid,gid
status1=sum(case status when 1 then 1 else 0 end )
from 表
group by rid,gid
order by rid