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

vidydeng 2010-10-09 01:30:36

求助一个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

...全文

130 10 点赞打赏收藏举报

写回复

10 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

cjzm83 2010-10-09



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

打赏
举报

回复赞

SQLCenter 2010-10-09

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

*/

打赏
举报

回复赞

SQL2088 2010-10-09

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

打赏
举报

回复赞

vidydeng 2010-10-09

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

打赏
举报

回复赞

SQLCenter 2010-10-09

看错了

打赏
举报

回复赞

SQLCenter 2010-10-09

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

*/