我的连接表查询代码是否有错误？

我真的对这些代码感到困惑。我有这样的查询

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `aIPK` AS select 
`ipbmst_fakultas`.`Kode` AS `Fakultas`,
`ipbmst_departemen`.`Kode` AS `Departemen`,
`akdmst_mahasiswamagister`.`NIM` AS `NIM`,
`akdmst_mahasiswamagister`.`TahunMasuk` AS `TahunMasuk`,
 `akdhis_kelanjutanstudi`.`IPK` AS `IPK`
from (((((`akdmst_mahasiswamagister` left join `akdmst_mayor` on((`akdmst_mahasiswamagister`.`MayorID` = `akdmst_mayor`.`ID`))) 
left join `ipbmst_departemen` on((`akdmst_mayor`.`DepartemenID` = `ipbmst_departemen`.`ID`))) 
left join `ipbmst_fakultas` on((`ipbmst_departemen`.`FakultasID` = `ipbmst_fakultas`.`ID`))) 
left join `ipbmst_orang` on((`akdmst_mahasiswamagister`.`NIM` = `ipbmst_orang`.`NIMS2Key`))) 
left join `akdhis_kelanjutanstudi` on((`akdhis_kelanjutanstudi`.`NIM` = `ipbmst_orang`.`NIMS2Key`)))
WHERE `akdhis_kelanjutanstudi`.`IPK` IS NOT NULL

order by NIM
LIMIT 100;

但“IPK”结果为NULL，实际上IPK有它的值。这些代码有什么问题吗？

这是我的表结构：

table 1: akdmst_mahasiswamagister (ID, MahasiswaID, NIM, MayorID, TahunMasuk)
table 2: akdmst_mayor(ID, DepartemenID)
table 3: ipbmst_departemen(ID, FakultasID, DepartmenName)
table 4: ipbmst_fakultas(ID, FacultyName)
table 5: ipbmst_orang(ID, Name, NIMS2Key)
table 6: akdhis_kelanjutanstudi(ID, NIM, IPK)

我还有关于此查询的其他问题。查询视图花费了太多时间。我想可能是因为表 akdhis_kelanjutanstudi 由超过 300K 的数据记录组成。我已经使用了“LIMIT 100”但仍然相同。你能帮我解决这个问题吗？

我不知道确切的问题是什么，但你的WHERE条款有问题：

WHERE IPK IS NOT NULL

不允许在列中引用列别名WHERE条款，因为其价值可能尚未确定。相反，你应该使用这个：

WHERE akdhis_kelanjutanstudi.IPK IS NOT NULL

Update:

您在原始视图中使用的括号看起来很奇怪、不必要，而且可能是错误的。尝试使用以下方法：

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
    SQL SECURITY DEFINER VIEW aIPK AS
SELECT t4.Kode AS Fakultas,
       t3.Kode AS Departemen,
       t1.NIM AS NIM,
       t1.TahunMasuk AS TahunMasuk,
       t6.IPK AS IPK
FROM akdmst_mahasiswamagister t1
LEFT JOIN akdmst_mayor t2
    ON t1.MayorID = t2.ID
LEFT JOIN ipbmst_departemen t3
    ON t2.DepartemenID = t3.ID
LEFT JOIN ipbmst_fakultason t4
    ON t3.FakultasID = t4.ID
LEFT JOIN ipbmst_orang t5
    ON t1.NIM = t5.NIMS2Key
LEFT JOIN akdhis_kelanjutanstudi t6
    ON t6.NIM = t5.NIMS2Key
WHERE t6.IPK IS NOT NULL
ORDER BY NIM
LIMIT 100;