有三张表 T1,T2,T3T1数据北京 A
北京 B
北京 CT2数据北京 1
北京 2
北京 3
北京 4T3数据北京 I
北京 J
北京 K
北京 L
北京 M得到的结果是把三张表合成一张表北京 I 1 A
北京 J 2 B
北京 K 3 C
北京 L 4
北京 M这个SQL如何写?
北京 B
北京 CT2数据北京 1
北京 2
北京 3
北京 4T3数据北京 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
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*/
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*/