table:团队(tid、tname、tcolor、budget)玩家(pid、pname、年龄、国家)播放(pid、tid、季节、值)-(pid参考players表中的pid)-(tid引用teams表中的tid)胜利(wtid、ltid、季节、wscore、lscore)-(wtid(优胜者)和ltid(失败者)参考团队表中的tid)
我必须为此编写查询:(我尝试了“not exist”和“not in”,但做不到。)查找与所有球队一起比赛的球队的tid。
gdrx4gfi1#
这样做可以:
SELECT teamFROM ( SELECT tid team, WINS.ltid otherteam FROM TEAMS LEFT JOIN WINS ON WINS.wtid = tid UNION ALL SELECT tid team, LOSE.wtid otherteam FROM TEAMS LEFT JOIN WINS LOSE ON LOSE.ltid = tid)GROUP BY teamHAVING COUNT(DISTINCT otherteam) >= (SELECT COUNT(tid)-1 AS teamcount FROM TEAMS)otherteams
SELECT team
FROM (
SELECT tid team, WINS.ltid otherteam
FROM TEAMS
LEFT JOIN WINS ON WINS.wtid = tid
UNION ALL
SELECT tid team, LOSE.wtid otherteam
LEFT JOIN WINS LOSE ON LOSE.ltid = tid
)
GROUP BY team
HAVING COUNT(DISTINCT otherteam) >= (SELECT COUNT(tid)-1 AS teamcount FROM TEAMS)otherteams
7gs2gvoe2#
这对我有用。
SELECT t1.tidFROM teams t1WHERE NOT EXISTS( SELECT t2.tid FROM teams t2 WHERE t2.tid NOT IN (SELECT wtid FROM wins WHERE ltid = t1.tid UNION (SELECT ltid FROM wins WHERE wtid = t1.tid)) AND t1.tid != t2.tid)
SELECT
t1.tid
FROM
teams t1
WHERE
NOT EXISTS( SELECT
t2.tid
teams t2
t2.tid NOT IN (SELECT
wtid
wins
ltid = t1.tid UNION (SELECT
ltid
wtid = t1.tid)) AND t1.tid != t2.tid)
2条答案
按热度按时间gdrx4gfi1#
这样做可以:
7gs2gvoe2#
这对我有用。