表testa:
id aa bb
----------- -------------------- --------------------
1 lix
2 nek
3 nek
4 vvb
表testb:
aa bb
-------------------- --------------------
nek aaa
vvb bbb
现要更新testa表的bb字段为testb表的bb字段，条件是两表的aa相同，但是如果testa的aa有多条记录相同，只更新其中1条的bb即可，更新哪1条没要求，如果用语句update a set a.bb=b.bb from testa a,testb b where a.aa=b.aa，那这样相同记录都会更新，我想要的结果是更新后testa为：
id aa bb
----------- -------------------- --------------------
1 lix
2 nek aaa
3 nek
4 vvb bbb
请高手解答啊，能否通过1个语句实现，谢谢了！
------解决思路----------------------
抛块砖
UPDATE
a
SET
a.bb = b.bb
FROM
[testa] a ,
[testb] b
WHERE
a.aa = b.aa
AND a.id IN ( SELECT
id
FROM
(
SELECT
aa, MAX(id) AS id
FROM
[testa]
GROUP BY
aa
) a )
------解决思路----------------------
奇特的需求，一个SQL语句我搞不定，只能想到如此笨重的方法，期待大神来指教：
create table testa(
id int identity(1,1) primary key,
aa varchar(10),
bb varchar(10)
)
create table testb(
aa varchar(10),
bb varchar(10)
)
insert into testa(aa)
values('lix'),('nek'),('nek'),('vvb')
insert into testb(aa,bb)
values('nek','aaa'),('vvb','bbb')
select
row_number() over(order by newid()) as new_id,
id,
aa,
bb
into #a
from testa
update a set a.bb=c.bb from
testa a inner join
(
select a.id,a.aa from #a a inner join
(
select min(new_id) new_id,aa from #a group by aa
) b on a.new_id=b.new_id
) b on a.aa=b.aa and a.id=b.id
inner join testb c on a.aa=c.aa
select * from testa
/***********
结果
id aa bb
1 lix NULL
2 nek NULL
3 nek aaa
4 vvb bbb
5 lix NULL
6 nek NULL
7 nek NULL
8 vvb NULL
************/
------解决思路----------------------
-- 使用 row_number 函数
create table a(id int , aa varchar(10) , bb varchar(10))
go
insert into a values
(1, 'lix',null),
(2, 'nek',null),
(3, 'nek',null),
(4, 'vvb',null),
(5, 'vvb',null)
go
create table b(aa varchar(10) , bb varchar(10))
go
insert into b values
('nek', 'aaa'),
('vvb', 'bbb')
go
select * from a
go
select * from b
go
with m as (
select a.* , row_number() over(partition by aa order by id) rn from a
)
update m set bb = b.bb
from m , b
where m.rn = 1 and m.aa = b.aa
go
select * from a
go
drop table a ,b
go
(5 行受影响)
(2 行受影响)
id aa bb
----------- ---------- ----------
1 lix NULL
2 nek NULL
3 nek NULL
4 vvb NULL
5 vvb NULL
(5 行受影响)
aa bb
---------- ----------
nek aaa
vvb bbb
(2 行受影响)
(2 行受影响)
id aa bb
----------- ---------- ----------
1 lix NULL
2 nek aaa
3 nek NULL
4 vvb bbb
5 vvb NULL
(5 行受影响)