求个SQL 急求 非常感谢

[我2我骄傲]

有三张表 T1,T2，T3

T1数据

北京 A
北京 B
北京 C

T2数据

北京 1
北京 2
北京 3
北京 4

T3数据

北京 I
北京 J
北京 K
北京 L
北京 M

得到的结果是把三张表合成一张表

北京 I 1 A
北京 J 2 B
北京 K 3 C
北京 L 4
北京 M

这个SQL如何写?

[人生无悔]

--北京地点列是你表的第一列，数据列是你表的第二列
select 
 coalesce(T1.北京地点列,T2.北京地点列,T3.北京地点列) 北京地点列,
 isnull(T3.数据列,0) [数据列T3表],
 isnull(T2.数据列,0) [数据列T2表],
 isnull(T1.数据列,0) [数据列T1表]
from T1
full join T2 on T1.北京地点列=T2.北京地点列
full join T3 on T1.北京地点列=T3.北京地点列;

[问苍天人生几何]

;with t1 as
(
 select row_number() over(order by col2) rn,* from T1
),
t2 as
(
 select row_number() over(order by col2) rn,* from T2
),
t3 as
(
 select row_number() over(order by col2) rn,* from T3
)
select a.col1,a.col2,b.col2,c.col2 from T3 a
left join T2 b
on a.rn=b.rn
left join T1 c
on a.rn=c.rn

[mugua604]

CREATE TABLE TABLE1(City NVARCHAR(20),ID NVARCHAR(10))
INSERT INTO TABLE1
SELECT  '北京','A'
UNION ALL 
SELECT  '北京','B'
UNION ALL 
SELECT  '北京','C'


CREATE TABLE TABLE2(City NVARCHAR(20),ID NVARCHAR(10))
INSERT INTO TABLE2
SELECT  '北京','1'
UNION ALL 
SELECT  '北京','2'
UNION ALL 
SELECT  '北京','3'
UNION ALL 
SELECT  '北京','4'


CREATE TABLE TABLE3(City NVARCHAR(20),ID NVARCHAR(10))
INSERT INTO TABLE3
SELECT  '北京','I'
UNION ALL 
SELECT  '北京','J'
UNION ALL 
SELECT  '北京','K'
UNION ALL 
SELECT  '北京','L'
UNION ALL 
SELECT  '北京','M'


SELECT T0.City,T0.ID,T1.ID,T2.ID
FROM
master.dbo.spt_values A1
LEFT JOIN 
(
	SELECT ROW_NUMBER() OVER (ORDER BY ID) LineNum,City,ID
	FROM TABLE3
) T0 ON A1.Number =T0.LineNum
LEFT JOIN 
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) LineNum,City,ID
FROM TABLE2
) T1 ON T0.LineNum = T1.LineNum
LEFT JOIN 
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) LineNum,City,ID
FROM TABLE1
) T2 ON T0.LineNum = T2.LineNum
WHERE A1.Type='P' AND (T0.City IS NOT NULL OR T1.City IS NOT NULL OR T2.City is not null)

/*
City                 ID         ID         ID
-------------------- ---------- ---------- ----------
北京                   I          1          A
北京                   J          2          B
北京                   K          3          C
北京                   L          4          NULL
北京                   M          NULL       NULL

(5 行受影响)

*/

[叶子]

--> 测试数据: @T1
declare @T1 table (name varchar(4),value varchar(1))
insert into @T1
select '北京','A' union all
select '北京','B' union all
select '北京','C'

--> 测试数据: @T2
declare @T2 table (name varchar(4),value int)
insert into @T2
select '北京',1 union all
select '北京',2 union all
select '北京',3 union all
select '北京',4

--> 测试数据: @T3
declare @T3 table (name varchar(4),value varchar(1))
insert into @T3
select '北京','I' union all
select '北京','J' union all
select '北京','K' union all
select '北京','L' union all
select '北京','M'

;with m1 as
(
select row_number() over (order by getdate()) as id,* from @T1
),m2 as
(
select row_number() over (order by getdate()) as id,* from @T2
),m3 as
(
select row_number() over (order by getdate()) as id,* from @T3
)

select c.name,c.value as c1,b.value as c2,a.value as c3
from m3 c left join m2 b on c.id=b.id
left join m1 a on a.id=c.id
/*
name c1   c2          c3
---- ---- ----------- ----
北京   I    1           A
北京   J    2           B
北京   K    3           C
北京   L    4           NULL
北京   M    NULL        NULL

*/

[mugua604]

个人考虑了T3的表中的记录小于其他2个表的记录，会出问题的。。

[以学习为目的]

--> 测试数据: @T1
declare @T1 table (name varchar(4),value varchar(1))
insert into @T1
select '北京','A' union all
select '北京','B' union all
select '北京','C'

--> 测试数据: @T2
declare @T2 table (name varchar(4),value int)
insert into @T2
select '北京',1 union all
select '北京',2 union all
select '北京',3 union all
select '北京',4

--> 测试数据: @T3
declare @T3 table (name varchar(4),value varchar(1))
insert into @T3
select '北京','I' union all
select '北京','J' union all
select '北京','K' union all
select '北京','L' union all
select '北京','M'
-->开始查询
;with 
m1 as
(
select rn=row_number() over (order by getdate()),* from @T1
),
m2 as
(
select rn=row_number() over (order by getdate()),* from @T2
),
m3 as
(
select rn=row_number() over (order by getdate()),* from @T3
)

select c.name,c.value as c1,b.value as c2,a.value as c3
from m3 c left join m2 b on c.rn=b.rn
left join m1 a on a.rn=c.rn
-->结果集
/*
name c1   c2          c3
---- ---- ----------- ----
北京   I    1           A
北京   J    2           B
北京   K    3           C
北京   L    4           NULL
北京   M    NULL        NULL

*/