t_xmlb(项目类别表)
dm mc
01 A
02 B
03 C
t_zgbm(主管部门表)
dm mc
01 AA
02 BB
03 CCt_xmb(项目表)
mc(名称) hzdw(合作单位) lb(项目类别) bm(主管部门) je(项目金额)
M1 N1 01 02 1
M2 N2 01 01 2
M3 N3 02 03 3
M4 N4 02 01 4
M5 N5 02 01 5
M6 N6 03 02 6
输出结果
A 3
1 M1 N1 BB 1
2 M2 N2 AA 2
B 12
1 M3 N3 CC 3
2 M4 N4 AA 4
3 M5 N5 AA 5
C 6
1 M6 N6 BB 6
dm mc
01 A
02 B
03 C
t_zgbm(主管部门表)
dm mc
01 AA
02 BB
03 CCt_xmb(项目表)
mc(名称) hzdw(合作单位) lb(项目类别) bm(主管部门) je(项目金额)
M1 N1 01 02 1
M2 N2 01 01 2
M3 N3 02 03 3
M4 N4 02 01 4
M5 N5 02 01 5
M6 N6 03 02 6
输出结果
A 3
1 M1 N1 BB 1
2 M2 N2 AA 2
B 12
1 M3 N3 CC 3
2 M4 N4 AA 4
3 M5 N5 AA 5
C 6
1 M6 N6 BB 6
from (
select *,ROW_NUMBER()over(partition by 类别mc order by mc ) as rowid
from (
select b.mc as 类别mc,a.mc,a.hzdw,c.mc as 部门mc,a.je
from T_xmb as a inner join t_xmlb as b on a.lb=b.dm
inner join t_zgbm as c on a.bm=c.dm
union all
select b.mc as 类别mc,'','','' as 部门mc,sum(a.je)
from T_xmb as a inner join t_xmlb as b on a.lb=b.dm
inner join t_zgbm as c on a.bm=c.dm
group by b.mc) as X
) as Y
order by 类别mc,rowid
--> 测试数据: @t_xmlb
declare @t_xmlb table (dm varchar(2),mc varchar(1))
insert into @t_xmlb
select '01','A' union all
select '02','B' union all
select '03','C'--> 测试数据: @t_zgbm
declare @t_zgbm table (dm varchar(2),mc varchar(2))
insert into @t_zgbm
select '01','AA' union all
select '02','BB' union all
select '03','CC'--> 测试数据: @t_xmb
declare @t_xmb table (mc varchar(2),hzdw varchar(2),lb varchar(2),bm varchar(2),je int)
insert into @t_xmb
select 'M1','N1','01','02',1 union all
select 'M2','N2','01','01',2 union all
select 'M3','N3','02','03',3 union all
select 'M4','N4','02','01',4 union all
select 'M5','N5','02','01',5 union all
select 'M6','N6','03','02',6
;with m2 as
(
select
ltrim(row_number() over (partition by lb order by (select 1))) as id,
*,0 as c1
from @t_xmb union all
select
b.mc,'','',lb,'',sum(je) as je,1
from @t_xmb a left join @t_xmlb b on a.lb=b.dm
group by lb,b.mc
)select
a.id,a.mc,a.hzdw,isnull(b.mc,'') as dm,a.je
from m2 a left join @t_zgbm b on a.bm=b.dm
order by lb,c1 desc,id
/*
id mc hzdw dm je
------------------------ ---- ---- ---- -----------
A 3
1 M1 N1 BB 1
2 M2 N2 AA 2
B 12
1 M3 N3 CC 3
2 M4 N4 AA 4
3 M5 N5 AA 5
C 6
1 M6 N6 BB 6
*/