使用并集和条件优化SQl查询

Soham Shah 发表于 Dev

苏汉·莎（Soham Shah）

我已经编写了一个查询，以根据传递的参数获取第一班，第二班，第三班或所有班的生产数量。

排班时间如下：

1st: 06:00 - 14:00
2nd: 14:00 - 22:00
3rd: 22:00 - 06:00

我的查询如下所示：

// Param.1 = date start , Param.2 = date end , Param.3 = shift start time , Param.4 = shift end time
// Param.5 = 1 when ShiftStarttime < ShiftEndtime ( Param.1 = 2015-12-26T06:00:00,  param.2 = 2015-12-26T14:00:00, param.3 = 6 , param.4 = 10 )
// Param.5 = 2 when ShiftStarttime > ShiftEndtime ( Param.1 = 2015-12-26T22:00:00,  param.2 = 2015-12-27T06:00:00, param.3 = 22 , param.4 = 2 )
// Param.5 = 3 when all of the shifts ( Param.1 = 2015-12-26T06:00:00,  param.2 = 2015-12-27T06:00:00)

SELECT 

      B.Machine_NAME,
      C.Part_Name,     
      SUM(A.QT_CONFIRMED) QT_CONFIRMED,
      D.SCRAP_REASON

 FROM 
       CONFIRMATION A 

       INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID  
       INNER JOIN PART C ON B.Part_ID = C.Part_ID
       LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID

WHERE

      '[Param.5]' = '1'
       AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND ((DATEPART(hour, DT_CONFIRMATION) >= '[Param.3]') AND (DATEPART(hour, DT_CONFIRMATION) < '[Param.4]'))

GROUP BY

      B.Machine_NAME,
      C.Part_Name,
      D.SCRAP_REASON

UNION


SELECT 

      B.Machine_NAME,
      C.Part_Name,     
      SUM(A.QT_CONFIRMED) QT_CONFIRMED,
      D.SCRAP_REASON

 FROM 
       CONFIRMATION A 

       INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID  
       INNER JOIN PART C ON B.Part_ID = C.Part_ID
       LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID


WHERE

      '[Param.5]' = '2'
       AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND (DATEPART(hour, DT_CONFIRMATION) BETWEEN 22 AND 23 OR  DATEPART(hour, DT_CONFIRMATION) BETWEEN 0 and 5)

GROUP BY

      B.Machine_NAME,
      C.Part_Name,
      D.SCRAP_REASON

UNION


SELECT 

      B.Machine_NAME,
      C.Part_Name,     
      SUM(A.QT_CONFIRMED) QT_CONFIRMED,
      D.SCRAP_REASON

 FROM 
       CONFIRMATION A 

       INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID  
       INNER JOIN PART C ON B.Part_ID = C.Part_ID
       LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID


WHERE

      '[Param.5]' = '3'
    AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') 

GROUP BY

      B.Machine_NAME,
      C.Part_Name,
      D.SCRAP_REASON

因此，基本上取决于班次开始时间和班次结束时间，由于Param.5参数，将仅执行查询的并集。请查看查询中的COMMENT以了解参数。

此查询可以正常运行并给出正确的结果，但是我敢肯定，如果我可以删除Union或对其进行某些修改，可以对其进行优化以使其运行更快。

有谁有更好的主意以更优化的方式编写此查询？如果是，请分享。

蒂姆·比格莱森（Tim Biegeleisen）

除WHERE子句不同外，这两个查询似乎相同。尝试使用将两个WHERE子句连接到单个查询中OR。请注意，我添加DISTINCT了该SELECT语句以确保仍然删除重复项（UNION操作员在原始查询中所做的操作）。

SELECT DISTINCT
    B.Machine_NAME,
    C.Part_Name,     
    SUM(A.QT_CONFIRMED) QT_CONFIRMED,
    D.SCRAP_REASON
FROM 
    CONFIRMATION A
INNER JOIN MACHINE B ON A.Machine_ID = B.Machine_ID  
INNER JOIN PART C ON B.Part_ID = C.Part_ID
LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID = D.SCRAP_REASON_ID
WHERE
    (
        '[Param.5]' = '1' AND
        (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND
        (
            (DATEPART(hour, DT_CONFIRMATION) >= '[Param.3]') AND
            (DATEPART(hour, DT_CONFIRMATION) < '[Param.4]')
        )
    )
    OR
    (
         '[Param.5]' = '2' AND
         (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND
         (
             DATEPART(hour, DT_CONFIRMATION) BETWEEN 22 AND 23 OR
             DATEPART(hour, DT_CONFIRMATION) BETWEEN 0 and 5
         )
    )
GROUP BY
    B.Machine_NAME,
    C.Part_Name,
    D.SCRAP_REASON