我还是把sql给贴出来吧,帮我看看呗 select case when grouping(peisong.peisong)=1 then '' else dian.name end as Dname , case when grouping(dian.name)=1 then '合计' when grouping(peisong.peisong)=1 then '小计' else peisong.peisong end as Pname , isnull(a.number,0) as assign, isnull(sum(case when (DATEPART(day,kdate)) between 1 and 31 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as complete, case when isnull(a.number,0) = 0 then ltrim(0) else ltrim(cast(cast(sum(case when (DATEPART(day,kdate)) between 1 and 31 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end )*1.0/a.number*100.0 as decimal(18,2))as varchar)) + '%' end as rate, isnull(sum(case when (DATEPART(day,kdate)) between 1 and 10 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as Fmonth, isnull(sum(case when (DATEPART(day,kdate)) between 11 and 20 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as Mmonth, isnull(sum(case when (DATEPART(day,kdate)) between 21 and 31 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as Lmonth, isnull(sum(case when (DATEPART(day,kdate)) between 1 and 31 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as total from haoma_add left join dian on dian.dian = haoma_add.dian left join peisong on peisong.id = haoma_add.peisong left join (select dian,peisong,number from assignment where assignment.year =2008 and assignment.month =5 and assignment.type = '稀有号码' group by dian,peisong,number) a on haoma_add.dian = a.dian and haoma_add.peisong=a.peisong where haoma_add.dian = 1 group by dian.name,peisong.peisong,a.number with rollup having grouping(dian.name)=1 or grouping(peisong.peisong)=1 or grouping(a.number)=0 其中assign没有合计,其他的都有合计,number也是int型的????? 还有哦,这个rate是个百分比,可是在小计和合计时怎么算这个百分比啊?
select case when grouping(peisong.peisong)=1 then '' else dian.name end as Dname ,
case when grouping(dian.name)=1 then '合计' when grouping(peisong.peisong)=1 then '小计' else peisong.peisong end as Pname ,
isnull(a.number,0) as assign,
isnull(sum(case when (DATEPART(day,kdate)) between 1 and 31 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as complete,
case when isnull(a.number,0) = 0 then ltrim(0) else ltrim(cast(cast(sum(case when (DATEPART(day,kdate)) between 1 and 31 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end )*1.0/a.number*100.0 as decimal(18,2))as varchar)) + '%' end as rate,
isnull(sum(case when (DATEPART(day,kdate)) between 1 and 10 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as Fmonth,
isnull(sum(case when (DATEPART(day,kdate)) between 11 and 20 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as Mmonth,
isnull(sum(case when (DATEPART(day,kdate)) between 21 and 31 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as Lmonth,
isnull(sum(case when (DATEPART(day,kdate)) between 1 and 31 and DATEPART(year,kdate)=2008 and DATEPART(month,kdate)=5 then 1 else 0 end ),0) as total from haoma_add
left join dian on dian.dian = haoma_add.dian left join peisong on peisong.id = haoma_add.peisong
left join (select dian,peisong,number from assignment where assignment.year =2008 and assignment.month =5 and assignment.type = '稀有号码' group by dian,peisong,number) a on haoma_add.dian = a.dian and haoma_add.peisong=a.peisong
where haoma_add.dian = 1 group by dian.name,peisong.peisong,a.number with rollup
having grouping(dian.name)=1 or grouping(peisong.peisong)=1 or grouping(a.number)=0
其中assign没有合计,其他的都有合计,number也是int型的?????
还有哦,这个rate是个百分比,可是在小计和合计时怎么算这个百分比啊?
更可怜的是有个啥不会的问题,连个请教的人都没有、
只能靠在csdn上问。。
这郁闷的
而用with rollup时,要合计的栏位必须要sum的
呵呵
又收获了一点东东
结贴了