使用含HAVING子句的MAX函数编写嵌套SQL查询，找出赞助游戏数最值的机构

Got it, let's work through these two nested SQL queries for your database setup. First, let's recap your table structures (uppercase fields denote foreign keys as you specified):

Table Structures #

• Game: (ID, Version, Name, Price, Color, IDDISTRIBUTION [FK to Distribution.ID], #worker [FK])
• Distribution: (ID, Name, #worker [FK], quality)
• Istitute: (ID, Name, NINCeo [FK to Designer.NIN], City)
• Sponsor: (IDGAME [FK to Game.ID], IDISTITUTE [FK to Istitute.ID], VERSIONGAME [FK to Game.Version])
• Designer: (NIN, name, surname, role, budget)
• Project: (NINDESIGNER [FK to Designer.NIN], IDGAME [FK to Game.ID], VERSIONGAME [FK to Game.Version], #hours)

1. Query for Institute(s) with the Most Sponsored Games #

This nested query handles ties (if multiple institutes have the same highest number of sponsored games) by first calculating sponsorship counts per institute, finding the maximum count, then filtering institutes that match that max value:

SELECT i.Name, COUNT(s.IDGAME) AS #max_games
FROM Istitute i
INNER JOIN Sponsor s ON i.ID = s.IDISTITUTE
GROUP BY i.ID, i.Name
HAVING COUNT(s.IDGAME) = (
    -- Grab the highest number of sponsored games across all institutes
    SELECT MAX(sponsor_count)
    FROM (
        -- Calculate how many games each institute has sponsored
        SELECT COUNT(IDGAME) AS sponsor_count
        FROM Sponsor
        GROUP BY IDISTITUTE
    ) AS institute_sponsor_counts
);

Breakdown:

• The innermost subquery groups Sponsor entries by institute and counts how many games each sponsors.
• The middle subquery finds the largest value from those counts.
• The main query joins institutes with their sponsorships, groups them, and returns only those where their count matches the maximum.

2. Query for Institute(s) with the Fewest Sponsored Games #

We use similar nested logic here, but target the minimum count instead. There are two variations depending on whether you want to include institutes that haven't sponsored any games (count = 0):

Version 1: Only Institutes with At Least One Sponsorship #

SELECT i.Name, COUNT(s.IDGAME) AS #min_games
FROM Istitute i
INNER JOIN Sponsor s ON i.ID = s.IDISTITUTE
GROUP BY i.ID, i.Name
HAVING COUNT(s.IDGAME) = (
    -- Get the lowest number of sponsored games among active sponsors
    SELECT MIN(sponsor_count)
    FROM (
        SELECT COUNT(IDGAME) AS sponsor_count
        FROM Sponsor
        GROUP BY IDISTITUTE
    ) AS institute_sponsor_counts
);

Version 2: Include Institutes with Zero Sponsorships #

If you need to account for institutes that haven't sponsored any games (their count will be 0), use a LEFT JOIN to retain all institute records:

SELECT i.Name, COUNT(s.IDGAME) AS #min_games
FROM Istitute i
LEFT JOIN Sponsor s ON i.ID = s.IDISTITUTE
GROUP BY i.ID, i.Name
HAVING COUNT(s.IDGAME) = (
    -- Get the lowest count, including 0 for non-sponsoring institutes
    SELECT MIN(sponsor_count)
    FROM (
        SELECT COUNT(s_inner.IDGAME) AS sponsor_count
        FROM Istitute i_inner
        LEFT JOIN Sponsor s_inner ON i_inner.ID = s_inner.IDISTITUTE
        GROUP BY i_inner.ID
    ) AS institute_sponsor_counts
);

内容的提问来源于stack exchange，提问作者user1970743