A complex group by SQL query
Lets say there is free car tha开发者_运维技巧t you can win if your participate in the draw. To participate, you supply name, address, phone number and email. Only different email is required to participate in draw. More than 100,000 people participated in the draw. Many of them used 2 or 3 different emails but have use the same name, address and phone numbers. Write a query that tell me how many duplicates there are in the table. My situation is similar to this.
Here is the table format
RecID Name Address Phone email
Assume all records are nvarchar except RecID which is int. Also assume all fields are supplied. I do not know how to use group by when 3 columns match in required.
SELECT Name, Address, Phone, COUNT(*) as NumEntries
FROM YourTable
GROUP BY Name, Address, Phone
HAVING COUNT(*) > 1 /* Duplicates exist */
And, if you'd also want the individual email addresses included:
SELECT t.Name, t.Address, t.Phone, t.email, q.NumEntries
FROM YourTable t
INNER JOIN (SELECT Name, Address, Phone, COUNT(*) as NumEntries
FROM YourTable
GROUP BY Name, Address, Phone
HAVING COUNT(*) > 1 /* Duplicates exist */) q
ON t.Name = q.Name
AND t.Address = q.Address
AND t.Phone = q.Phone
ORDER BY t.Name, t.Address, t.Phone, t.email
You just use all the fields that are supposed to be the same in your GROUP BY
:
SELECT Name, Address, Phone, Count(*)
from MyTAble
GROUP BY Name, Address, Phone
HAVING Count(*) > 1
The records returned are those Name, Address, Phone combinations which have duplicates. The number of duplicates is the total of all Count(*)'s minus the number of records returned by the query (since technically one out of each of these groups is not a duplicate, just the rest).
Assuming table has column name Name as PART_NAME PHONE Number as PART_NAME select d.*, COUNT(PART_NAME) OVER(PARTITION BY PART_NAME,PHONE_NUMBER) FROM draw d
SELECT *
FROM table
WHERE Phone IN
( SELECT Phone
FROM table
GROUP BY Name, Address, Phone
WHERE COUNT(Name) > 1
) AS A
This should get you the entire row for every duplicate.
You can add an ORDER BY Name, Address, Phone
to the outer query to make sure the duplicates end up next to each other.
"Beware of bugs in the above code; I have only proved it correct, not tried it." -- Donald Knuth
精彩评论