求助查询：用NULL替代左表的重复行？？

[vidydeng]

求助一个sql查询怎么写啊
有表A（col1），数据如下
col1
1
2
3
有表B（col1（外键），col2（主键））数据如下
col1 col2
1 1
1 2
1 3
通过连接查询
select * from A
left join B on A.col1=B.col1
得到结果
col1 col1 col2
1 1 1
1 1 2
1 1 3
2 NULL NULL
3 NULL NULL
问题：现想把刚才连接查询的左表的重复的列用null替换得到如下结果，怎么操作？
怎么得到这样的结果：
1 1 1
NULL NULL 2
NULL NULL 3
2 NULL NULL
3 NULL NULL

[kevn]

第一行的1 1 1 为什么前两个没换成null？

[vidydeng]

就是想保留第一行的值 ，后面重复了的。就改成null

[lcqtgb]

[Quote=引用 1 楼 kevinwen0413 的回复:]
第一行的1 1 1 为什么前两个没换成null？
[/Quote]
我也是如此想。。

[lcqtgb]

不重复啊。。晕死。。。

[SQLCenter]

--> 测试数据：#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
*/

[SQLCenter]

看错了

[vidydeng]

如果对与a表中存在多列，怎么处理呢？

[SQL2088]

这些语句，前台很容易，后台也可以，就是比较烦

[SQLCenter]

--> 测试数据：#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
*/

[cjzm83]

--> 测试数据：#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