为什么联接表项目的计数如此之高？

发布于 2025-02-12 07:13:49 字数 1429 浏览 0 评论 0原文

我有一个Transact SQL查询，该查询在Count字段中返回巨大的值。

SELECT max(l.Date)
      ,count(stp.Key) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY count(stp.Key) DESC

如果我替换count（stp.key）元素（从profile where where wm.id = id中选择计数（key）），那么我得到真实计数：

SELECT max(l.Date)
      ,(select count(Key) from Profile where wm.Id = Id) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY (select count(Key) from Profile where wm.Id = Id) DESC

例如，使用简单的计数到位，顶部结果显示147,000，但选择...到位，它仅显示65，等等。

我相信这是因为计数是计算最终结果集中的每个记录。但是，如何仅计算stp.key的记录匹配ID字段的记录？

[edit] 如评论中指出的那样，以解释问题...

表：Web

Id
1
2
3

表：配置文件

表：log

Id Date
1  2022-06-12
1  2022-06-02
2  2022-06-23
2  2022-06-01
3  2022-06-14
3  2022-06-03

o，最终我想要的是结果集中每个Web记录的配置文件表中的行计数。我了解我的原始查询只是在最终结果集中计算记录，但我不明白如何使查询计算每个wm.id-也就是说，我想在加入中计算相关记录桌子。

原文

I have a Transact SQL query which returns huge values in the Count field.

SELECT max(l.Date)
      ,count(stp.Key) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY count(stp.Key) DESC

If I replace the count(stp.Key) element with (select count(Key) from Profile where wm.Id = Id) then I get the real count:

SELECT max(l.Date)
      ,(select count(Key) from Profile where wm.Id = Id) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY (select count(Key) from Profile where wm.Id = Id) DESC

For example, with the simple count in place the top result shows 147,000 but with the select ... in place it shows just 65, etc.

I believe this is because the count is counting every record in the final result set. But how do I get it to only count the stp.Key for the records which match the Id field?

[EDIT] As pointed out in the comments, to explain the problem...

Table: Web

Id
1
2
3

Table: Profile

Table: Log

Id Date
1  2022-06-12
1  2022-06-02
2  2022-06-23
2  2022-06-01
3  2022-06-14
3  2022-06-03

So, ultimately what I would like is a the count of rows from the Profile table for each Web record in the result set. I understand that what my original query had was simply counting the records in the final result set, but I don't understand how to get the query to count per wm.Id - that is, I want to count the related records in the joined table.

分享到QQ

分享到微博

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

发布评论

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

各自安好 2025-02-19 07:13:49

尝试独特，如果stp.key是唯一的

count(distinct stp.Key)

try with distinct, if stp.Key is unique

count(distinct stp.Key)

回复收藏 0 原文

仙气飘飘 2025-02-19 07:13:49

尽量避免 log 联接中的表，这可能会给您正确的结果。
通过以下方式将其用作子查询

    SELECT 
(select top 1 Date from Log where WebId=wm.WebId order by Date desc)
  ,count(stp.Key) as 'Count'
  ,wm.Id
FROM Web wm LEFT JOIN Profile stp ON wm.WebId = stp.WebId
WHERE Code = 'RR'

Try to avoid Log table in join that may give you the correct Result.
Use it as subquery By following way

    SELECT 
(select top 1 Date from Log where WebId=wm.WebId order by Date desc)
  ,count(stp.Key) as 'Count'
  ,wm.Id
FROM Web wm LEFT JOIN Profile stp ON wm.WebId = stp.WebId
WHERE Code = 'RR'

回复收藏 0 原文

~没有更多了~