mysql - SQL 联接性能注意事项

Question

我正在寻找更好的方法来实现我使用查询 2 实现的结果 2。简而言之，我正在尝试将文件和 CD 列合并为每个 SalesDetailId 的一行。很明显，查询 2 的性能并不理想。查询 1 和结果 1 是我开始的内容，它们显示了我正在使用的数据结构类型。

查询 1:

SELECT 
  SDT.SalesDetailId,
  RlFile.Code AS FILE,
  RlCD.Code AS CD
FROM SalesDetail SDT
INNER JOIN Sales S
  ON S.SalesID = SDT.SalesID
LEFT JOIN SalesReflookup SRL 
    ON SRL.SalesID = S.SalesID AND SRL.RefLookupTypeId IN (30,200)  
LEFT JOIN reflookup.reflookup RlFileStatus 
    ON RlFileStatus.reflookupid = SRL.reflookupid AND RlFile.reflookuptypeid = 30
LEFT JOIN reflookup.reflookup RlCatastrophe 
    ON RlCD.reflookupid = SRL.reflookupid AND RlCatastrophe.reflookuptypeid = 200
WHERE 
    SDT.SalesDetailId = 4042910

结果 1:

| SalesDetailId | FILE |  CD  |
|       4042910 | C    | NULL |
|       4042910 | NULL | 95E  |

查询 2:

SELECT 
  SDT.SalesDetailId,
  FILE.Code AS FILE,
  CD.Code AS CD
FROM SalesDetail SDT
INNER JOIN Sales S
  ON S.SalesID = SDT.SalesID
LEFT JOIN (SELECT
          RL.code,SRL.SalesID
          FROM reflookup.reflookup RL
          INNER JOIN SalesReflookup SRL 
            ON RL.reflookupid = SRL.reflookupid AND RL.reflookuptypeid = 30) FILE ON FILE.SalesID = S.SalesID
LEFT JOIN (SELECT
          RL.code,SRL.SalesID
          FROM reflookup.reflookup RL
          INNER JOIN SalesReflookup SRL 
            ON RL.reflookupid = SRL.reflookupid AND RL.reflookuptypeid = 200) CD ON CD.SalesID = S.SalesID

WHERE SDT.SalesDetailId = 4042910

结果 2(期望的结果)

| SalesDetailId | FILE | CD |
|       4042910 | C           | 95E            |

Answer 1

您可以在第一个查询中使用 GROUP BY:

SELECT SDT.SalesDetailId,
       MAX(RlFile.Code) AS FILE,
       MAX(RlCD.Code) AS CD
FROM . . .
WHERE  SDT.SalesDetailId = 4042910
GROPU BY SDT.SalesDetailId;