Question

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

我得知,我无法在我的问询中使用“TOP(1)”。任何人都知道我如何能够不这样做?

感谢!

Team
ID, Name

TeamMember
ID, TeamID, UserID

Answer 1

这是粗略的,但行之有效:

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)

Answer 2

这使我感到 d。即便存在联系,如果在出现关系时需要所有浏览量,则将退回一个小组的名称,而不是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!

我确实站起来并质疑“零敲碎打1”规则。 Ask 告诉你的人,是出于业绩原因,要把你现有询问的表现与我们即将到来的任何判决相比较。

Answer 3

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

友情链接