两个表的join查询
表一
ID TYPE child-TYPE Serial No.
1 A A1 10001
1 A A2 10002
1 A A3 10003
表二
TYPE child-TYPE
A A1
A A2
A A3
A A4
A A5
希望得到结果
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
该怎么写呢 ？
[解决办法]
----------------------------------------------------------------
-- 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
*/
select b.id,a.type,a.[child-TYPE],b.[Serial No.]
from 表二 a
left join 表一 b
on a.[child-TYPE] = b.[child-TYPE]
