sql - 涉及到同一个表的数十个连接的查询不返回所有结果
问题描述
我有一个多次加入同一个表的查询,它需要超过 2 打表别名。但是,它不会返回所有结果。这是查询。
Select
bracketID_1, bracketWins_1, a.logo as bracketID1_logo, bracketID_2, bracketWins_2, b.logo as bracketID2_logo, bracketID_3, bracketWins_3, c.logo as bracketID3_logo,
bracketID_4, bracketWins_4, d.logo as bracketID4_logo, bracketID_5, bracketWins_5, e.logo as bracketID5_logo, bracketID_6, bracketWins_6, f.logo as bracketID6_logo,
bracketID_7, bracketWins_7, g.logo as bracketID7_logo, bracketID_8, bracketWins_8, h.logo as bracketID8_logo, bracketID_9, bracketWins_9, i.logo as bracketID9_logo,
bracketID_10, bracketWins_10, j.logo as bracketID10_logo, bracketID_11, bracketWins_11, k.logo as bracketID11_logo, bracketID_12, bracketWins_12, l.logo as bracketID12_logo,
bracketID_13, bracketWins_13, m.logo as bracketID13_logo, bracketID_14, bracketWins_14, n.logo as bracketID14_logo, bracketID_15, bracketWins_15, o.logo as bracketID15_logo,
bracketID_16, bracketWins_16, p.logo as bracketID16_logo, bracketID_17, bracketWins_17, q.logo as bracketID17_logo, bracketID_18, bracketWins_18, r.logo as bracketID18_logo,
bracketID_19, bracketWins_19, s.logo as bracketID19_logo, bracketID_20, bracketWins_20, t.logo as bracketID20_logo, bracketID_21, bracketWins_21, u.logo as bracketID21_logo,
bracketID_22, bracketWins_22, v.logo as bracketID22_logo, bracketID_23, bracketWins_23, w.logo as bracketID23_logo, bracketID_24, bracketWins_24, x.logo as bracketID24_logo,
bracketID_25, bracketWins_25, y.logo as bracketID25_logo, bracketID_26, bracketWins_26, z.logo as bracketID26_logo, bracketID_27, bracketWins_27, aa.logo as bracketID27_logo,
bracketID_28, bracketWins_28, ab.logo as bracketID28_logo, bracketID_29, bracketWins_29, ac.logo as bracketID29_logo, bracketID_30, bracketWins_30, ad.logo as bracketID30_logo
from YearlyPlayoffResults
join teams a on YearlyPlayoffResults.bracketID_1 = a.Abbreviation
join teams b on YearlyPlayoffResults.bracketID_2 = b.Abbreviation
join teams c on YearlyPlayoffResults.bracketID_3 = c.Abbreviation
join teams d on YearlyPlayoffResults.bracketID_4 = d.Abbreviation
join teams e on YearlyPlayoffResults.bracketID_5 = e.Abbreviation
join teams f on YearlyPlayoffResults.bracketID_6 = f.Abbreviation
join teams g on YearlyPlayoffResults.bracketID_7 = g.Abbreviation
join teams h on YearlyPlayoffResults.bracketID_8 = h.Abbreviation
join teams i on YearlyPlayoffResults.bracketID_9 = i.Abbreviation
join teams j on YearlyPlayoffResults.bracketID_10 = j.Abbreviation
join teams k on YearlyPlayoffResults.bracketID_11 = k.Abbreviation
join teams l on YearlyPlayoffResults.bracketID_12 = l.Abbreviation
join teams m on YearlyPlayoffResults.bracketID_13 = m.Abbreviation
join teams n on YearlyPlayoffResults.bracketID_14 = n.Abbreviation
join teams o on YearlyPlayoffResults.bracketID_15 = o.Abbreviation
join teams p on YearlyPlayoffResults.bracketID_16 = p.Abbreviation
join teams q on YearlyPlayoffResults.bracketID_17 = q.Abbreviation
join teams r on YearlyPlayoffResults.bracketID_18 = r.Abbreviation
join teams s on YearlyPlayoffResults.bracketID_19 = s.Abbreviation
join teams t on YearlyPlayoffResults.bracketID_20 = t.Abbreviation
join teams u on YearlyPlayoffResults.bracketID_21 = u.Abbreviation
join teams v on YearlyPlayoffResults.bracketID_22 = v.Abbreviation
join teams w on YearlyPlayoffResults.bracketID_23 = w.Abbreviation
join teams x on YearlyPlayoffResults.bracketID_24 = x.Abbreviation
join teams y on YearlyPlayoffResults.bracketID_25 = y.Abbreviation
join teams z on YearlyPlayoffResults.bracketID_26 = z.Abbreviation
join teams aa on YearlyPlayoffResults.bracketID_27 = aa.Abbreviation
join teams ab on YearlyPlayoffResults.bracketID_28 = ab.Abbreviation
join teams ac on YearlyPlayoffResults.bracketID_29 = ac.Abbreviation
join teams ad on YearlyPlayoffResults.bracketID_30 = ad.Abbreviation
但是,它只返回随机年份,也不按特定顺序返回。此外,当我添加 where 子句以返回特定年份时,有些年份有效,有些则无效,而丢失的年份确实存在于表中。
解决方案
虽然@Larnu 是正确的,您需要对数据进行规范化,但与此同时,您可以通过将您的替换JOINs
为LEFT JOIN
. 它将显示哪些列有NULL
记录,这会导致您的数据“丢失”。
这可能是“预期的”结果,如果您有奇数个团队,则可能会跳过某些括号。
推荐阅读
- kubernetes - Kubernetes 中的 NetworkPolicy 在 podSelector 上不匹配
- ruby-on-rails - 如何在 order rails 查询中修复 sql 注入
- c# - 合并包含在分离实体中的 ICollection
- java - java - 如何使用正则表达式防止Java电子邮件字段中的重音字符?
- pip - 如何下载pyaudio
- node.js - npm 安装:500 错误
- python - 将数据从 Python 返回到 Unity 的问题
- c# - 荧光笔墨水笔触未渲染到画布控件上
- search - 在 Flutter 应用程序中搜索不同的 Firestore 集合
- php - 错误:Flutter web 中的 XMLHttpRequest 错误