日期:2014-05-17 浏览次数:20620 次
select left(businesstypename,3) as businesstypename
,[1]=sum(case when datepart(day,[day])=1 then 1 else 0 end)
,[2]=sum(case when datepart(day,[day])=2 then 1 else 0 end)
,[3]=sum(case when datepart(day,[day])=3 then 1 else 0 end)
.....................
.......
,[31]=sum(case when datepart(day,[day])=31 then 1 else 0 end)
from cpl_OrderItems
where convert(varchar(7),[day],120)='2013-07' and (businesstypename like 'aaa%' or businesstypename like 'bbb%')
group by left(businesstypename,3)
select
(select count(*)
from cpl_OrderItems
where day= '2013-08-01' and businesstypename like 'aaa%') as '1',
(select count(*)
from cpl_OrderItems
where day= '2013-08-02' and businesstypename like 'aaa%') as '2',
。。。。。。
(select count(*)
from cpl_OrderItems
where day= '2013-08-31' and businesstypename like 'aaa%') as '31'
union all
select
(select count(*)
from cpl_OrderItems
where day= '2013-08-01' and businesstypename like 'bbb%') as '1',
(select count(*)
from cpl_OrderItems
where day= '2013-08-02' and businesstypename like 'bbb%') as '2',
。。。。。。
(select count(*)
from cpl_OrderItems
where day= '2013-08-31' and businesstypename like 'bbb%') as '31'