根据字段过滤重复行 [英] Filter duplicate rows based on a field
问题描述
我有几条记录(账单)基本上是相互重复的,只有一个字段代表账单所用的语言.
I have several records (bills) that are basically duplicates of each other, except for one field, which represents the language that bill is in.
例如:
ID,BillID,Account,Name,Amount,Lang
1,0001,abcd,John Smith,10.99,ENG
2,0002,qwer,Jane Doe,9.99,ENG
3,0001,abcd,John Smith,10.99,SPA
4,0003,abcd,John Smith,4.99,CHI
所有字段都是字符串,除了 ID,它是一个自动编号.
All fields are strings, except ID, which is an autonumber.
在我的 SQL 选择中,我有
In my SQL select, I have
SELECT *
FROM Bills
WHERE Account='abcd'
它总共返回 3 行,但对于同一张帐单返回 2 行.我需要返回特定帐户的唯一帐单.所以在上面的场景中,我想检索 2 个账单,账单 ID 为 0003,SPA 或 ENG 版本为 0001,但不能同时检索.
and it returns 3 rows in total, but 2 rows for the same bill. I need to return unique bills for a specific account. So in the scenario above, I want to retrieve 2 bills with billID 0003 and either SPA or ENG version of 0001, but not both.
查询会是什么?
谢谢
我不能依赖于始终存在的特定语言.例如,我不能说 SELECT * FROM Bills WHERE Account='abcd' AND Lang='ENG'
因为有时账单可能只有一种语言,而不是 ENG
,有时可能是多种语言的任意组合.
I cannot rely on a specific language always being there. For example, I cannot say SELECT * FROM Bills WHERE Account='abcd' AND Lang='ENG'
because sometimes a bill may be only in one language, which is not ENG
, and sometimes may be in several languages in any combination.
推荐答案
可能最简单的方法是使用 ROW_NUMBER
和 PARTITION BY
Probably the easiest way would be to use ROW_NUMBER
and PARTITION BY
SELECT * FROM (
SELECT b.*,
ROW_NUMBER() OVER (PARTITION BY BillID ORDER BY Lang) as num
FROM Bills b
WHERE Account = 'abcd'
) tbl
WHERE num = 1
这篇关于根据字段过滤重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!