日期:2014-05-17 浏览次数:20562 次
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'--> 测试数据:[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 <X86> (Build 7600: )