TSQL invalid HAVING count
I am using SSMS 2008 and trying to use a HAVING statement. This should be a real simple query. However, I am only getting one record returned event though there are numerous duplicates.
Am I doing something wrong with the HAVING statement here? Or is there some other function that I could use instead?
select
address_desc,
people_id
from
dbo.address_view
where people_id is not NULL
group by people_id , address_desc
having count(*) > 1
sample data from address_view:
people_id address_desc
---------- --------开发者_JS百科----
Murfreesboro, TN 37130 F15D1135-9947-4F66-B778-00E43EC44B9E
11 Mohawk Rd., Burlington, MA 01803 C561918F-C2E9-4507-BD7C-00FB688D2D6E
Unknown, UN 00000 C561918F-C2E9-4507-BD7C-00FB688D2D6E
Jacksonville, NC 28546 FC7C78CD-8AEA-4C8E-B93D-010BF8E4176D
Memphis, TN 38133 8ED8C601-5D35-4EB7-9217-012905D6E9F1
44 Maverick St., Fitchburg, MA 8ED8C601-5D35-4EB7-9217-012905D6E9F1
The GROUP BY is going to lump your duplicates together into a single row.
I think instead, you want to find all people_id
values with duplicate address_desc
:
SELECT a.address_desc, a.people_id
FROM dbo.address_view a
INNER JOIN (SELECT address_desc
FROM dbo.address_view
GROUP BY address_desc
HAVING COUNT(*) > 1) t
ON a.address_desc = t.address_desc
using row_number and partition you can find the duplicate occurrences where row_num>1
select address_desc,
people_id,
row_num
from
(
select
address_desc,
people_id,
row_number() over (partition by address_desc order by address_desc) row_num
from
dbo.address_view
where people_id is not NULL
) x where row_num>1
精彩评论