22,300
社区成员




WITH A(id,content) AS(
SELECT 1,'a1'
),
B(id,content) AS(
SELECT 1,'b1'UNION ALL
SELECT 2,'b2'
),
C(id,content) as(
SELECT 1,'c1' UNION ALL
SELECT 2,'c2' UNION ALL
SELECT 3,'c3'
)
select T1.id,conA,conB,Tc.content as conC from (
select TA.id,TA.content as conA,TB.content as conB,TA.num,TB.num1 from (
select *,ROW_NUMBER() over(order by id) as num from A) as TA
right join
(select *,ROW_NUMBER() over(order by id) as num1 from B) as TB
on TA.num=TB.num1
) as T1
right join
(select *,ROW_NUMBER() over(order by id) as num2 from C) as TC
on T1.num=TC.num2
SELECT ISNULL(RTRIM(a.Id),'') AS id,ISNULL(a.A,'') AS A,ISNULL(b.B,'') AS B,ISNULL(c.C,'') AS C FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY RAND()) AS RN FROM @A) AS a RIGHT JOIN(SELECT *,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY RAND()) AS RN FROM @B) AS b ON a.ID=b.ID AND a.RN=b.RN RIGHT JOIN (SELECT*,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY RAND()) AS RN FROM @C) AS c ON c.ID=b.ID AND c.RN=b.RN
/*
id A B C
1 a1 b1 c1
b2 c2
c3
*/
-- NULL值,你自己按实际业务处理一下;
create table A(id int , name varchar(10))
create table B(id int , name varchar(10))
create table C(id int , name varchar(10))
insert into A values(100,'a1');
insert into B values(1,'b1'),(2,'b2');
insert into C values(1,'c1'),(2,'c2'),(3,'c3');
with A1 as(
select row_number() over(order by getdate()) rn , * from A
),
B1 as(
select row_number() over(order by getdate()) rn , * from B
),
C1 as(
select row_number() over(order by getdate()) rn , * from C
)
select A1.name , B1.name,C1.name
from A1
full join B1 on A1.rn = B1.rn
full join C1 on isnull(A1.rn,B1.rn) = C1.rn
go
drop table A,B,C
go
(1 行受影响)
(2 行受影响)
(3 行受影响)
name name name
---------- ---------- ----------
a1 b1 c1
NULL b2 c2
NULL NULL c3
(3 行受影响)