有三张表 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如何写?
------解决方案--------------------
- SQL code
--北京地点列是你表的第一列，数据列是你表的第二列select coalesce(T1.北京地点列,T2.北京地点列,T3.北京地点列) 北京地点列, isnull(T3.数据列,0) [数据列T3表], isnull(T2.数据列,0) [数据列T2表], isnull(T1.数据列,0) [数据列T1表]from T1full join T2 on T1.北京地点列=T2.北京地点列full join T3 on T1.北京地点列=T3.北京地点列;
------解决方案--------------------
- SQL code
;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 aleft join T2 bon a.rn=b.rnleft join T1 con a.rn=c.rn
------解决方案--------------------
- SQL code
CREATE TABLE TABLE1(City NVARCHAR(20),ID NVARCHAR(10))INSERT INTO TABLE1SELECT '北京','A'UNION ALL SELECT '北京','B'UNION ALL SELECT '北京','C'CREATE TABLE TABLE2(City NVARCHAR(20),ID NVARCHAR(10))INSERT INTO TABLE2SELECT '北京','1'UNION ALL SELECT '北京','2'UNION ALL SELECT '北京','3'UNION ALL SELECT '北京','4'CREATE TABLE TABLE3(City NVARCHAR(20),ID NVARCHAR(10))INSERT INTO TABLE3SELECT '北京','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.IDFROMmaster.dbo.spt_values A1LEFT JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY ID) LineNum,City,ID FROM TABLE3) T0 ON A1.Number =T0.LineNumLEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY ID) LineNum,City,IDFROM TABLE2) T1 ON T0.LineNum = T1.LineNumLEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY ID) LineNum,City,IDFROM TABLE1) T2 ON T0.LineNum = T2.LineNumWHERE 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 行受影响)*/
------解决方案--------------------
- SQL code
--> 测试数据: @T1declare @T1 table (name varchar(4),value varchar(1))insert into @T1select '北京','A' union allselect '北京','B' union allselect '北京','C'--> 测试数据: @T2declare @T2 table (name varchar(4),value int)insert into @T2select '北京',1 union allselect '北京',2 union allselect '北京',3 union allselect '北京',4--> 测试数据: @T3declare @T3 table (name varchar(4),value varchar(1))insert into @T3select '北京','I' union allselect '北京','J' union allselect '北京','K' union allselect '北京','L' union allselect '北京','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 c3from m3 c left join m2 b on c.id=b.idleft 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*/
------解决方案--------------------
- SQL code
--> 测试数据: @T1declare @T1 table (name varchar(4),value varchar(1))insert into @T1select '北京','A' union allselect '北京','B' union allselect '北京','C'--> 测试数据: @T2declare @T2 table (name varchar(4),value int)insert into @T2select '北京',1 union allselect '北京',2 union allselect '北京',3 union allselect '北京',4--> 测试数据: @T3declare @T3 table (name varchar(4),value varchar(1))insert into @T3select '北京','I' union allselect '北京','J' union allselect '北京','K' union allselect '北京','L' union allselect '北京','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 c3from m3 c left join m2 b on c.rn=b.rnleft 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*/