mysql - 在Mysql中从右表加入四个没有重复的表？

Question

我必须从数据库中获取所有用户的数据，数据库结构如下，每个表中只有几列，但足以证明概念

 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.intReqId

FROM tblpersonal

LEFT JOIN tblrequests ON tblpersonal.intCompNo = tblrequests.intCompNo

AND tblrequests.intReqId = (SELECT MAX(req.intReqId) FROM tblrequests AS req WHERE req.intCompNo = tblpersonal.intCompNo)

LEFT JOIN tbltrain ON tblpersonal.intCompNo = tbltrain.intCompNo

LEFT JOIN tbledu ON tblpersonal.intCompNo = tbledu.intCompNo

WHERE tblrequests.intReqId IS NOT NULL

AND tblpersonal.intCompNo = 12368

GROUP BY tblpersonal.intCompNo, tbltrain.intTrainId, tbledu.intEduId

ORDER 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 |

如何在没有重复的情况下获取所有培训和教育的员工数据

数据库的运行示例可以在下面找到

标签： mysqlsqlleft-joinwith-statement

Answer 1

如果你不介意一点字符串聚合

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 intTrainIds
FROM tblpersonal
INNER 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.intCompNo
LEFT JOIN tbledu
  ON tbledu.intCompNo = tblpersonal.intCompNo
WHERE tblpersonal.intCompNo = 12368
GROUP BY
 tblpersonal.intCompNo
,tblpersonal.strName
ORDER BY tblpersonal.intCompNo;

结果：

intCompNo | 字符串名称 | intReqIdList | intEduIdList | inttrainIdList | inttrainIds
--------: | :-------- | :----------- | :----------- | :------------------------------------------------ -------------------------------------------------- ------------------------------------------------------ | ----------:
    12368 | 测试用户 | 213 | 107, 109 | 5194, 5203, 5575, 5580, 5585, 5591, 5636, 5666, 5676, 5680, 5682, 5685, 5688, 5694, 5700, 5704, 5709, 5713, 5718, 51720, 500,7, 500,7, 24

对db<>fiddle的测试在这里

或者你觉得他们联合起来怎么样？

SET @CompNo = 12368;
SELECT
 person.intCompNo
,person.strName AS PersonName
,Src.Source
,Src.SrcId
FROM
(
  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 Src
INNER JOIN tblpersonal AS person
        ON Src.intCompNo = person.intCompNo
ORDER BY 
 person.intCompNo, 
 Src.Source, 
 Src.SrcId;

db<>在这里摆弄

或者也许这种使用联合的变体？

SET @CompNo = 12368;
SELECT
 person.intCompNo
,person.strName AS PersonName
,Src.intReqId
,Src.intTrainId
,Src.intEduId
FROM
(
  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 Src
INNER JOIN tblpersonal AS person
        ON Src.intCompNo = person.intCompNo
ORDER BY 
 person.intCompNo,
 Src.intTrainId, Src.intEduId, Src.intReqId;

但也许您更希望在 MySql 8.0 中寻找像这样的解决方案。它重用了 CTE（公用表表达式）。
并使用窗口函数 ROW_NUMBER。
基本上，它将教育和请求链接到更大的训练表的相同计算行号上。

WITH PERSONAL AS
(
    SELECT intCompNo, strName
    FROM tblpersonal
    WHERE intCompNo IN (12368)
)
SELECT
 person.intCompNo
,person.strName AS PersonName
,req.intReqId
,edu.intEduId
,trn.intTrainId
FROM PERSONAL AS person
LEFT 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 trn
ON trn.intCompNo = person.intCompNo
LEFT 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.rn
LEFT 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.rn
ORDER BY person.intCompNo;

intCompNo | 人名 | intReqId | 国际教育 | intTrainId
--------: | :--------- | --------: | --------: | ---------:
    12368 | 测试用户 | 213 | 107 | 5194
    12368 | 测试用户 |     空| 109 | 5203
    12368 | 测试用户 |     空|     空| 5575
    12368 | 测试用户 |     空|     空| 5580
    12368 | 测试用户 |     空|     空| 5585
    ...

在这里测试db<>fiddle