sql - 避免子查询根据基本记录的日期从同一个表中选择记录

Question

我在 SQL Server 2012 中有一个 StudentScores 表，如下所示。评分系统使用特殊规则进行加权。对于学生的每个数学成绩，结果集中都会有一行。该行可能有也可能没有科学和文学列的分数，具体取决于“科学数学结果日期的两个月内”和“文学数学结果日期的一个月内”是否有可用分数。

注意:这是我为了简化实际业务领域问题而创建的场景。

我创建了以下带有子查询的查询。有没有一种方法可以在没有子查询的情况下更有效地重写它？

表格

DECLARE @StudentScores TABLE (StudentMarkID INT IDENTITY(1,1) NOT NULL, StudentID INT, SubjectCode VARCHAR(10), ResultDate DATETIME, Score DECIMAL(5,2))
INSERT INTO @StudentScores (StudentID,SubjectCode,ResultDate,Score)
SELECT 1, 'MATHS','2016-01-10',35
UNION ALL
SELECT 1, 'LITERATURE','2016-01-10',62
UNION ALL
SELECT 1, 'SCIENCE','2016-01-30',65
UNION ALL
SELECT 1, 'SCIENCE','2016-02-02',61
UNION ALL
SELECT 1, 'LITERATURE','2016-02-03',60
UNION ALL
SELECT 1, 'MATHS','2016-03-25',55
UNION ALL
SELECT 2, 'LITERATURE','2016-01-10',12
UNION ALL
SELECT 2, 'SCIENCE','2016-01-30',14
UNION ALL
SELECT 2, 'SCIENCE','2016-02-14',12
UNION ALL
SELECT 2, 'LITERATURE','2016-02-14',15
UNION ALL
SELECT 2, 'MATHS','2016-03-25',18

查询

SELECT SS.StudentID, Score AS MathsScore, 
ResultDate AS MathsResultDate,
    (SELECT TOP 1 Score 
            FROM @StudentScores S2 
            WHERE S2.StudentID = SS.StudentID 
            AND S2.SubjectCode = 'SCIENCE'
            AND S2.ResultDate >= DATEADD(MONTH,-2,SS.ResultDate)
            ORDER BY s2.ResultDate DESC
    ) AS ScienceScore,
    (SELECT TOP 1 ResultDate 
            FROM @StudentScores S2 
            WHERE S2.StudentID = SS.StudentID 
            AND S2.SubjectCode = 'SCIENCE'
            AND S2.ResultDate >= DATEADD(MONTH,-2,SS.ResultDate)
            ORDER BY s2.ResultDate DESC
    ) AS ScienceResultDate,
    (SELECT TOP 1 Score 
            FROM @StudentScores S2 
            WHERE S2.StudentID = SS.StudentID 
            AND S2.SubjectCode = 'LITERATURE'
            AND S2.ResultDate >= DATEADD(MONTH,-1,SS.ResultDate)
            ORDER BY s2.ResultDate DESC
    ) AS LiteratureScore,
    (SELECT TOP 1 ResultDate 
            FROM @StudentScores S2 
            WHERE S2.StudentID = SS.StudentID 
            AND S2.SubjectCode = 'LITERATURE'
            AND S2.ResultDate >= DATEADD(MONTH,-1,SS.ResultDate)
            ORDER BY s2.ResultDate DESC
    ) AS LiteratureResultDate
FROM @StudentScores SS
WHERE SS.SubjectCode = 'MATHS'

预期结果

Answer 1

我已成功将查询减少为对数据表的两次调用 - 一次用于获取数学详细信息，因为它们的日期用于提取其他科目的详细信息，第二次用于获取其他科目的详细信息:

WITH DataSource_Maths AS
(
    SELECT SS.[StudentID]
          ,SS.[Score] AS [MathsScore]
          ,SS.[ResultDate] AS [MathsResultDate]
          -- we are using this interal ID later in the final join between the two CTEs
          -- in order to know which record, for which date period refers
          ,ROW_NUMBER() OVER(ORDER BY SS.[StudentID], SS.[ResultDate]) AS InternalID
    FROM @StudentScores SS
    WHERE SS.[SubjectCode] = 'MATHS'
),
DataSource_Others AS
(
    SELECT DS.[StudentID]
          ,DS.[SubjectCode]
          ,DS.[Score]
          ,DS.[ResultDate]
          ,Ds.[RowID]
          ,SS.[InternalID]
    FROM DataSource_Maths SS
    OUTER APPLY
    (
        SELECT *
               -- calculating row ID for each record across student and subject (we are going to take only the latest ones)
               -- this is achived using TOP in your example
              ,DENSE_RANK() OVER (PARTITION BY [StudentID], [SubjectCode] ORDER BY [ResultDate] DESC) AS [RowID]
        FROM @StudentScores
        WHERE
        ( 
            [ResultDate] >= DATEADD(MONTH, -2, SS.[MathsResultDate]) AND [SubjectCode] = 'SCIENCE'
            OR
            [ResultDate] >= DATEADD(MONTH, -1, SS.[MathsResultDate]) AND [SubjectCode] = 'LITERATURE' 
        ) AND [StudentID] = SS.[StudentID]
    ) DS
)
SELECT FDS_M.[StudentID]
      ,FDS_M.[MathsScore] AS [MathsScore]
      ,FDS_M.[MathsResultDate] AS [MathsResultDate]
      ,FDS_S.[Score] AS [ScienceScore]
      ,FDS_S.[ResultDate] AS [ScienceResultDate] 
      ,FDS_L.[Score] AS [LiteratureScore]
      ,FDS_L.[ResultDate] AS [LiteratureResultDate] 
FROM DataSource_Maths FDS_M
LEFT JOIN DataSource_Others FDS_S
    ON FDS_M.[InternalID] = FDS_S.[InternalID]
    AND FDS_S.[SubjectCode] = 'SCIENCE'
    AND FDS_S.[RowID] = 1
LEFT JOIN DataSource_Others FDS_L
    ON FDS_M.[InternalID] = FDS_L.[InternalID]
    AND FDS_L.[SubjectCode] = 'LITERATURE'
    AND FDS_L.[RowID] = 1;

当然，在更复杂的示例中，您可以在临时表中具体化 CTE 子句(例如)，以简化和优化查询。