如何在没有top(1)的情况下通过明细表记录的计数来获取主表记录

发布于 2024-12-04 17:10:21 字数 536 浏览 1 评论 0原文

我有一个主表（团队）和一个详细表（团队成员）。团队成员与团队有 FK。我需要获取拥有最多团队成员的团队的团队记录。起初我被

SELECT team.name
FROM   team
       INNER JOIN (SELECT TOP 1 COUNT(*) AS membercount,
                          teamID
                   FROM   teammember
                   GROUP BY teamID
                   ORDER BY Count(*) DESC) AS team_with_most_members
         ON team.id = team_with_most_members.teamID

告知我不能在查询中使用 TOP(1) 。有人知道如果没有的话我该怎么做吗？

谢谢！

Team
ID, Name

TeamMember
ID, TeamID, UserID

原文

I have a master table (Team) and a detail table (TeamMember). TeamMember has a FK to Team.
I need to get the Team record for the team that has the most team members. I at first had

SELECT team.name
FROM   team
       INNER JOIN (SELECT TOP 1 COUNT(*) AS membercount,
                          teamID
                   FROM   teammember
                   GROUP BY teamID
                   ORDER BY Count(*) DESC) AS team_with_most_members
         ON team.id = team_with_most_members.teamID

I was informed that I cannot use TOP(1) in my queries. Anyone have an idea how I can do it without?

Thanks!

Team
ID, Name

TeamMember
ID, TeamID, UserID

分享到QQ

分享到微博

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

发布评论

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

七分※倦醒 2024-12-11 17:10:21

这个很粗糙，但很有效：

SELECT t.name
FROM team AS t
JOIN teammember AS tm ON tm.teamID = t.ID
GROUP BY t.Name
HAVING COUNT(tm.id) = (SELECT MAX(members) FROM (SELECT COUNT(id) members FROM teammember GROUP BY teamid) AS sub)

This one is crude but it works:

SELECT t.name
FROM team AS t
JOIN teammember AS tm ON tm.teamID = t.ID
GROUP BY t.Name
HAVING COUNT(tm.id) = (SELECT MAX(members) FROM (SELECT COUNT(id) members FROM teammember GROUP BY teamid) AS sub)

回复收藏 0 原文

仙气飘飘 2024-12-11 17:10:21

这让我感觉很肮脏。即使出现平局，它也会返回一个团队名称 - 如果您希望在出现平局时获得所有行，请使用 DENSE_RANK() 而不是 ROW_NUMBER()。

SELECT t.ID, t.Name FROM
(
    SELECT 
        TeamID, rn = ROW_NUMBER() OVER (ORDER BY c DESC)
    FROM 
    (
        SELECT TeamID, c = COUNT(*) 
        FROM dbo.TeamMember GROUP BY TeamID
    ) AS x
) AS y
INNER JOIN dbo.Team AS t
ON y.TeamID = t.ID
WHERE y.rn = 1; -- **EDIT** forgot the most important part!

我真的会站起来挑战“没有第一名”的规则。询问那些告诉您出于性能原因将现有查询的性能与我们提出的任何查询进行比较的人。

This makes me feel dirty. It will return a single team name even if there is a tie - if you want all rows in the event of a tie, use DENSE_RANK() instead of ROW_NUMBER().

SELECT t.ID, t.Name FROM
(
    SELECT 
        TeamID, rn = ROW_NUMBER() OVER (ORDER BY c DESC)
    FROM 
    (
        SELECT TeamID, c = COUNT(*) 
        FROM dbo.TeamMember GROUP BY TeamID
    ) AS x
) AS y
INNER JOIN dbo.Team AS t
ON y.TeamID = t.ID
WHERE y.rn = 1; -- **EDIT** forgot the most important part!

I'd really stand up and challenge the "no TOP 1" rule. Ask the person who told you it was for performance reasons to compare the performance of your existing query with any of the kludges we've come up with.

回复收藏 0 原文

薄荷→糖丶微凉 2024-12-11 17:10:21

TOP 1 是最干净的方式。这是一个可能有效的非常复杂的方法：

SELECT ID FROM (

SELECT ID, Tally, MAX(Tally) over (partition by ID) AS MaxTally
  FROM (SELECT t1.ID,
               COUNT(t2.ID) AS Tally
          FROM @Team t1
          JOIN @TeamMember t2
            ON t2.TeamID = t1.ID
         GROUP BY t1.ID) x

) y WHERE Tally = MaxTally

TOP 1 is cleanest way. Here's a really convoluted way that might work:

SELECT ID FROM (

SELECT ID, Tally, MAX(Tally) over (partition by ID) AS MaxTally
  FROM (SELECT t1.ID,
               COUNT(t2.ID) AS Tally
          FROM @Team t1
          JOIN @TeamMember t2
            ON t2.TeamID = t1.ID
         GROUP BY t1.ID) x

) y WHERE Tally = MaxTally

回复收藏 0 原文

~没有更多了~