一个中需要多个select查询的SQL查询

Question

我现在在大学学习数据库，在我的项目中，我有3个表:联赛、团队和有

• 联赛(leagueId，leagueName)
• 团队(teamId，teamName)
• 具有(leagueId，teamId，year)

  League                    Team                          Has
  leagueid | leaguename     teamid | teamname       leagueid | teamid | year
  1        |  SerieA           1   | Juventus          1     |   1    | 2016
  2        | ChampionsL.       2   | Man.Utd           2     |   1    | 2016
  3        |  PremierL.        3   | Arsenal           3     |   2    | 2016
                                                       2     |   3    | 2013
                                                       2     |   3    | 2014

Team_Name  |  League_Name
 Juventus  |  SerieA
 Juventus  |  ChampionsL

select
    t.teamname,
    l.leaguename
from
    team t,
    league l,
    has h
where
    t.teamid   = h.teamid and
    h.leagueid = l.leagueid 
group by
    t.teamname,
    l.leaguename
having
    count( l.leagueid ) >= 2

Answer 1

试试这个。对我有效。

CREATE TABLE League ( leagueId INT PRIMARY KEY NOT NULL, leagueName VARCHAR(20)); 
GO

CREATE TABLE Team ( TeamId INT PRIMARY KEY NOT NULL, TeamName VARCHAR(20))
 GO
CREATE TABLE Has ( TeamId INT FOREIGN KEY (TeamId) REFERENCES Team(TeamId), 
                    leagueId INT FOREIGN KEY(leagueId) REFERENCES League(leagueId)) 


SELECT tn.TeamName, lg.leagueName
FROM Team tn
JOIN Has ha ON tn.TeamId = ha.TeamId
JOIN League lg ON ha.leagueId = lg.leagueId 
WHERE tn.TeamId IN (
                    SELECT  h.TeamId
                    FROM Team t
                    JOIN Has h ON t.TeamId = h.TeamId
                    JOIN League l ON h.leagueId = l.leagueId 
                    GROUP BY h.TeamId
                    HAVING COUNT(h.leagueId) > 2)