mysql - 在MySQL中从正确的表联接四个没有重复的表？

我必须从数据库中获取所有用户的数据，数据库结构如下，每个表中只有几列，但足以证明概念 CREATE TABLE `tblpersonal` ( `intCompNo` int(11) NOT NULL, `strName` varchar(500) COLLATE utf8_bin NOT NULL)CREATE TABLE `tbledu` ( `intEduId` int(11) NOT NULL AUTO_INCREMENT, `intCompNo` int(11) NOT NULL, `intEduType` varchar(64) COLLATE utf8_bin DEFAULT NULL)CREATE TABLE `tbltrain` ( `intTrainId` int(5) NOT NULL AUTO_INCREMENT, `intCompNo` int(5) NOT NULL, `strCourseName` varchar(300) COLLATE utf8_bin NOT NULL)CREATE TABLE `tblrequests` ( `intReqId` int(11) NOT NULL AUTO_INCREMENT, `intCompNo` int(11) NOT NULL, `strNotes` varchar(512) COLLATE utf8_bin NOT NULL, `dateOfSubmit` datetime NOT NULL, PRIMARY KEY (`intReqId`))现在，我在下面运行查询，以获取人员姓名，他的学历ID和他的培训ID，以及他发送的最后一个请求号SELECT tblpersonal.intCompNo, tblpersonal.strName, tbltrain.intTrainId, tbledu.intEduId, tblrequests.intReqIdFROM tblpersonalLEFT JOIN tblrequests ON tblpersonal.intCompNo = tblrequests.intCompNoAND tblrequests.intReqId = (SELECT MAX(req.intReqId) FROM tblrequests AS req WHERE req.intCompNo = tblpersonal.intCompNo)LEFT JOIN tbltrain ON tblpersonal.intCompNo = tbltrain.intCompNoLEFT JOIN tbledu ON tblpersonal.intCompNo = tbledu.intCompNoWHERE tblrequests.intReqId IS NOT NULLAND tblpersonal.intCompNo = 12368GROUP BY tblpersonal.intCompNo, tbltrain.intTrainId, tbledu.intEduIdORDER BY tblpersonal.intCompNo, tbledu.intEduId, tbltrain.intTrainid;问题是我的结果是笛卡尔积，如下所示仅显示一名员工的结果+-----------+--------------------------+------------+----------+----------+| intCompNo | strName | intTrainId | intEduId | intReqId |+-----------+--------------------------+------------+----------+----------+| 12368 | ????? ???? ???? ???????? | 5194 | 107 | 388 || 12368 | ????? ???? ???? ???????? | 5203 | 107 | 388 || 12368 | ????? ???? ???? ???????? | 5575 | 107 | 388 || 12368 | ????? ???? ???? ???????? | 5580 | 107 | 388 || 12368 | ????? ???? ???? ???????? | 5585 | 107 | 388 || 12368 | ????? ???? ???? ???????? | 5591 | 107 | 388 || 12368 | ????? ???? ???? ???????? | 5636 | 107 | 388 || 12368 | ????? ???? ???? ???????? | 5666 | 107 | 388 || 12368 | ????? ???? ???? ???????? | 5676 | 107 | 388 |如何获得员工的所有培训和教育中的数据而不重复Running Example of Database can be found below 最佳答案 如果您不介意字符串聚合SELECT tblpersonal.intCompNo,tblpersonal.strName,GROUP_CONCAT(DISTINCT tblrequests.intReqId ORDER BY tblrequests.intReqId SEPARATOR ', ') AS intReqIdList,GROUP_CONCAT(DISTINCT tbledu.intEduId ORDER BY tbledu.intEduId SEPARATOR ', ') AS intEduIdList,GROUP_CONCAT(DISTINCT tbltrain.intTrainId ORDER BY tbltrain.intTrainId SEPARATOR ', ') AS intTrainIdList,COUNT(DISTINCT tbltrain.intTrainId) AS intTrainIdsFROM tblpersonalINNER JOIN tblrequests ON tblrequests.intCompNo = tblpersonal.intCompNo AND tblrequests.intReqId = (SELECT MAX(req.intReqId) FROM tblrequests AS req WHERE req.intCompNo = tblpersonal.intCompNo)LEFT JOIN tbltrain ON tbltrain.intCompNo = tblpersonal.intCompNoLEFT JOIN tbledu ON tbledu.intCompNo = tblpersonal.intCompNoWHERE tblpersonal.intCompNo = 12368GROUP BY tblpersonal.intCompNo,tblpersonal.strNameORDER BY tblpersonal.intCompNo;结果：intCompNo | strName | intReqIdList | intEduIdList | intTrainIdList | intTrainIds--------: | :-------- | :----------- | :----------- | :--------------------------------------------------------------------------------------------------------------------------------------------- | ----------: 12368 | TEST_USER | 213 | 107, 109 | 5194, 5203, 5575, 5580, 5585, 5591, 5636, 5666, 5676, 5680, 5682, 5685, 5688, 5694, 5700, 5704, 5709, 5713, 5718, 5720, 5722, 5725, 7008, 7014 | 24A test on db<>fiddle hereOr how do you feel about uniting them?SET @CompNo = 12368;SELECT person.intCompNo,person.strName AS PersonName,Src.Source,Src.SrcIdFROM( SELECT req.intCompNo, 'req' AS Source, MAX(req.intReqId) AS SrcId FROM tblrequests req WHERE req.intCompNo = @CompNo GROUP BY req.intCompNo UNION ALL SELECT train.intCompNo, 'trn', train.intTrainId FROM tbltrain AS train WHERE train.intCompNo = @CompNo UNION ALL SELECT edu.intCompNo, 'edu', edu.intEduId FROM tbledu AS edu WHERE edu.intCompNo = @CompNo) AS SrcINNER JOIN tblpersonal AS person ON Src.intCompNo = person.intCompNoORDER BY person.intCompNo, Src.Source, Src.SrcId;db 小提琴here还是使用工会的这种变化？SET @CompNo = 12368;SELECT person.intCompNo,person.strName AS PersonName,Src.intReqId,Src.intTrainId,Src.intEduIdFROM( SELECT req.intCompNo , MAX(req.intReqId) AS intReqId , 0 AS intTrainId , 0 AS intEduId FROM tblrequests req WHERE req.intCompNo = @CompNo GROUP BY req.intCompNo UNION ALL SELECT train.intCompNo , 0 AS intReqId , train.intTrainId , 0 AS intEduId FROM tbltrain AS train WHERE train.intCompNo = @CompNo UNION ALL SELECT edu.intCompNo , 0 AS intReqId , 0 AS intTrainId , edu.intEduId FROM tbledu AS edu WHERE edu.intCompNo = @CompNo) AS SrcINNER JOIN tblpersonal AS person ON Src.intCompNo = person.intCompNoORDER BY person.intCompNo, Src.intTrainId, Src.intEduId, Src.intReqId;但是也许您正在寻找一种可以在MySql 8.0中工作的解决方案。它重新使用CTE（公用表表达式）。并使用窗口函数ROW_NUMBER。基本上，它在更大的训练表的相同计算行号上链接edu和request。WITH PERSONAL AS( SELECT intCompNo, strName FROM tblpersonal WHERE intCompNo IN (12368))SELECT person.intCompNo,person.strName AS PersonName,req.intReqId,edu.intEduId,trn.intTrainIdFROM PERSONAL AS personLEFT JOIN( SELECT t.intCompNo, t.intTrainId , ROW_NUMBER() OVER (PARTITION BY t.intCompNo ORDER BY t.intTrainId) AS rn FROM tbltrain AS t JOIN PERSONAL p ON p.intCompNo = t.intCompNo) AS trnON trn.intCompNo = person.intCompNoLEFT JOIN( SELECT t.intCompNo , MAX(t.intReqId) AS intReqId , 1 AS rn FROM tblrequests t JOIN PERSONAL p ON p.intCompNo = t.intCompNo GROUP BY t.intCompNo) AS req ON req.intCompNo = trn.intCompNo AND req.rn = trn.rnLEFT JOIN( SELECT t.intCompNo, t.intEduId , ROW_NUMBER() OVER (PARTITION BY t.intCompNo ORDER BY t.intEduId) AS rn FROM tbledu AS t JOIN PERSONAL p ON p.intCompNo = p.intCompNo) AS edu ON edu.intCompNo = trn.intCompNo AND edu.rn = trn.rnORDER BY person.intCompNo;intCompNo |人名| intReqId | intEduId | intTrainId--------：| ：--------- | -------：| -------：| ---------：    12368 | TEST_USER | 213 | 107 | 5194    12368 | TEST_USER |空| 109 | 5203    12368 | TEST_USER |空|空| 5575    12368 | TEST_USER |空|空| 5580    12368 | TEST_USER |空|空| 5585    ...在db fiddle here上测试关于mysql - 在MySQL中从正确的表联接四个没有重复的表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59128541/