开发者

SQL select rows that have (partially) duplicated data

开发者 https://www.devze.com 2023-01-29 06:09 出处:网络
I have the following database schema: Product ID | Component | ... Product ID - a foreign key Component - parts of the Product

I have the following database schema:

Product ID | Component | ...

Product ID - a foreign key

Component - parts of the Product

For some Arcane reason a number of Records have the same Product ID & Component. Is there a SQL query that would return all the Product ID's & Components, that have multiple identical Components?

E.g. given the following table

| Product ID | Component |
--------------------------
| 1          | c1000     |
| 1          | c1100     |
| 2          | c2000     |
| 2          | c2000     |
| 2开发者_如何学C          | c2200     |
| 3          | c3000     |

The SQL query should return:

| Product ID | Component |
--------------------------
| 2          | c2000     |


SELECT
  ProductId,
  Component
FROM
  Table
GROUP BY
  ProductId,
  Component
HAVING
  COUNT(*) > 1


SELECT ProductId, Component, count(*) Duplicates
 from MyTable  --  or whatever
 group by ProductId, Component
 having count(*) > 1

This will also show you how many duplicate entries there are.


select "Product ID", Component
from table
group by "Product ID", Component
having count(*) > 1
0

精彩评论

暂无评论...
验证码 换一张
取 消