使用COUNT函数跨多个列进行匹配? [英] Using COUNT function to match across multiple columns?
问题描述
我正在尝试构造一个Oracle SQL查询，以便在两列中的表中查找匹配的数据.
我有一张大桌子，上面有以下各列(除其他外):
"ModuleItemID" [主键int]
匹配模块名称查找表的"ModuleNumber" [int]列
"PartNo"列[string]
单个ModuleNumber可以与多个PartNo关联，但是每个PartNo只能与每个ModuleNumber一起出现一次.
我需要根除以相同ModuleNumber出现两次的PartNos的多个实例-即使用计数大于1的特定ModuleNumber和PartNo组合查找所有ModuleItemID行"-但我不确定确切的语法是什么.
在下面的示例中，我想查找重复的行，例如1& 3和7& 9:
| ModuleItemID | ModuleNumber | PartNo |
|---|---|---|
| 1 | 1 | 161-4514 |
| 2 | 1 | 168-0056 |
| 3 | 1 | 161-4514 |
| 4 | 2 | 160-4514 |
| 5 | 2 | 168-0056 |
| 6 | 3 | 160-4514 |
| 7 | 3 | 171-0023 |
| 8 | 3 | 181-0565 |
| 9 | 3 | 171-0023 |
谢谢！
I''m trying to construct an Oracle SQL query to find matching data in a table across two columns.
I have a large table with the following columns (among others):
''ModuleItemID'' [Primary Key int]
''ModuleNumber'' [int] column matching to a lookup table of Module Names
''PartNo'' column [string]
A single ModuleNumber can be associated with many PartNo''s, but each PartNo should only appear with each ModuleNumber once.
I need to root out several instances of PartNos that appear twice with the same ModuleNumber - i.e. "find all ModuleItemID rows with a particular ModuleNumber and PartNo combination where count >1" - but I''m not sure what the exact syntax is.
In the example below, I want to find duplicate rows like 1 & 3 and 7 & 9:
| ModuleItemID | ModuleNumber | PartNo |
|---|---|---|
| 1 | 1 | 161-4514 |
| 2 | 1 | 168-0056 |
| 3 | 1 | 161-4514 |
| 4 | 2 | 160-4514 |
| 5 | 2 | 168-0056 |
| 6 | 3 | 160-4514 |
| 7 | 3 | 171-0023 |
| 8 | 3 | 181-0565 |
| 9 | 3 | 171-0023 |
Thank you!
推荐答案
这应该为您提供模块/部件号列表以及它们在表中出现的次数:
SELECT ModuleNumber, PartNo, COUNT(*) as ModulePartCount FROM MyTable GROUP BY ModuleNumber, PartNo
如果只需要重复项，则可以添加一个having子句:
SELECT ModuleNumber, PartNo, COUNT(*) as ModulePartCount FROM MyTable GROUP BY ModuleNumber, PartNo HAVING COUNT(*) > 1
那是您要找的东西吗?如果不是，请准确说明您的最终结果.
This should get you a list of Module/Part Numbers and how many times they occur in the table:
SELECT ModuleNumber, PartNo, COUNT(*) as ModulePartCount FROM MyTable GROUP BY ModuleNumber, PartNo
If you only want duplicates, you can add a having clause:
SELECT ModuleNumber, PartNo, COUNT(*) as ModulePartCount FROM MyTable GROUP BY ModuleNumber, PartNo HAVING COUNT(*) > 1
Is that what you are looking for? If not, please state exactly what your end result is supposed to be.
这篇关于使用COUNT函数跨多个列进行匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
