一个查询有关问题，谢谢了

一个查询问题，多谢了！
表1结构
xh dwmc zgbm（主管部门）
1 单位1 A
2 单位2 A
3 单位3 B
4 单位4 B
5 单位5 B
6 单位6 C
表2
dwmc je
单位1 1
单位2 2
单位3 3
单位4 4
单位5 5
单位6 6

想要这种结果,并插入数据库中
mc je
A 3
单位1 1
单位2 2
B 11
单位3 3
单位4 4
单位5 5
c 6
单位6 6

[最优解释]

if object_id('[A]') is not null 
drop table [A]
go 
create table [A]([xh] int,[dwmc] nvarchar(5),[zgbm] varchar(1))
insert [A]
select 1,N'单位1','A' union all
select 2,N'单位2','A' union all
select 3,N'单位3','B' union all
select 4,N'单位4','B' union all
select 5,N'单位5','B' union all
select 6,N'单位6','C'--&gt; 测试数据：[B]
if object_id('[B]') is not null 
drop table [B]
go 
create table [B]([dwmc] nvarchar(5),[je] int)
insert [B]
select N'单位1',1 union all
select N'单位2',2 union all
select N'单位3',3 union all
select N'单位4',4 union all
select N'单位5',5 union all
select N'单位6',6
go

select mc,je from 
 (select a.zgbm as mc,sum(b.je) as je,1 as no,a.zgbm 
     from A a join B b on a.dwmc=b.dwmc group by zgbm
 union all
  select b.dwmc,b.je,2,a.zgbm 
    from A a join B b on a.dwmc=b.dwmc) t
 order by zgbm,no 
 
 /*
 mc    je
----- -----------
A     3
单位1   1
单位2   2
B     12
单位3   3
单位4   4
单位5   5
C     6
单位6   6

[其他解释]


----------------------------
-- Author  :TravyLee
-- Date    :2012-11-05 11:03:23
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--Jul  9 2008 14:43:34 
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 &lt;X86&gt; (Build 7600: ) 
 
--
----------------------------
--&gt; 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([xh] int,[dwmc] varchar(5),[zgbm] varchar(1))
insert [A]
select 1,'单位1','A' union all
select 2,'单位2','A' union all
select 3,'单位3','B' union all
select 4,'单位4','B' union all
select 5,'单位5','B' union all
select 6,'单位6','C'
--&gt; 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([dwmc] varchar(5),[je] int)
insert [B]
select '单位1',1 union all
select '单位2',2 union all
select '单位3',3 union all
select '单位4',4 union all
select '单位5',5 union all
select '单位6',6
go
with
t as(
select
A.dwmc,
A.zgbm,
B.je
from
A 
inner join
B 
on
A.dwmc=B.dwmc
)
select 
 zgbm,
 SUM(je) as je
from
t
group by
zgbm
union all
select
dwmc,
je
from
t
/*
zgbm  je
----- -----------
A     3
B     12
C     6
单位1   1
单位2   2
单位3   3
单位4   4
单位5   5
单位6   6

(9 行受影响)


*/

[其他解释]
select mc,je from
(select a.zgbm as mc,sum(b.je) as je,1 as no,a.zgbm
from 表1 a join 表2 b where a.dwmc=b.dwmc group by zgbm
union all
select b.dwmc,b.je,2,a.zgbm
from 表1 a join 表2 b where a.dwmc=b.dwmc)
order by zgbm,no
[其他解释]

with cte as
(
select dwmc=case when grouping(dwmc)=1 then  zgbm else dwmc end,je=sum(je)
from (select zgbm,a.dwmc,je
      from a join b on a.dwmc=b.dwmc)K
group by zgbm,dwmc
with rollup
)
select * from cte where dwmc is not null

[其他解释]

if(object_id('a') is not null) drop table a
go
create table a
(
xh int,
dwmc varchar(20),
zgbm varchar(1)
)
go
insert into a
select 1,'单位1','A' union all
select 2,'单位2','A' union all
select 3,'单位3','B' union all
select 4,'单位4','B' union all
select 5,'单位5','B' union all
select 6,'单位6','C'
go
if(object_id('b') is not null) drop table b
go
create table b
(
dwmc varchar(50),
je int
)
go
insert into b
select '单位1',1 union all
select '单位2',2 union all
select '单位3',3 union all

select '单位4',4 union all
select '单位5',5 union all
select '单位6',6

select a.zgbm as mc,sum(b.je)as je from a inner join b on a.dwmc = b.dwmc group by a.zgbm
union all
select a.dwmc as mc,je from a inner join b on a.dwmc = b.dwmc

/*
mc je
-------------------- -----------
A 3
B 12
C 6
单位1 1
单位2 2
单位3 3
单位4 4
单位5 5
单位6 6

(9 行受影响)

*/

[其他解释]
客气了客气了
[其他解释]
null
[其他解释]
null

一个查询有关问题谢谢了

热点推荐