show duplicate records
I have this query
SELECT id, timeOrdered, order_desc, firstname, lastname
FROM `db`.`myTable`
WHERE `myTable`.`cu开发者_如何学运维stomer_number` IN (100, 101, 102, 103, 104, 105)
I am trying to find which are duplicate records. If the timeOrdered, order_desc, firstname, lastname are same then its dupe.
What would be the query for that
ThanksTo see what values have duplicates:
SELECT t.order_desc,
t.firstname,
t.lastname
FROM db.mytable t
WHERE t.customer_number IN (100, 101, 102, 103, 104, 105)
GROUP BY t.order_desc, t.firstname, t.lastname, t.timeordered
HAVING COUNT(*) > 1
To see the entire record associated with those duplicates:
SELECT x.*
FROM db.mytable x
WHERE EXISTS(SELECT NULL
FROM db.mytable t
WHERE t.customer_number IN (100, 101, 102, 103, 104, 105)
AND t.order_desc = x.order_desc
AND t.firstname = x.firstname
AND t.lastname = x.lastname
AND t.timeordered = x.timeordered
GROUP BY t.order_desc, t.firstname, t.lastname, t.timeordered
HAVING COUNT(*) > 1)
WHERE x.customer_number IN (100, 101, 102, 103, 104, 105)
SELECT DISTINCT
t1.id
FROM
mytable t1
INNER JOIN mytable t2 ON
t1.timeordered = t2.timeordered AND
t1.order_desc = t2.order_desc AND
t1.firstname = t2.firstname AND
t1.lastname = t2.lastname AND
t1.id <> t2.id
精彩评论