reportid bra_num existusr newusr smmnum 200901 0091V 1 2 3
200902 0091V 4 5 6
200901 0900V 7 8 9
200902 0900V 10 11 12
需要得到的效果,如查询 reportid = 200901 至 200902 这段范围,那么: bra_num existusr newusr smmnum 0091V 4 7 90900V 10 19 21
总和 14 26 30将 existusr 以最一个月分显示出来 ,而总和数时,对 exist 只将 200902 里面的数进行相加,其他则如数相加,请问该如何实现呢?表名:cbcostreport
字段:reportid (char 6) 报表时间
bra_num (varchar 20) 部门号
existusr(int) 现存用户数
newuser(int) 新增用户数
smmnum(int) 短信数
200902 0091V 4 5 6
200901 0900V 7 8 9
200902 0900V 10 11 12
需要得到的效果,如查询 reportid = 200901 至 200902 这段范围,那么: bra_num existusr newusr smmnum 0091V 4 7 90900V 10 19 21
总和 14 26 30将 existusr 以最一个月分显示出来 ,而总和数时,对 exist 只将 200902 里面的数进行相加,其他则如数相加,请问该如何实现呢?表名:cbcostreport
字段:reportid (char 6) 报表时间
bra_num (varchar 20) 部门号
existusr(int) 现存用户数
newuser(int) 新增用户数
smmnum(int) 短信数
existusr=sum(existusr),
newusr=sum(newusr),
smmnum=sum(smmnum) from 表 group by bra_num where reportid in(200901,200902)
union all
select '总和',existusr=sum(existusr),
newusr=sum(newusr),
smmnum=sum(smmnum) from 表 where reportid in(200901,200902)
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-05 15:35:23
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (reportid INT,bra_num VARCHAR(5),existusr INT,newusr INT,smmnum INT)
INSERT INTO [tb]
SELECT 200901,'0091V',1,2,3 UNION ALL
SELECT 200902,'0091V',4,5,6 UNION ALL
SELECT 200901,'0900V',7,8,9 UNION ALL
SELECT 200902,'0900V',10,11,12select * from [tb]select bra_num,max(existusr),sum(newusr),sum(smmnum)
from tb
group by bra_num
union all
select '总计',sum(existusr) ,sum(newusr),sum(smmnum)
from (select bra_num,existusr=max(existusr),newusr=sum(newusr),smmnum=sum(smmnum)
from tb
group by bra_num)Kbra_num (无列名) (无列名) (无列名)
0091V 4 7 9
0900V 10 19 21
总计 14 26 30
from tb
group by bra_num
union all
select '总计',sum(existusr) ,sum(newusr),sum(smmnum)
from (select bra_num,existusr=max(existusr),newusr=sum(newusr),smmnum=sum(smmnum)
from tb
group by bra_num)K
bra_num,bra_desc,sum(Case reportid When '200902' Then num_existuser Else 0 End)
As num_existuser,sum(num_newuser) as 'num_newuser' FROM CBCostReport where reportid BETWEEN '200901' AND '200902' group by bra_num
create table cbcostreport(reportid char(6), ---报表时间
bra_num varchar (20) , ---部门号
existusr int ,---现存用户数
newuser int, ----新增用户数
smmnum int )
insert cbcostreport
select '200901' , '0091V', 1 , 2 , 3 union all
select '200902' , '0091V', 4 , 5 , 6 union all
select '200901' , '0900V', 7 , 8 , 9 union all
select '200902' , '0900V' , 10 , 11 , 12
select * from cbcostreport
select bra_num,
existusr=MAX(existusr),
newuser=SUM(newuser),
smmnum=SUM(smmnum )
from cbcostreport group by bra_num with rollup
insert into @tb select '200901','0091V',1,2,3
union all select '200902','0091V',4,5,6
union all select '200901','0900V',7,8,9
union all select '200902','0900V',10,11,12
;With China as
(
select bra_num,max(existusr) existusr,sum(newusr)newusr,sum(smmnum)smmnum from @tb
where reportid in (200901,200902)
group by bra_num
),China1 as
(
select '总分' as bra_num,sum(existusr) existusr,sum(newusr) newusr,sum(smmnum)smmnum from china)
select * from china
union all
select * from china1---------- ----------- ----------- -----------
0091V 4 7 9
0900V 10 19 21
总分 14 26 30(3 行受影响)