删除sql查询中的重复行

Question

我有一个表，结构如下

[id] [int] IDENTITY(1,1) NOT NULL,
[account_number] [int] NOT NULL,
[account_name] [varchar(100)] NULL,
[account_chapter] [varchar(20)] NULL,

可以有多个行具有相同的account_number，但是不同的account_name和account_chapter。

例如，我们可以有如下内容:

id  account_number  account_name  account_chapter
12  1111              Name01        chapter01
13  1111              Name02        chapter02
14  2222              Name03        chapter07
15  2222              Name05        chapter11
16  7777              Name06        chapter44

我想要的是一个查询，对于每个account_number，只过滤表中的第一个出现。例如，上面的查询必须按照以下方式进行转换:

id  account_number  account_name  account_chapter
12  1111              Name01        chapter01
14  2222              Name03        chapter07
16  7777              Name06        chapter44

下面是我写的查询:

with req01 as (select distinct account_number from accounts)
select * from req01 full join (select  * from accounts) as p on p.account_number = req01.account_number 

没有产生预期的结果。

有什么帮助吗?谢谢。

Answer 1

使用ROW_NUMBER:

SELECT TOP 1 WITH TIES *
FROM accounts
ORDER BY ROW_NUMBER() OVER (PARTITION BY account_number ORDER BY account_chapter);

或者，更典型地使用ROW_NUMBER:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY account_number
ORDER BY account_chapter) rn
FROM accounts
)
SELECT id, account_number, account_name, account_chapter
FROM cte
WHERE rn = 1;

请注意，这两个答案都假设account_chapter版本确定了哪些"重复"。

Answer 2

通常情况下，表中的出现将由日期/时间或标识列确定。您只有标识符，所以您似乎想:

select a.*
from accounts a
where a.id = (select min(a2.id)
from accounts a2
where a2.account_number = a.account_number
);