34,588
社区成员
发帖
与我相关
我的任务
分享
create table t1(id int,col int)
create table t2(id int,col int)
insert into t1 select 1,10 union all select 2,20
insert into t2 select 2,40 union all select 3,50
go
select t1.id,t1.col,t2.id,t2.col from t1 full join t2 on t1.id=t2.id
/*
id col id col
----------- ----------- ----------- -----------
1 10 NULL NULL
2 20 2 40
NULL NULL 3 50
(3 行受影响)
*/
go
drop table t1,t2
SELECT Stu.sname,(case when Two.rid<>(select min(rid) from ReportTwo)
then null else one.rnameOne end)as [rnameOne],
rnameTwo
FROM StudentInfo AS Stu
FULL JOIN ReportOne as one on Stu.id = one.id
FULL JOIN ReportTwo AS Two on Stu.id = Two.id
select sname,case rid when 1 then rnameOne else null end as rnameOne,rnameTwo from (
SELECT row_number() over(partition by RTOne.rnameOne order by RTOne.rnameOne)
as rid,
St.sname,RTOne.rnameOne,RTTwo.rnameTwo FROM StudentInfo AS St
LEFT JOIN ReportOne AS RTOne ON ST.id = RTOne.id
LEFT JOIN ReportTwo as RTTwo ON ST.id = RTTwo.id) aa
/*
sname rnameOne rnameTwo
-------------------- -------------------- --------------------
Hello Hello的报表One Hello的报表TwoA
Hello NULL Hello的报表TwoB
*/