22,301
社区成员




--> 测试数据:#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(col1 int)
insert into #a
select 1 union all
select 2 union all
select 3
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(col1 int, col2 int)
insert into #b
select 1, 1 union all
select 1, 2 union all
select 1, 3
;with tb as
(
select row_number() over (order by a.col1) as num,
row_number() over (partition by a.col1 order by getdate()) as num_p,
a.col1 as acol1,b.col1 as bcol1,b.col2
from #a A left join #b B on A.col1=B.col1
)
select num,acol1,bcol1,col2 from tb where num_p = 1
union all
select num,null,null,col2 from tb where num_p > 1
order by num
--> 测试数据:#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(col1 int)
insert into #a
select 1 union all
select 2 union all
select 3
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(col1 int, col2 int)
insert into #b
select 1, 1 union all
select 1, 2 union all
select 1, 3
select
acol1= case when bcol1 is null then acol1 when bcol2=mcol2 then acol1 else null end,
bcol1 = case bcol2 when mcol2 then bcol1 end,
bcol2
from
(select a.col1 acol1, b.col1 bcol1, b.col2 bcol2, min(b.col2)over(partition by b.col1)mcol2 from #a A left join #b B on A.col1=B.col1) t
order by t.acol1
/*
acol1 bcol1 bcol2
----------- ----------- -----------
1 1 1
NULL NULL 2
NULL NULL 3
2 NULL NULL
3 NULL NULL
*/
--> 测试数据:#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(col1 int)
insert into #a
select 1 union all
select 2 union all
select 3
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(col1 int, col2 int)
insert into #b
select 1, 1 union all
select 1, 2 union all
select 1, 3
select a.col1,
case when b.col2=(select min(col2) from #b where col1=B.col1) then b.col2 end bcol1,
b.col2
from #a A left join #b B on A.col1=B.col1
/*
col1 bcol1 col2
----------- ----------- -----------
1 1 1
1 NULL 2
1 NULL 3
2 NULL NULL
3 NULL NULL
*/