TSQL 无效 HAVING 计数

发布于 2024-12-08 08:07:55 字数 890 浏览 2 评论 0原文

我正在使用 SSMS 2008 并尝试使用 HAVING 语句。这应该是一个真正简单的查询。然而，尽管有很多重复项，但我只收到一条记录返回的事件。

我在这里的 HAVING 语句做错了什么吗？或者我可以使用其他一些功能来代替吗？

select
      address_desc,
      people_id
from 
      dbo.address_view
where people_id is not NULL
group by people_id , address_desc
having count(*) > 1

地址视图中的示例数据：

people_id                            address_desc
----------                           ------------
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

原文

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
----------                           ------------
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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

双手揣兜 2024-12-15 08:07:55

GROUP BY 会将重复项集中到一行中。

我认为，您想要查找具有重复 address_desc 的所有 people_id 值：

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

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

回复收藏 0 原文

能怎样 2024-12-15 08:07:55

使用 row_number 和分区，您可以找到重复出现的情况，其中 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
其中 row_num>1

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

回复收藏 0 原文

~没有更多了~