----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-18 21:44:18
-- 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)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Aid] int,[Aname] nvarchar(6))
insert [A]
select 1001,'sss' union all
select 1002,'win' union all
select 1003,'stu' union all
select 1004,'red' union all
select 1005,'ble'
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([Aid] int,[Cid] int,[Bdate] datetime)
insert [B]
select 1001,1,'2014-1-1' union all
select 1003,2,'2014-1-2'
--> 测试数据[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([Aid] int,[Cid] int,[Cdate] datetime)
insert [C]
select 1002,3,'2014-2-1' union all
select 1004,4,'2014-2-2'
--------------生成数据--------------------------select a.aid,a.aNAME,ISNULL(b.cid,c.cid)cid, Bdate ,Cdate
from [A] LEFT JOIN [b] ON a.aid=b.aid
LEFT JOIN c ON a.aid=c.aid
----------------结果----------------------------
/*
aid aNAME cid Bdate Cdate
----------- ------ ----------- ----------------------- -----------------------
1001 sss 1 2014-01-01 00:00:00.000 NULL
1002 win 3 NULL 2014-02-01 00:00:00.000
1003 stu 2 2014-01-02 00:00:00.000 NULL
1004 red 4 NULL 2014-02-02 00:00:00.000
1005 ble NULL NULL NULL
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-18 21:44:18
-- 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)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Aid] int,[Aname] nvarchar(6))
insert [A]
select 1001,'sss' union all
select 1002,'win' union all
select 1003,'stu' union all
select 1004,'red' union all
select 1005,'ble'
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([Aid] int,[Cid] int,[Bdate] datetime)
insert [B]
select 1001,1,'2014-1-1' union all
select 1003,2,'2014-1-2'
--> 测试数据[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([Aid] int,[Cid] int,[Cdate] datetime)
insert [C]
select 1002,3,'2014-2-1' union all
select 1004,4,'2014-2-2'
--------------生成数据--------------------------select a.aid,a.aNAME,ISNULL(b.cid,c.cid)cid, Bdate ,Cdate
from [A] LEFT JOIN [b] ON a.aid=b.aid
LEFT JOIN c ON a.aid=c.aid
----------------结果----------------------------
/*
aid aNAME cid Bdate Cdate
----------- ------ ----------- ----------------------- -----------------------
1001 sss 1 2014-01-01 00:00:00.000 NULL
1002 win 3 NULL 2014-02-01 00:00:00.000
1003 stu 2 2014-01-02 00:00:00.000 NULL
1004 red 4 NULL 2014-02-02 00:00:00.000
1005 ble NULL NULL NULL
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-18 21:44:18
-- 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)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Aid] int,[Aname] nvarchar(6))
insert [A]
select 1001,'sss' union all
select 1002,'win' union all
select 1003,'stu' union all
select 1004,'red' union all
select 1005,'ble'
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([Aid] int,[Cid] int,[Bdate] date)
insert [B]
select 1001,1,'2014-1-1' union all
select 1003,2,'2014-1-2'
--> 测试数据[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([Aid] int,[Cid] int,[Cdate] date)
insert [C]
select 1002,3,'2014-2-1' union all
select 1004,4,'2014-2-2'
--------------生成数据--------------------------select a.aid,a.aNAME,ISNULL(b.cid,c.cid)cid, Bdate ,Cdate
from [A] LEFT JOIN [b] ON a.aid=b.aid
LEFT JOIN c ON a.aid=c.aid
----------------结果----------------------------
/*
aid aNAME cid Bdate Cdate
----------- ------ ----------- ---------- ----------
1001 sss 1 2014-01-01 NULL
1002 win 3 NULL 2014-02-01
1003 stu 2 2014-01-02 NULL
1004 red 4 NULL 2014-02-02
1005 ble NULL NULL NULL
*/