如何用sql编写这个查询?

qij5mzcb  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(312)

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。

gdrx4gfi

gdrx4gfi1#

这样做可以:

  1. SELECT team
  2. FROM (
  3. SELECT tid team, WINS.ltid otherteam
  4. FROM TEAMS
  5. LEFT JOIN WINS ON WINS.wtid = tid
  6. UNION ALL
  7. SELECT tid team, LOSE.wtid otherteam
  8. FROM TEAMS
  9. LEFT JOIN WINS LOSE ON LOSE.ltid = tid
  10. )
  11. GROUP BY team
  12. HAVING COUNT(DISTINCT otherteam) >= (SELECT COUNT(tid)-1 AS teamcount FROM TEAMS)otherteams
7gs2gvoe

7gs2gvoe2#

这对我有用。

  1. SELECT
  2. t1.tid
  3. FROM
  4. teams t1
  5. WHERE
  6. NOT EXISTS( SELECT
  7. t2.tid
  8. FROM
  9. teams t2
  10. WHERE
  11. t2.tid NOT IN (SELECT
  12. wtid
  13. FROM
  14. wins
  15. WHERE
  16. ltid = t1.tid UNION (SELECT
  17. ltid
  18. FROM
  19. wins
  20. WHERE
  21. wtid = t1.tid)) AND t1.tid != t2.tid)
展开查看全部

相关问题