如何从SQL中的表中删除不同的记录 [英] how to delete distinct record from a table in sql

问题描述

假设我有一个表结构，其数据为

suppose i have a table struture with data as

Name id
a      1
a      1
b      1
c      1
d      2
d      2
e      2

我想要将结果集设置为

i want result set as

b    1
c    1
e    2

意味着我想从名称和ID上删除上面的不同记录，我该如何为此编写SQL查询.

Mean i want to delete the distinct record from the above with respect to both name and id ,how can i write a sql query for this.

b    1
c    1
e    2

推荐答案

WITH dups AS
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) AS rn
  FROM your_table
)
DELETE FROM dups WHERE rn>1;

WITH cte_test AS
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Name,ID ORDER BY Name) AS rn
  FROM table1
)
DELETE FROM cte_test WHERE rn>1;

这篇关于如何从SQL中的表中删除不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！