sql - 有没有更好的方法来编写这个 sql 查询（联合）？

Question

我想知道在性能和稳定性方面是否有更好的方法来编写这个 sql 查询。

因为，我想我重复了很多次代码，也许我可以用 CASE 子句或其他子句来做到这一点。

SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,
                Datediff(second, I.HoraIngreso, (SELECT HD.HoraInicio
                    FROM   [rrhh].TrabajadorHorarioDetalle HD
                            INNER JOIN [rrhh].TrabajadorHorario H
                                    ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                    WHERE  H.Estado = 1
                            AND HD.Estado = 1
                            AND HD.Dia = Datename(dw, I.Fecha)
                            AND H.IdTrabajador = I.IdTrabajador)) / 3600.0 AS Cantidad,
                'Sobretiempo en hora de ingreso...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND I.IdIncidencia = 1) AS x
    WHERE  x.Cantidad > 0.00
    UNION
    SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,
                Datediff(second, (SELECT HD.HoraInicioRefrigerio
                    FROM   [rrhh].TrabajadorHorarioDetalle HD
                            INNER JOIN [rrhh].TrabajadorHorario H
                                    ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                    WHERE  H.Estado = 1
                            AND HD.Estado = 1
                            AND HD.Dia = Datename(dw, I.Fecha)
                            AND H.IdTrabajador = I.IdTrabajador), I.HoraInicioRefrigerio) / 3600.0 AS Cantidad,
                'Sobretiempo en hora de inicio de refrigerio...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND I.IdIncidencia = 1) AS x
    WHERE  x.Cantidad > 0.00
    UNION
    SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,
                Datediff(second, I.HoraFinRefrigerio, (SELECT HD.HoraFinRefrigerio
                    FROM   [rrhh].TrabajadorHorarioDetalle HD
                        INNER JOIN [rrhh].TrabajadorHorario H
                                ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                    WHERE  H.Estado = 1
                        AND HD.Estado = 1
                        AND HD.Dia = Datename(dw, I.Fecha)
                        AND H.IdTrabajador = I.IdTrabajador)) / 3600.0 AS Cantidad,
                'Sobretiempo en hora de término de refrigerio...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND I.IdIncidencia = 1) AS x
    WHERE  x.Cantidad > 0.00
    UNION
    SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,
                Datediff(second, (SELECT HD.HoraFin
                    FROM   [rrhh].TrabajadorHorarioDetalle HD
                            INNER JOIN [rrhh].TrabajadorHorario H
                                    ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                    WHERE  H.Estado = 1
                            AND HD.Estado = 1
                            AND HD.Dia = Datename(dw, I.Fecha)
                            AND H.IdTrabajador = I.IdTrabajador), I.HoraSalida) / 3600.0 AS Cantidad,
                'Sobretiempo en hora de salida...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND I.IdIncidencia = 1) AS x
    WHERE  x.Cantidad > 0.00
    UNION
    --Search Horas Extras (HE25 y HE35) in table TrabajadorIncidencia
    SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,Datediff(second, I.HoraIngreso, I.HoraSalida) / 3600.0 AS Cantidad,'Sobretiempo en hora extra registrada...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND ( I.IdIncidencia = 2
                          OR I.IdIncidencia = 4 )) AS x
    WHERE  x.Cantidad > 0.00
    ORDER  BY x.Fecha;

前面的 sql 查询运行良好，但可能会更好。谢谢。

标签： sqlsql-server

Answer 1

您可以使用公用表表达式 (CTE) 来分解问题。在下面的示例中，我将问题分解为各个部分。我认为最终结果更容易遵循。

不要认为 CTE 会创建任何额外的处理。我经常对 SQL Server 的查询计划器如何高效地使用它们感到惊讶。

一些警告：结果可能会有所不同，因为 DATEDIFF 函数中的 SELECT 已被删除并替换为 JOIN。我将假设您与给定的谓词具有一对一的关系，否则您会遇到错误。此外，我可能在剪切和粘贴时犯了一个错误，所以如果它没有运行，请不要感到惊讶。

WITH TrabajadorIncidenciaDetalleCte AS (
    SELECT I.*, HD.HoraInicio, HD.HoraFinRefrigerio, HD.HoraFin
    FROM [rrhh].TrabajadorIncidencia I
    INNER JOIN [rrhh].TrabajadorHorarioDetalle HD ON HD.Dia = DATENAME(dw, I.Fecha)
    INNER JOIN [rrhh].TrabajadorHorario H ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario AND H.IdTrabajador = I.IdTrabajador
    WHERE  H.Estado = 1 AND HD.Estado = 1 AND I.Estado = 1 AND I.IdIncidencia = 1
)
, IncidenciaCte AS (
    SELECT IdTrabajador,Fecha, DATEDIFF(second, HoraIngreso, HoraInicio) AS Cantidad,
        'Sobretiempo en hora de ingreso...' AS Motivo, IdTrabajadorIncidencia
    FROM TrabajadorIncidenciaDetalleCte
    UNION ALL
    SELECT IdTrabajador,Fecha, DATEDIFF(second, HoraInicioRefrigerio, HoraFinRefrigerio) AS Cantidad,
        'Sobretiempo en hora de término de refrigerio...' AS Motivo, IdTrabajadorIncidencia
    FROM TrabajadorIncidenciaDetalleCte
    UNION ALL
    SELECT IdTrabajador,Fecha, DATEDIFF(second, HoraFin, HoraSalida) AS Cantidad,
        'Sobretiempo en hora de salida...' AS Motivo, IdTrabajadorIncidencia
    FROM TrabajadorIncidenciaDetalleCte
    UNION ALL
    SELECT IdTrabajador, Fecha, DATEDIFF(second, HoraIngreso, HoraSalida) AS Cantidad,
        'Sobretiempo en hora extra registrada...' AS Motivo, IdTrabajadorIncidencia
    FROM [rrhh].TrabajadorIncidencia
    WHERE Estado = 1 AND ( IdIncidencia = 2 OR IdIncidencia = 4 )   
)
SELECT x.Fecha, x.IdTrabajador, 
    CAST((x.Cantidad / 3600.0) AS DECIMAL(18, 2)) AS Cantidad,
    x.Motivo, x.IdTrabajadorIncidencia
FROM IncidenciaCte x
WHERE x.Cantidad > 0.00
ORDER BY x.Fecha;