22,209
社区成员
发帖
与我相关
我的任务
分享
declare @tb table([日期] varchar(8),[地点] varchar(8),[话单1] int,[话单2] int,[话单3] int,[话单4] int,[话单5] numeric(5,2),[话单6] numeric(5,2))
insert @tb
select '20081001','鹤壁市区',2861,15271,2970,16550,143.05,763.55 union all
select '20081001','淇县',1380,10738,1765,10734,69,536.9 union all
select '20081001','山城',2585,19668,2253,22862,129.25,983.4 union all
select '20081001','浚县',1580,19133,1769,17396,79,956.65 union all
select '20081002','鹤壁市区',2015,12484,2409,14902,100.75,624.2 union all
select '20081002','淇县',1090,9438,1258,9762,54.5,471.9 union all
select '20081002','山城',2010,16382,1939,22343,100.5,819.1 union all
select '20081002','浚县',1231,16272,1784,16737,61.55,813.6 union all
select '20081003','鹤壁市区',2058,12805,2268,13393,102.9,640.25 union all
select '20081003','淇县',941,9946,1173,10067,47.05,497.3 union all
select '20081003','山城',2071,15666,1801,19459,103.55,783.3 union all
select '20081003','浚县',1301,16411,1404,14531,65.05,820.55 union all
select '20081004','鹤壁市区',1911,13098,2189,14148,95.55,654.9 union all
select '20081004','淇县',1016,9564,1196,12926,50.8,478.2 union all
select '20081004','山城',1797,16176,1775,23373,89.85,808.8 union all
select '20081004','浚县',1284,17001,1738,17029,64.2,850.05 union all
select '20081005','鹤壁市区',1883,13165,1994,13738,94.15,658.25 union all
select '20081005','淇县',1026,9720,1348,9645,51.3,486 union all
select '20081005','山城',1627,16034,1620,18468,81.35,801.7 union all
select '20081005','浚县',1097,17003,1341,15345,54.85,850.15 union all
select '20081006','鹤壁市区',2181,13780,2430,13459,109.05,689 union all
select '20081006','淇县',1103,8939,1286,9067,55.15,446.95 union all
select '20081006','山城',2036,15259,1860,18246,101.8,762.95 union all
select '20081006','浚县',1084,15442,1249,14024,54.2,772.1
select [日期], [地点],[话单1],[话单2],[话单3],[话单4],[话单5],[话单6]
from (select * from @tb
union all
select [日期], '总和' AS [地点], sum([话单1]),sum([话单2]),sum([话单3]),sum([话单4]),sum([话单5]),sum([话单6])
from @tb
group by [日期]
)t order by [日期],[地点]
--测试结果:
/*
日期 地点 话单1 话单2 话单3 话单4 话单5 话单6
20081001 鹤壁市区 2861 15271 2970 16550 143.05 763.55
20081001 浚县 1580 19133 1769 17396 79.00 956.65
20081001 淇县 1380 10738 1765 10734 69.00 536.90
20081001 山城 2585 19668 2253 22862 129.25 983.40
20081001 总和 8406 64810 8757 67542 420.30 3240.50
20081002 鹤壁市区 2015 12484 2409 14902 100.75 624.20
20081002 浚县 1231 16272 1784 16737 61.55 813.60
20081002 淇县 1090 9438 1258 9762 54.50 471.90
20081002 山城 2010 16382 1939 22343 100.50 819.10
20081002 总和 6346 54576 7390 63744 317.30 2728.80
20081003 鹤壁市区 2058 12805 2268 13393 102.90 640.25
20081003 浚县 1301 16411 1404 14531 65.05 820.55
20081003 淇县 941 9946 1173 10067 47.05 497.30
20081003 山城 2071 15666 1801 19459 103.55 783.30
20081003 总和 6371 54828 6646 57450 318.55 2741.40
20081004 鹤壁市区 1911 13098 2189 14148 95.55 654.90
20081004 浚县 1284 17001 1738 17029 64.20 850.05
20081004 淇县 1016 9564 1196 12926 50.80 478.20
20081004 山城 1797 16176 1775 23373 89.85 808.80
20081004 总和 6008 55839 6898 67476 300.40 2791.95
20081005 鹤壁市区 1883 13165 1994 13738 94.15 658.25
20081005 浚县 1097 17003 1341 15345 54.85 850.15
20081005 淇县 1026 9720 1348 9645 51.30 486.00
20081005 山城 1627 16034 1620 18468 81.35 801.70
20081005 总和 5633 55922 6303 57196 281.65 2796.10
20081006 鹤壁市区 2181 13780 2430 13459 109.05 689.00
20081006 浚县 1084 15442 1249 14024 54.20 772.10
20081006 淇县 1103 8939 1286 9067 55.15 446.95
20081006 山城 2036 15259 1860 18246 101.80 762.95
20081006 总和 6404 53420 6825 54796 320.20 2671.00
*/
--分区平均
select * from [tb]
union all
select '分区平均',地点, avg(话单1),avg(话单2),avg(话单3),avg(话单4),avg(话单5),avg(话单6) from tb group by 地点
order by 地点,日期
/**
日期 地点 话单1 话单2 话单3 话单4 话单5 话单6
-------------------- -------- ----------- ----------- ----------- ----------- ---------------------------------------- ----------------------------------------
20081001 鹤壁市区 2861 15271 2970 16550 143.050000 763.550000
20081002 鹤壁市区 2015 12484 2409 14902 100.750000 624.200000
20081003 鹤壁市区 2058 12805 2268 13393 102.900000 640.250000
20081004 鹤壁市区 1911 13098 2189 14148 95.550000 654.900000
20081005 鹤壁市区 1883 13165 1994 13738 94.150000 658.250000
20081006 鹤壁市区 2181 13780 2430 13459 109.050000 689.000000
分区平均 鹤壁市区 2151 13433 2376 14365 107.575000 671.691666
20081001 浚县 1580 19133 1769 17396 79.000000 956.650000
20081002 浚县 1231 16272 1784 16737 61.550000 813.600000
20081003 浚县 1301 16411 1404 14531 65.050000 820.550000
20081004 浚县 1284 17001 1738 17029 64.200000 850.050000
20081005 浚县 1097 17003 1341 15345 54.850000 850.150000
20081006 浚县 1084 15442 1249 14024 54.200000 772.100000
分区平均 浚县 1262 16877 1547 15843 63.141666 843.850000
20081001 淇县 1380 10738 1765 10734 69.000000 536.900000
20081002 淇县 1090 9438 1258 9762 54.500000 471.900000
20081003 淇县 941 9946 1173 10067 47.050000 497.300000
20081004 淇县 1016 9564 1196 12926 50.800000 478.200000
20081005 淇县 1026 9720 1348 9645 51.300000 486.000000
20081006 淇县 1103 8939 1286 9067 55.150000 446.950000
分区平均 淇县 1092 9724 1337 10366 54.633333 486.208333
20081001 山城 2585 19668 2253 22862 129.250000 983.400000
20081002 山城 2010 16382 1939 22343 100.500000 819.100000
20081003 山城 2071 15666 1801 19459 103.550000 783.300000
20081004 山城 1797 16176 1775 23373 89.850000 808.800000
20081005 山城 1627 16034 1620 18468 81.350000 801.700000
20081006 山城 2036 15259 1860 18246 101.800000 762.950000
分区平均 山城 2021 16530 1874 20791 101.050000 826.541666
(所影响的行数为 28 行)
**/
--分天总和
select * from [tb]
union all
select 日期,' 分天总和',sum(话单1),sum(话单2),sum(话单3),sum(话单4),sum(话单5),sum(话单6) from tb group by 日期
order by 日期,地点 desc
/**
日期 地点 话单1 话单2 话单3 话单4 话单5 话单6
-------------------- --------- ----------- ----------- ----------- ----------- ---------------------------------------- ----------------------------------------
20081001 山城 2585 19668 2253 22862 129.25 983.40
20081001 淇县 1380 10738 1765 10734 69.00 536.90
20081001 浚县 1580 19133 1769 17396 79.00 956.65
20081001 鹤壁市区 2861 15271 2970 16550 143.05 763.55
20081001 分天总和 8406 64810 8757 67542 420.30 3240.50
20081002 山城 2010 16382 1939 22343 100.50 819.10
20081002 淇县 1090 9438 1258 9762 54.50 471.90
20081002 浚县 1231 16272 1784 16737 61.55 813.60
20081002 鹤壁市区 2015 12484 2409 14902 100.75 624.20
20081002 分天总和 6346 54576 7390 63744 317.30 2728.80
20081003 山城 2071 15666 1801 19459 103.55 783.30
20081003 淇县 941 9946 1173 10067 47.05 497.30
20081003 浚县 1301 16411 1404 14531 65.05 820.55
20081003 鹤壁市区 2058 12805 2268 13393 102.90 640.25
20081003 分天总和 6371 54828 6646 57450 318.55 2741.40
20081004 山城 1797 16176 1775 23373 89.85 808.80
20081004 淇县 1016 9564 1196 12926 50.80 478.20
20081004 浚县 1284 17001 1738 17029 64.20 850.05
20081004 鹤壁市区 1911 13098 2189 14148 95.55 654.90
20081004 分天总和 6008 55839 6898 67476 300.40 2791.95
20081005 山城 1627 16034 1620 18468 81.35 801.70
20081005 淇县 1026 9720 1348 9645 51.30 486.00
20081005 浚县 1097 17003 1341 15345 54.85 850.15
20081005 鹤壁市区 1883 13165 1994 13738 94.15 658.25
20081005 分天总和 5633 55922 6303 57196 281.65 2796.10
20081006 山城 2036 15259 1860 18246 101.80 762.95
20081006 淇县 1103 8939 1286 9067 55.15 446.95
20081006 浚县 1084 15442 1249 14024 54.20 772.10
20081006 鹤壁市区 2181 13780 2430 13459 109.05 689.00
20081006 分天总和 6404 53420 6825 54796 320.20 2671.00
(所影响的行数为 30 行)
**/
CREATE proc p_qry
@JFKMBID varchar(20),
@JFKMBSYSID varchar(1000),
@date1 datetime,
@date2 datetime,
@r_count int
as
set nocount on
--分拆 @JFKMBSYSID
declare @i int
set @i=len(@JFKMBSYSID)
if isnull(@i,0)<1 return
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount @i
select jFKMBSYSID=substring(@JFKMBSYSID,id,patindex('%[,;]%',stuff(@JFKMBSYSID+',',1,id,'')))
,gid=case when substring(','+@JFKMBSYSID,id,1)=',' then 0 else 1 end,sid=0
into #t1 from #t
where substring(','+@JFKMBSYSID,id,1) in(',',';')
order by id
--生成分组标志
select @i=1
update #t1 set @i=case when gid=0 then @i else @i+1 end,gid=@i
--生成处理数据
select a1.gid,a2.PX,b.qydm,cnt=count(*),sid=0
into #t2
from #t1 a1,S_IntegralMode a2,S_PntegralFactInfo b
where a1.jFKMBSYSID=a2.jFKMBSYSID
and a2.jFKMBSYSID=b.jFKMBSYSID
and b.slsj>=convert(char(10),@date1,120)
and b.slsj<convert(char(10),@date2+1,120)
and b.JFKMBID=@JFKMBID
group by a1.gid,a2.PX,b.qydm
order by a1.gid,a2.PX,b.qydm
--去掉不符合条件的记录
delete a from #t2 a
where not exists(
select * from #t2
where qydm=a.qydm and gid=a.gid and cnt>=@r_count)
--生成交叉表处理标志
declare @gid int,@PX int
set @i=1
update #t2 set @i=case when @gid=gid
then case when @PX=PX then @i else @i+1 end
else 1 end
,@gid=gid,@PX=PX,sid=@i
--生成交叉表处理语句
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000)
select @s1='',@s2='',@s3='',@s4=''
,@i=max(sid) from #t2
while @i>0
select @s1=',[col'+rtrim(@i)
+']=cast(sum(case sid when '+rtrim(@i)
+' then cnt else 0 end) as varchar)'+@s1
,@s2=',[col'+rtrim(@i)
+']=max(case sid when '+rtrim(@i)
+' then cast(PX as varchar) else '''' end)'+@s2
,@s3=',a.[col'+rtrim(@i)+']'+@s3
,@i=@i-1
--显示结果
exec('
select qymc=isnull(b.qymc,a.qydm)'+@s3+'
from(
select gid,qydm,s1=1'+@s1+' from #t2 group by gid,qydm
union all
select gid,''查询分组''+rtrim(gid),s1=0'+@s2+' from #t2 group by gid
union all
select gid,''分组''+rtrim(gid)+''合计'',s1=2'+@s1+' from #t2 group by gid
)a left join S_Enterprise b on a.qydm=b.qydm
order by a.gid,a.s1,a.qydm
')
GO
exec p_qry '4-2005','45,46,47,48;49,50,51,52,53;54,55;56,57,58;59','2005-1-1','2005-1-31',1
go
drop proc p_qry
/*--测试结果
qymc
------
查询分组1 1 2 3 4
象山医药药材有限公司医药商店 1 1 1 1 0
象山同仁堂大药房药业有限公司 3 3 3 3 0
分组1合计 4 4 4 4 0
查询分组2 5 6 7 8 9
象山医药药材有限公司医药商店 1 1 1 1 1
象山同仁堂大药房药业有限公司 3 3 3 3 3
分组2合计 4 4 4 4 4
查询分组3 10 11
象山医药药材有限公司医药商店 1 1 0 0 0
象山同仁堂大药房药业有限公司 3 3 0 0 0
分组3合计 4 4 0 0 0
查询分组4 12 13 14
象山医药药材有限公司医药商店 1 1 1 0 0
象山同仁堂大药房药业有限公司 3 3 3 0 0
分组4合计 4 4 4 0 0
查询分组5 15
象山医药药材有限公司医药商店 1 0 0 0 0
象山同仁堂大药房药业有限公司 3 0 0 0 0
分组5合计 4 0 0 0 0
--*/
--示例
--测试数据
create table tb([DB-ID] varchar(10),ENTITY varchar(10),DATE varchar(10),[CUST-NO] int,AMOUNT decimal(10,2),TAX decimal(10,2))
insert tb select 'RCHQ','001','2004-11-10',200000,100.00,17.00
union all select 'RCHQ','001','2004-11-10',200000,200.00,34.00
union all select 'RCHQ','001','2004-11-12',200000,150.00,25.50
union all select 'RCHQ','002','2004-11-10',200000,100.00,17.00
union all select 'RCHQ','002','2004-11-10',200000,200.00,34.00
union all select 'RCHQ','002','2004-11-12',200000,150.00,25.50
go
--查询
select [DB-ID],ENTITY,DATE,[CUST-NO],AMOUNT,TAX
from(
select [DB-ID]=case
when grouping([DB-ID])=1 then '合计'
else [DB-ID] end
,ENTITY=case
when grouping([DB-ID])=1 then ''
when grouping(ENTITY)=1 then '小计'
else ENTITY end
,DATE=case
when grouping([DB-ID])=1 then ''
when grouping(ENTITY)=1 then ''
when grouping(DATE)=1 then '小计'
else DATE end
,[CUST-NO]=case
when grouping([DB-ID])=1 then ''
when grouping(ENTITY)=1 then ''
when grouping(DATE)=1 then ''
when grouping([CUST-NO])=1 then '小计'
else cast([CUST-NO] as varchar) end
,AMOUNT=sum(AMOUNT),TAX=sum(TAX)
,s1=grouping([DB-ID]),s2=[DB-ID]
,s3=grouping(ENTITY),s4=ENTITY
,s5=grouping(DATE),s6=DATE
,s7=grouping([CUST-NO]),s8=[CUST-NO]
from tb
group by [DB-ID],ENTITY,DATE,[CUST-NO] with rollup
having grouping([CUST-NO])=1
union all
select [DB-ID],ENTITY,DATE,cast([CUST-NO] as varchar),AMOUNT,TAX
,s1=0,s2=[DB-ID]
,s3=0,s4=ENTITY
,s5=0,s6=DATE
,s7=0,s8=[CUST-NO]
from tb
)a order by s1,s2,s3,s4,s5,s6,s7,s8
go
--删除测试
drop table tb
/*--测试结果
DB-ID ENTITY DATE CUST-NO AMOUNT TAX
-------- -------- ----------------- --------- -------- ---------
RCHQ 001 2004-11-10 200000 100.00 17.00
RCHQ 001 2004-11-10 200000 200.00 34.00
RCHQ 001 2004-11-10 小计 300.00 51.00
RCHQ 001 2004-11-12 200000 150.00 25.50
RCHQ 001 2004-11-12 小计 150.00 25.50
RCHQ 001 小计 450.00 76.50
RCHQ 002 2004-11-10 200000 100.00 17.00
RCHQ 002 2004-11-10 200000 200.00 34.00
RCHQ 002 2004-11-10 小计 300.00 51.00
RCHQ 002 2004-11-12 200000 150.00 25.50
RCHQ 002 2004-11-12 小计 150.00 25.50
RCHQ 002 小计 450.00 76.50
RCHQ 小计 900.00 153.00
合计 900.00 153.00
--*/