----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-13 15:34:31
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[表一]
if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([ID] int,[TYPE] varchar(1),[child-TYPE] varchar(2),[Serial No] INT )
insert [表一]
select 1,'A','A1',10001 union all
select 1,'A','A2',10002 union all
select 1,'A','A3',10003
--> 测试数据:[表二]
if object_id('[表二]') is not null drop table [表二]
go
create table [表二]([TYPE] varchar(1),[child-TYPE] varchar(2))
insert [表二]
select 'A','A1' union all
select 'A','A2' union all
select 'A','A3' union all
select 'A','A4' union all
select 'A','A5'
--------------开始查询--------------------------
select ISNULL(a.id,1) ,ISNULL(a.[type],b.[type]),b.[child-TYPE],a.[Serial No]
from [表一] a full JOIN [表二] b ON a.[type]=b.[type] AND a.[child-TYPE]=b.[child-TYPE]
----------------结果----------------------------
/*
child-TYPE Serial No
----------- ---- ---------- -----------
1 A A1 10001
1 A A2 10002
1 A A3 10003
1 A A4 NULL
1 A A5 NULL
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-13 15:34:31
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[表一]
if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([ID] int,[TYPE] varchar(1),[child-TYPE] varchar(2),[Serial No] INT )
insert [表一]
select 1,'A','A1',10001 union all
select 1,'A','A2',10002 union all
select 1,'A','A3',10003
--> 测试数据:[表二]
if object_id('[表二]') is not null drop table [表二]
go
create table [表二]([TYPE] varchar(1),[child-TYPE] varchar(2))
insert [表二]
select 'A','A1' union all
select 'A','A2' union all
select 'A','A3' union all
select 'A','A4' union all
select 'A','A5'
--------------开始查询--------------------------
select ISNULL(a.id,1) ,ISNULL(a.[type],b.[type]),b.[child-TYPE],a.[Serial No]
from [表一] a full JOIN [表二] b ON a.[type]=b.[type] AND a.[child-TYPE]=b.[child-TYPE]
----------------结果----------------------------
/*
child-TYPE Serial No
----------- ---- ---------- -----------
1 A A1 10001
1 A A2 10002
1 A A3 10003
1 A A4 NULL
1 A A5 NULL
*/
from 表二 a
left join 表一 b
on a.[child-TYPE] = b.[child-TYPE]
那个ID 1只是个例子,实际当中这是自增变量,我不可能用1来取代的回复3楼
你的SQL出来的ID那一列也是NULL,这不是我要的继续研究中
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-13 15:34:31
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[表一]
if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([ID] int,[TYPE] varchar(1),[child-TYPE] varchar(2),[Serial No] INT )
insert [表一]
select 1,'A','A1',10001 union all
select 1,'A','A2',10002 union all
select 1,'A','A3',10003 union all
select 2,'A','A3',10003
--> 测试数据:[表二]
if object_id('[表二]') is not null drop table [表二]
go
create table [表二]([TYPE] varchar(1),[child-TYPE] varchar(2))
insert [表二]
select 'A','A1' union all
select 'A','A2' union all
select 'A','A3' union all
select 'A','A4' union all
select 'A','A5'
--------------开始查询--------------------------
select *
from [表一] a full JOIN [表二] b ON a.[type]=b.[type] AND a.[child-TYPE]=b.[child-TYPE]
----------------结果----------------------------
/*
ID TYPE child-TYPE Serial No TYPE child-TYPE
----------- ---- ---------- ----------- ---- ----------
1 A A1 10001 A A1
1 A A2 10002 A A2
1 A A3 10003 A A3
2 A A3 10003 A A3
NULL NULL NULL NULL A A4
NULL NULL NULL NULL A A5*/
继续思考中
如果有2个以上,我希望ID那一列继续有值,如下
1 A A1 10001
1 A A2 10002
1 A A3 10003
1 A A4 NULL
1 A A5 NULL
2 A A1 NULL
2 A A2 NULL
2 A A3 10003
2 A A4 NULL
2 A A5 NULL
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-13 15:34:31
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[表一]
if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([ID] int,[TYPE] varchar(1),[child-TYPE] varchar(2),[Serial No] INT )
insert [表一]
select 1,'A','A1',10001 union all
select 1,'A','A2',10002 union all
select 1,'A','A3',10003 union all
select 2,'A','A3',10003 union all
select 3,'A','A3',10003
--> 测试数据:[表二]
if object_id('[表二]') is not null drop table [表二]
go
create table [表二]([TYPE] varchar(1),[child-TYPE] varchar(2))
insert [表二]
select 'A','A1' union all
select 'A','A2' union all
select 'A','A3' union all
select 'A','A4' union all
select 'A','A5'
--------------开始查询--------------------------SELECT b.id,b.[type],a.[child-TYPE],MAX(CASE WHEN a.[child-TYPE]=b.[child-TYPE] THEN b.[Serial No] ELSE NULL END) [Serial No]
FROM (SELECT [child-TYPE] FROM [表二]) a CROSS JOIN [表一] b --ON a.[child-TYPE]=b.[child-TYPE]
GROUP BY a.[child-TYPE],b.id,b.[type]
ORDER BY b.id
----------------结果----------------------------
/*
id type child-TYPE Serial No
----------- ---- ---------- -----------
1 A A1 10001
1 A A2 10002
1 A A3 10003
1 A A4 NULL
1 A A5 NULL
2 A A1 NULL
2 A A2 NULL
2 A A3 10003
2 A A4 NULL
2 A A5 NULL
3 A A1 NULL
3 A A2 NULL
3 A A3 10003
3 A A4 NULL
3 A A5 NULL*/