一表有如下数据,如何按照编号中的分级关系,
汇总数据到第一级数据上,编号有多个,值不固定的。
编号 名称 数量
01 A 1
01.02 B 2
01.03 C 3 04 D 1
04.02 E 2
04.03 F 3
如何写sql语句,按编号汇总,输出结果:
01 A 6
01.02 B 2
01.03 C 3 04 D 6
04.02 E 2
04.03 F 3
汇总数据到第一级数据上,编号有多个,值不固定的。
编号 名称 数量
01 A 1
01.02 B 2
01.03 C 3 04 D 1
04.02 E 2
04.03 F 3
如何写sql语句,按编号汇总,输出结果:
01 A 6
01.02 B 2
01.03 C 3 04 D 6
04.02 E 2
04.03 F 3
编号 数量
01 1
01.02 2
01.03 3 04 1
04.02 2
04.03 3
如何写sql语句,按编号汇总,输出结果:
01 6
01.02 2
01.03 3 04 6
04.02 2
04.03 3
编号,
名称,
数量=case when LEN(编号)=2 then (select SUM(数量) from tb where LEFT(编号,2)=k.编号) else 数量 end
from tb k
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( 编号 varchar(20), 数量 int )
go
insert tb SELECT '01' , 1 UNION ALL SELECT
'01.02' , 2 UNION ALL SELECT
'01.03' , 3 UNION ALL SELECT
'04' , 1 UNION ALL SELECT
'04.02' , 2 UNION ALL SELECT
'04.03' , 3
go
select
编号,
数量=case when LEN(编号)=2 then (select SUM(数量) from tb where LEFT(编号,2)=k.编号) else 数量 end
from tb k
go
编号 数量
-------------------- -----------
01 6
01.02 2
01.03 3
04 6
04.02 2
04.03 3(6 行受影响)
首先创建测试表、添加数据。
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7) insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
情况一:只有一个分类汇总列时,只需要一个合计。只需要增加with rollup即可。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup
情况二:有多个分类汇总列,只需要一个合计.增加rollup之后,需要增加判断。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b,
sum(c),sum(d),sum(e) from #t
group by a,b with rollup
having grouping(b)=0 or grouping(a)=1select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(c)=0 or grouping(a)=1
情况三:有多个分类汇总列,需要全部的小计和合计。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
另外一种显示小计的方式
select case when grouping(a)=1 then '合计'
when grouping(b)=1 then cast(a as varchar)+'小计'
else cast(a as varchar) end a,
case when grouping(b)=0 and grouping(c)=1
then cast(b as varchar)+'小计' else cast(b as varchar) end b,
case when grouping(c)=1 and grouping(b)=0
then '' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
情况四:有多个分类汇总列,需要部分的小计和合计
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=0
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=1
01.02 2
01.03 3 04.02 2
04.03 3
如何写sql语句,按编号汇总,输出结果:
01 6
01.02 2
01.03 3 04 6
04.02 2
04.03 3