---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-05-19 15:11:05 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[TB1] if object_id('[TB1]') is not null drop table [TB1] go create table [TB1]([BLU1] nvarchar(4),[RepN1] int) insert [TB1] select '01',133955 union all select '02',147485 union all select '03',153295 union all select '04',166689 union all select '05',188967 union all select '06',138090 union all select '07',107990 union all select '08',60633 union all select '09',68339 union all select '10',26799 union all select '11',14902 --> 测试数据[TB2] if object_id('[TB2]') is not null drop table [TB2] go create table [TB2]([BLU2] nvarchar(4),[RepN2] int) insert [TB2] select '02',5655 union all select '03',25026 union all select '04',29502 union all select '05',89886 union all select '06',71380 union all select '07',109676 union all select '08',132257 union all select '09',201662 union all select '10',245659 union all select '11',140627 union all select '12',155814 --------------生成数据-------------------------- SELECT CAST(blu1 AS INT )bul ,SUM([RepN1])[RepN] FROM ( select blu2 AS blu1,[RepN2] AS [RepN1] from [TB2] UNION ALL select blu1,[RepN1] from [TB1])a GROUP BY blu1 ORDER BY [RepN] ----------------结果---------------------------- /* bul RepN ----------- ----------- 1 133955 2 153140 11 155529 12 155814 3 178321 8 192890 4 196191 6 209470 7 217666 9 270001 10 272458 5 278853 */
INSERT tb3 SELECT id=CASE WHEN blu1 IS NOT NULL AND blu2 IS NOT NULL THEN blu1 WHEN blu1 IS NOT NULL AND blu2 IS NULL THEN blu1 WHEN blu1 IS NULL AND blu2 IS NOT NULL THEN blu2 END, repn=CASE WHEN blu1 IS NOT NULL AND blu2 IS NOT NULL THEN repn1+repn2 WHEN blu1 IS NOT NULL AND blu2 IS NULL THEN repn1 WHEN blu1 IS NULL AND blu2 IS NOT NULL THEN repn2 END, repn1,repn2,repn=repn1+repn2 FROM tb1 FULL OUTER JOIN tb2 ON blu1=blu2
表tb3
N1 N2
1 133955
2 153140
11 178321
12 196191
3 278853
8 209470
4 217666
6 192890
7 270001
9 272458
10 155529
5 155814
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-19 15:11:05
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[TB1]
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1]([BLU1] nvarchar(4),[RepN1] int)
insert [TB1]
select '01',133955 union all
select '02',147485 union all
select '03',153295 union all
select '04',166689 union all
select '05',188967 union all
select '06',138090 union all
select '07',107990 union all
select '08',60633 union all
select '09',68339 union all
select '10',26799 union all
select '11',14902
--> 测试数据[TB2]
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([BLU2] nvarchar(4),[RepN2] int)
insert [TB2]
select '02',5655 union all
select '03',25026 union all
select '04',29502 union all
select '05',89886 union all
select '06',71380 union all
select '07',109676 union all
select '08',132257 union all
select '09',201662 union all
select '10',245659 union all
select '11',140627 union all
select '12',155814
--------------生成数据--------------------------
SELECT CAST(blu1 AS INT )bul ,SUM([RepN1])[RepN]
FROM (
select blu2 AS blu1,[RepN2] AS [RepN1] from [TB2]
UNION ALL
select blu1,[RepN1] from [TB1])a
GROUP BY blu1
ORDER BY [RepN]
----------------结果----------------------------
/*
bul RepN
----------- -----------
1 133955
2 153140
11 155529
12 155814
3 178321
8 192890
4 196191
6 209470
7 217666
9 270001
10 272458
5 278853
*/
SELECT
id=CASE WHEN blu1 IS NOT NULL AND blu2 IS NOT NULL THEN blu1
WHEN blu1 IS NOT NULL AND blu2 IS NULL THEN blu1
WHEN blu1 IS NULL AND blu2 IS NOT NULL THEN blu2 END,
repn=CASE WHEN blu1 IS NOT NULL AND blu2 IS NOT NULL THEN repn1+repn2
WHEN blu1 IS NOT NULL AND blu2 IS NULL THEN repn1
WHEN blu1 IS NULL AND blu2 IS NOT NULL THEN repn2 END,
repn1,repn2,repn=repn1+repn2
FROM tb1
FULL OUTER JOIN tb2
ON blu1=blu2