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
精彩评论