1、delete B where ONAME not in (select max(ONAME) from B group by UNAME)2、alter table A Add ONAME varchar(50) nullupdate A set ONAME=(select B.ONAME from B where A.UNAME=B.UNAME)
create table B ( UNAME varchar(20) null, ONAME varchar(50) null )insert into B select 'AAA','A1' union ALL select 'AAA','A2' union ALL select 'AAA','A3' union ALL select 'AAA','A4' union ALL select 'BBB','B1' union ALL select 'BBB','B2' union ALL select 'BBB','B3' union ALL select 'BBB','B4' union ALL select 'CCC','C4' union ALL select 'DDD','D4'delete B where ONAME not in (select max(ONAME) from B group by UNAME)select * from B------------------------------ AAA A4 BBB B4 CCC C4 DDD D4
select max(ONAME) from B group by UNAME 这句是什么作用?找出 UNAME 對應的一條 ONAME ,
select max(ONAME) from B group by UNAME 这句是什么作用?找出 UNAME 對應的一條 ONAME ,這條記錄將被保留,其餘紀錄將被刪除!也可以用 select min(ONAME) from B group by UNAME
delete from a where uname in (select count(uname) from a group by uname having(count(uname)>1) ) 不知道这样能不能帮到你
不好意思,应该是b表.不是a表. delete from b where uname in (select count(uname) from b group by uname having(count(uname)>1) )
pennyzhueng(摩摩茶),你的我执行后,提示: “将 nvarchar 值 转换为数据类型为 int 的列时发生语法错误” 为什么?是不是 这句有问题?——having(count(uname)>1
搞定了,第一个问题的两种写法: delete from B where Uname in ( select Uname from ( select Uname ,count(Uname ) as b from B group by Uname ) as b where b.b>1 ) ================================ delete from B where Uname in( SELECT UNAME FROM B a WHERE EXISTS (SELECT * FROM B WHERE UNAME= a.UNAME AND ONAME <> a.ONAME ))
(
UNAME varchar(20) null,
ONAME varchar(50) null
)insert into B select 'AAA','A1'
union ALL select 'AAA','A2'
union ALL select 'AAA','A3'
union ALL select 'AAA','A4'
union ALL select 'BBB','B1'
union ALL select 'BBB','B2'
union ALL select 'BBB','B3'
union ALL select 'BBB','B4'
union ALL select 'CCC','C4'
union ALL select 'DDD','D4'delete B where ONAME not in (select max(ONAME) from B group by UNAME)select * from B------------------------------
AAA A4
BBB B4
CCC C4
DDD D4
你的处理方法是如果UNAME同名的话,保留一个,删去其他的,但是没有考虑到UID是否与ONAME相互对应。
from a
where uname in (select count(uname)
from a
group by uname
having(count(uname)>1)
)
不知道这样能不能帮到你
delete
from b
where uname in (select count(uname)
from b
group by uname
having(count(uname)>1)
)
“将 nvarchar 值 转换为数据类型为 int 的列时发生语法错误”
为什么?是不是 这句有问题?——having(count(uname)>1
delete from B where Uname in (
select Uname from (
select Uname ,count(Uname ) as b from B
group by Uname ) as b
where b.b>1 )
================================
delete from B where Uname in(
SELECT UNAME
FROM B a
WHERE EXISTS
(SELECT *
FROM B
WHERE UNAME= a.UNAME AND ONAME <> a.ONAME ))