我的查询是否有错误的代码加入表格? [英] Is there any wrong code with my query to join table?

问题描述

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

I am really confused with this codes. I have query like this

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具有其值.这些代码有什么问题?

but "IPK" result is NULL, actually IPK has its value. What's wrong with those codes?

这是我的表结构:

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)

关于此查询，我还有其他问题.查询视图花费了太多时间.我认为可能是因为表dhdhis_kelanjutanstudi由300,000多条数据记录组成.我使用了"LIMIT 100"，但仍然相同.你能帮我解决这个问题吗?

I also have the other problem about this query. It took too much time to query the view. I thought maybe it's because table akdhis_kelanjutanstudi that consists of more than 300K data records. I have used "LIMIT 100" but still the same. could you please help me to solve that problem?

推荐答案

我不知道确切的问题是什么，但是您的WHERE子句有问题:

I don't know what the exact problem(s) is, but your WHERE clause has a problem:

WHERE IPK IS NOT NULL

不允许在WHERE子句中引用列别名，因为可能尚未确定其值.相反，您应该使用以下代码:

It is not allowed to refer to a column alias in the WHERE clause, because its value may not be determined yet. Instead, you should use this:

WHERE akdhis_kelanjutanstudi.IPK IS NOT NULL

更新:

您在原始视图中使用的括号看起来很奇怪，不必要，甚至可能是错误的.尝试使用以下内容:

The parentheses you used in your original view look strange, unnecessary, and possibly wrong. Try using the following:

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;

这篇关于我的查询是否有错误的代码加入表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！