SQL Server 创建时间表时出现问题

qgelzfjb 于 2022-11-21 发布在其他

关注(0)|答案(1)|浏览(122)

我一直在尝试使用SQL Server创建时间表，并提出了以下查询。

SELECT *
INTO #TempConsultantSchedule
FROM SmConsultantSchedule
WHERE ConsultantId = 32
    AND StartDate >= '2022-11-14 00:00:00' AND EndDate <= '2022-11-18 00:00:00'


    SELECT '08:00' AS Slot,
        A.StuName AS Mon,
        B.StuName AS Tue,
        C.StuName AS Wed,
        D.StuName AS Thur,
        E.StuName AS Fri
    FROM (  SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                #TempConsultantSchedule.StudentId
            FROM #TempConsultantSchedule
            INNER JOIN StuStudent
                ON #TempConsultantSchedule.StudentId = StuStudent.Id
            WHERE StartTime >= '08:00:00' AND EndTime <= '08:30:00'     
                AND DATENAME(WEEKDAY, StartDate) = 'Monday'
            GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) A
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '08:00:00' AND EndTime <= '08:30:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Tuesday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) B
    ON A.StudentId =  B.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '08:00:00' AND EndTime <= '08:30:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Wednesday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) C
    ON B.StudentId =  C.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '08:00:00' AND EndTime <= '08:30:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Thursday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) D
    ON C.StudentId = D.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '08:00:00' AND EndTime <= '08:30:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Friday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) E
    ON D.StudentId = E.StudentId

    UNION ALL

    SELECT '08:30' AS Slot,
        A.StuName AS Mon,
        B.StuName AS Tue,
        C.StuName AS Wed,
        D.StuName AS Thu,
        E.StuName AS Fri
    FROM (  SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                #TempConsultantSchedule.StudentId
            FROM #TempConsultantSchedule
            INNER JOIN StuStudent
                ON #TempConsultantSchedule.StudentId = StuStudent.Id
            WHERE StartTime >= '08:30:00' AND EndTime <= '09:00:00'     
                AND DATENAME(WEEKDAY, StartDate) = 'Monday'
            GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) A
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '08:30:00' AND EndTime <= '09:00:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Tuesday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) B
    ON A.StudentId =  B.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '08:30:00' AND EndTime <= '09:00:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Wednesday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) C
    ON B.StudentId =  C.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '08:30:00' AND EndTime <= '09:00:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Thursday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) D
    ON C.StudentId = D.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '08:30:00' AND EndTime <= '09:00:00'         
                    AND DATENAME(WEEKDAY, StartDate) = 'Friday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) E
    ON D.StudentId = E.StudentId

    UNION ALL

    SELECT '09:00' AS Slot,
        ISNULL(A.StuName, '') AS Mon,
        ISNULL(B.StuName, '') AS Tue,
        ISNULL(C.StuName, '') AS Wed,
        ISNULL(D.StuName, '') AS Thu,
        ISNULL(E.StuName, '') AS Fri
    FROM (  SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                #TempConsultantSchedule.StudentId
            FROM #TempConsultantSchedule
            INNER JOIN StuStudent
                ON #TempConsultantSchedule.StudentId = StuStudent.Id
            WHERE StartTime >= '09:00:00' AND EndTime <= '09:30:00'     
                AND DATENAME(WEEKDAY, StartDate) = 'Monday'
            GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) A
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '09:00:00' AND EndTime <= '09:30:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Tuesday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) B
    ON A.StudentId =  B.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '09:00:00' AND EndTime <= '09:30:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Wednesday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) C
    ON B.StudentId =  C.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '09:00:00' AND EndTime <= '09:30:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Thursday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) D
    ON C.StudentId = D.StudentId
    LEFT JOIN ( SELECT StuStudent.FirstName + ' ' + StuStudent.LastName AS StuName,
                    #TempConsultantSchedule.StudentId
                FROM #TempConsultantSchedule
                INNER JOIN StuStudent
                    ON #TempConsultantSchedule.StudentId = StuStudent.Id
                WHERE StartTime >= '09:00:00' AND EndTime <= '09:30:00'     
                    AND DATENAME(WEEKDAY, StartDate) = 'Friday'
                GROUP BY StuStudent.FirstName, StuStudent.LastName, #TempConsultantSchedule.StudentId
        ) E
    ON D.StudentId = E.StudentId

IF OBJECT_ID('tempdb..#TempConsultantSchedule', 'U') IS NOT NULL
DROP TABLE #TempConsultantSchedule

上述查询的输出会以下列格式传回。

我在这里遇到的问题是，当08：00和08：30时段有数据可用时，相应的时段会出现。但是，当09：00时段没有数据可用时，该时段不会出现。请参见下图，了解我期望它如何返回输出

如果有人能帮助我如何修改上述查询以获得预期的输出，我将不胜感激。

sql-server

来源：https://stackoverflow.com/questions/74456582/issue-while-creating-a-timetable

1条答案

按热度按时间

falq053o1#

可以简化查询的方法是使用递归生成参考时间表。
然后，您可以透视数据以创建所需的结果集。
我没有您的表模式或数据，但查询将如下所示：

WITH Slots AS
(
    SELECT DATEADD(HOUR,8,'1900-01-01') AS StartDate,DATEADD(MINUTE,30, DATEADD(HOUR,8,'1900-01-01')) AS EndDate
    UNION ALL 
    SELECT DATEADD(MINUTE,30,StartDate), DATEADD(MINUTE,30,EndDate)
    FROM Slots
    WHERE DATEPART(HOUR,EndDate) < 18
), [Days] AS
(
    SELECT DATENAME(WEEKDAY,'1900-01-01') AS [DAYNAME],CAST('1900-01-01' AS date) [DAY]
    UNION ALL
    SELECT DATENAME(WEEKDAY,DATEADD(DAY,1,[DAY])),DATEADD(DAY,1,[DAY])
    FROM [Days]
    WHERE DATEPART(WEEKDAY,[DAY]) < 6
)
SELECT FORMAT(StartDate,'HH:mm') AS Slot,
  [Monday], 
  [Tuesday], 
  [Wednesday], 
  [Thursday], 
  [Friday]
FROM  
(
    SELECT *
    FROM Slots s
    CROSS JOIN [Days] d
    LEFT JOIN SmConsultantSchedule sc ON sc.StartTime >= s.StartDate AND sc.EndTime <= EndDate AND DATENAME(WEEKDAY, sc.StartDate) = d.DAYNAME     
) AS SourceTable  
PIVOT  
(  
  MAX(sc.StuName)  
  FOR [DAYNAME] IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])  
) AS PivotTable;

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 创建时间表时出现问题

1条答案

相关问题

热门标签

最新问答