时间戳

首页 > 解决方案 > SQL 联合的替代方案

sql - SQL 联合的替代方案

问题描述

我有以下 SQL 脚本，它当前显示基于给定时间的时间范围内的行数。例如，在给定时间之前 15 分钟和之后 15 分钟，在给定时间之前 30 分钟和之后 30 分钟等。它工作正常，但我需要从工会的角度改变它以提高性能。关于如何通过单个选择/表实现这一目标的其他方法的任何想法？

DECLARE @GameNumber int
DECLARE @EventType int

SET @GameNumber = 116
SET @EventType = 1

SELECT 'Between -120 mins and -90 mins' as TimePeriod
      ,COUNT(*) 
FROM attendance ta
LEFT OUTER JOIN SubStations sub
ON TA_ServiceNumber = sub.ServiceNumber
AND TA_DoorNumber = sub.DoorNumber
INNER JOIN [dbo].[Products] pro
ON ta.TA_GameNumber = pro.prd_GameNumber
INNER JOIN dbo.producttypes prot
ON ta.Eventtype = prot.PRD_TypeID AND @EventType = prot.PRD_EventTypeID

WHERE GameNumber = @GameNumber
AND EventType = @EventType
AND ta.TA_TimeAndDate BETWEEN DATEADD(minute, -120, pro.GameTime) AND DATEADD(minute, -90, pro.GameTime)

UNION ALL

SELECT 'Between -90 mins and -60 mins' as TimePeriod
      ,COUNT(*) AS Flow
FROM attendance ta
INNER JOIN TA_OperationCode toc
ON ta.TA_OperationCode = toc.OperationCode
LEFT OUTER JOIN SubStations sub
ON TA_ServiceNumber = sub.ServiceNumber
AND TA_DoorNumber = sub.DoorNumber
INNER JOIN [dbo].[Products] pro
ON ta.TA_GameNumber = pro.prd_GameNumber
INNER JOIN dbo.producttypes prot
ON ta.TA_Eventtype = prot.PRD_TypeID AND @EventType = prot.PRD_EventTypeID

WHERE GameNumber = @GameNumber
AND EventType = @EventType 
AND ta.TA_TimeAndDate BETWEEN DATEADD(minute, -90, pro.GameTime) AND DATEADD(minute, -60, pro.GameTime)

UNION ALL

SELECT 'Between -60 mins and -45 mins' as TimePeriod
      ,COUNT(*) AS Flow
FROM attendance ta
INNER JOIN TA_OperationCode toc
ON ta.TA_OperationCode = toc.OperationCode
LEFT JOIN SubStationssub
ON TA_ServiceNumber = sub.ServiceNumber
AND TA_DoorNumber = sub.DoorNumber
INNER JOIN [dbo].[Products] pro
ON ta.TA_GameNumber = pro.prd_GameNumber
INNER JOIN dbo.producttypes prot
ON ta.TA_Eventtype = prot.PRD_TypeID AND @EventType = prot.PRD_EventTypeID

WHERE GameNumber = @GameNumber
AND EventType = @EventType
AND ta.TA_TimeAndDate BETWEEN DATEADD(minute, -60, pro.GameTime) AND DATEADD(minute, -45, pro.GameTime)

标签： sqlsql-serverdatabase

解决方案

GROUP BY与CASE表达式一起使用：

SELECT v.TimePeriod, COUNT(*) 
FROM attendance ta LEFT OUTER JOIN
     SubStations sub
     ON TA_ServiceNumber = sub.ServiceNumber AND
        TA_DoorNumber = sub.DoorNumber INNER JOIN
        [dbo].[Products] pro
        ON ta.TA_GameNumber = pro.prd_GameNumber INNER JOIN 
        dbo.producttypes prot
        ON ta.Eventtype = prot.PRD_TypeID AND
        @EventType = prot.PRD_EventTypeID CROSS APPLY
        (VALUES (CASE WHEN ta.TA_TimeAndDate BETWEEN DATEADD(minute, -120, pro.GameTime) AND DATEADD(minute, -90, pro.GameTime)
                      THEN 'Between -90 mins and -60 mins'
                      WHEN ta.TA_TimeAndDate BETWEEN DATEADD(minute, -90, pro.GameTime) AND DATEADD(minute, -60, pro.GameTime)
                      THEN 'Between -90 mins and -60 mins'
                      . . .
                 END)
        ) v(TimePeriod)
WHERE GameNumber = @GameNumber AND EventType = @EventType
GROUP BY v.TimePeriod
ORDER BY MIN(ta.TA_TimeAndDate);

推荐阅读