一条删除重复的sql语句

求救一条删除重复的sql语句


SELECT *
FROM User_Property a
WHERE  ((SELECT COUNT(property_mall_ID)
FROM User_Property
WHERE property_mall_ID=a.property_mall_ID and  User_Property_UserName = a.User_Property_UserName)&gt;1 )
ORDER BY User_Property_UserName DESC

我这里面查询出来的，都是property_mall_ID重复的记录
比如
ID username property_mall_ID
1 aaaa 1
2 aaaa 1
3 aaaa 2
4 aaaa 2
5 bbbb 1
6 bbbb 1
7 cccc 5
8 cccc 5
.......

我需要一条sql语句，删除重复的，保留ID为最小的一条记录(表里面有几万条数据)

删除后的记录为
ID username property_mall_ID
1 aaaa 1
3 aaaa 2
5 bbbb 1
7 cccc 5
.....

求哪位大侠Help一下 sql
[解决办法]



delete from User_Property where  id in
(
select id from
(
(
SELECT     dense_rank() OVER (partition BY username+property_mall_ID   ORDER BY property_mall_ID
 ASC) AS AId,id from User_Property 
) 
) t where t.aid&lt;&gt;1
)

手写的不知道可以不可以你先备份下表在执行免得弄错了.
[解决办法]
一楼正确答案。。。
[解决办法]

引用:

有哪位大神指导一下、、、、、


delete from ax
 where id 
[解决办法]
 username 
[解决办法]
 property_mall_id in
       (select id 
[解决办法]
 username 
[解决办法]
 property_mall_id
          from (select id,
                       username,
                       property_mall_id,
                       row_number() over(partition by username, property_mall_id order by id) rn
                  from ax)
         where rn &lt;&gt; '1');

        ID USERNAME PROPERTY_MALL_ID ROWID
---------- -------- ---------------- ------------------
         1 aaaa                    1 AAASRaAAGAAAAYbAAA
         3 aaaa                    2 AAASRaAAGAAAAYbAAC
         5 bbbb                    1 AAASRaAAGAAAAYbAAE
         7 cccc                    5 AAASRaAAGAAAAYbAAG

[解决办法]
在*号前面加一个distinct就ok了
[解决办法]
delete from test where id in (select max(id) from test group by username,property_mall_ID having COUNT(*)>1)

[解决办法]
delete from User_Property where id not in
(
select min(id) from User_Property group by username
)

[解决办法]
delete from 表名 where id not in
(select min(id) from 表名 group by property_mall_ID)

一条删除重复的sql语句

热点推荐