---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-25 15:42:17 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[t1] if object_id('[t1]') is not null drop table [t1] go create table [t1]([total1] int,[datefmt] int) insert [t1] select 4,201404 union all select 816,201405 union all select 127,201406 --> 测试数据[t2] if object_id('[t2]') is not null drop table [t2] go create table [t2]([total2] int,[datefmt] int) insert [t2] select 50,201405 union all select 4,201406 --> 测试数据[t3] if object_id('[t3]') is not null drop table [t3] go create table [t3]([total3] int,[datefmt] int) insert [t3] select 3,201404 union all select 83,201405 union all select 157,201406 --------------生成数据----------------------------select * from [t3]--select * from [t2]select * from [t1] a FULL JOIN [t2] b ON a.datefmt=b.datefmt FULL JOIN [t3] c ON c.datefmt=a.datefmt ----------------结果---------------------------- /* total1 datefmt total2 datefmt total3 datefmt ----------- ----------- ----------- ----------- ----------- ----------- 4 201404 NULL NULL 3 201404 816 201405 50 201405 83 201405 127 201406 4 201406 157 201406 */
你full join的时候join错表了,t1和t3full join,而你用了t2 和t3 full join,前面t1 和t2没问题
这样和我上面写得Full Join也没多大区别啊 大神能解释下吗
因为t1和t3都需要用datefmt相等来查出来,所以你要用t1和t3 full join,但是你用了t2和t3 full join,所以只有t2和t3同时出现的数据才有值,否则为null
你看这样会不会好理解一点:---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-25 15:42:17 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[t1] if object_id('[t1]') is not null drop table [t1] go create table [t1]([total1] int,[datefmt] int) insert [t1] select 4,201404 union all select 816,201405 union all select 127,201406 --> 测试数据[t2] if object_id('[t2]') is not null drop table [t2] go create table [t2]([total2] int,[datefmt] int) insert [t2] select 50,201405 union all select 4,201406 --> 测试数据[t3] if object_id('[t3]') is not null drop table [t3] go create table [t3]([total3] int,[datefmt] int) insert [t3] select 3,201404 union all select 83,201405 union all select 157,201406 --------------生成数据--------------------------SELECT * FROM ( SELECT total1 , a.datefmt AS datefmt1 , total2 , b.datefmt AS datefmt2 FROM [t1] a FULL JOIN [t2] b ON a.datefmt = b.datefmt ) a FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2) /* total1 datefmt1 total2 datefmt2 total3 datefmt ----------- ----------- ----------- ----------- ----------- ----------- 4 201404 NULL NULL 3 201404 816 201405 50 201405 83 201405 127 201406 4 201406 157 201406 */
你是说这样? ---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-25 15:42:17 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[t1] if object_id('[t1]') is not null drop table [t1] go create table [t1]([total1] int,[datefmt] int) insert [t1] select 4,201404 union all select 816,201405 union all select 127,201406 --> 测试数据[t2] if object_id('[t2]') is not null drop table [t2] go create table [t2]([total2] int,[datefmt] int) insert [t2] select 50,201405 union all select 4,201406 --> 测试数据[t3] if object_id('[t3]') is not null drop table [t3] go create table [t3]([total3] int,[datefmt] int) insert [t3] SELECT 10,201403 union all select 3,201404 union all select 83,201405 union all select 157,201406 --------------生成数据--------------------------SELECT * FROM ( SELECT total1 , a.datefmt AS datefmt1 , total2 , b.datefmt AS datefmt2 FROM [t1] a FULL JOIN [t2] b ON a.datefmt = b.datefmt ) a FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2) ORDER BY datefmt1,datefmt2,datefmt/* total1 datefmt1 total2 datefmt2 total3 datefmt ----------- ----------- ----------- ----------- ----------- ----------- NULL NULL NULL NULL 10 201403 4 201404 NULL NULL 3 201404 816 201405 50 201405 83 201405 127 201406 4 201406 157 201406*/
---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-25 15:42:17 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[t1] if object_id('[t1]') is not null drop table [t1] go create table [t1]([total1] int,[datefmt] int) insert [t1] select 4,201404 union all select 816,201405 union all select 127,201406 --> 测试数据[t2] if object_id('[t2]') is not null drop table [t2] go create table [t2]([total2] int,[datefmt] int) insert [t2] select 50,201405 union all select 4,201406 --> 测试数据[t3] if object_id('[t3]') is not null drop table [t3] go create table [t3]([total3] int,[datefmt] int) insert [t3] SELECT 10,201403 union all select 3,201404 union all select 83,201405 union all select 157,201406 --------------生成数据--------------------------SELECT total1,total2,total3,COALESCE (a.datefmt1,a.datefmt2,c.datefmt)datefmt FROM ( SELECT total1 , a.datefmt AS datefmt1 , total2 , b.datefmt AS datefmt2 FROM [t1] a FULL JOIN [t2] b ON a.datefmt = b.datefmt ) a FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2) /* total1 total2 total3 datefmt ----------- ----------- ----------- ----------- 4 NULL 3 201404 816 50 83 201405 127 4 157 201406 NULL NULL 10 201403*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-25 15:42:17
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([total3] int,[datefmt] int)
insert [t3]
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据----------------------------select * from [t3]--select * from [t2]select *
from [t1] a FULL JOIN [t2] b ON a.datefmt=b.datefmt
FULL JOIN [t3] c ON c.datefmt=a.datefmt
----------------结果----------------------------
/*
total1 datefmt total2 datefmt total3 datefmt
----------- ----------- ----------- ----------- ----------- -----------
4 201404 NULL NULL 3 201404
816 201405 50 201405 83 201405
127 201406 4 201406 157 201406
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-25 15:42:17
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([total3] int,[datefmt] int)
insert [t3]
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据--------------------------SELECT *
FROM ( SELECT total1 ,
a.datefmt AS datefmt1 ,
total2 ,
b.datefmt AS datefmt2
FROM [t1] a
FULL JOIN [t2] b ON a.datefmt = b.datefmt
) a
FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2) /*
total1 datefmt1 total2 datefmt2 total3 datefmt
----------- ----------- ----------- ----------- ----------- -----------
4 201404 NULL NULL 3 201404
816 201405 50 201405 83 201405
127 201406 4 201406 157 201406
*/
你是说这样?
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-25 15:42:17
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([total3] int,[datefmt] int)
insert [t3]
SELECT 10,201403 union all
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据--------------------------SELECT *
FROM ( SELECT total1 ,
a.datefmt AS datefmt1 ,
total2 ,
b.datefmt AS datefmt2
FROM [t1] a
FULL JOIN [t2] b ON a.datefmt = b.datefmt
) a
FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2)
ORDER BY datefmt1,datefmt2,datefmt/*
total1 datefmt1 total2 datefmt2 total3 datefmt
----------- ----------- ----------- ----------- ----------- -----------
NULL NULL NULL NULL 10 201403
4 201404 NULL NULL 3 201404
816 201405 50 201405 83 201405
127 201406 4 201406 157 201406*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-25 15:42:17
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([total1] int,[datefmt] int)
insert [t1]
select 4,201404 union all
select 816,201405 union all
select 127,201406
--> 测试数据[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([total2] int,[datefmt] int)
insert [t2]
select 50,201405 union all
select 4,201406
--> 测试数据[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([total3] int,[datefmt] int)
insert [t3]
SELECT 10,201403 union all
select 3,201404 union all
select 83,201405 union all
select 157,201406
--------------生成数据--------------------------SELECT total1,total2,total3,COALESCE (a.datefmt1,a.datefmt2,c.datefmt)datefmt
FROM ( SELECT total1 ,
a.datefmt AS datefmt1 ,
total2 ,
b.datefmt AS datefmt2
FROM [t1] a
FULL JOIN [t2] b ON a.datefmt = b.datefmt
) a
FULL JOIN [t3] c ON c.datefmt = ISNULL(a.datefmt1, a.datefmt2)
/*
total1 total2 total3 datefmt
----------- ----------- ----------- -----------
4 NULL 3 201404
816 50 83 201405
127 4 157 201406
NULL NULL 10 201403*/