SQL中如何筛选各地点最新日期的属性数据

要从你的历史数据集中提取每个地点的最新日期对应结果，最简洁高效的方法是使用窗口函数ROW_NUMBER()，按地点分组后对样本日期倒序排序，取每组中排序第一的记录（即最新日期数据）。

以下是修改后的完整SQL语句：

WITH RankedSamples AS (
    SELECT 
        StationName, 
        CONVERT(date, SampleDate_D) AS SampleDate, 
        LongName, 
        Value, 
        ReportingUnits, 
        FlagCode, 
        DetectedResult, 
        SampleMatrix,
        -- 按地点分组，对日期倒序排名，最新日期排第1
        ROW_NUMBER() OVER (PARTITION BY StationName ORDER BY SampleDate_D DESC) AS RowNum
    FROM Analyses AS a
    LEFT JOIN Samples AS s ON s.SampleNumber=a.SampleNumber
    LEFT JOIN SampleMatrix AS sm ON s.SampleMatrixCode=sm.SampleMatrixCode
    LEFT JOIN Stations AS st ON st.StationNumber=s.StationNumber
    LEFT JOIN Parameters AS p ON p.ParameterNumber=a.ParameterNumber
    LEFT JOIN StationTypes AS stp ON stp.StationTypeCode=st.StationTypeCode
    LEFT JOIN ReportingUnits AS ru ON ru.ReportingUnitsCode=a.ReportUnitsCode
    WHERE LongName IN ('1,4-Dioxane', 'Chlorobenzene', '1,2-Dichlorobenzene', '1,3-Dichlorobenzene', '1,4-Dichlorobenzene', '1,2,3-Trichlorobenzene', '1,2,4-Trichlorobenzene', 
            '1,3,5-Trichlorobenzene', '1,2,3,4-Tetrachlorobenzene', '1,2,3,5-Tetrachlorobenzene', '1,3,4,5-Tetrachlorobenzene', 'Pentachlorobenzene', 'Hexachlorobenzene')
        AND QCSampleCode = 'O'
)
SELECT 
    StationName, 
    SampleDate, 
    LongName, 
    Value, 
    ReportingUnits, 
    FlagCode, 
    DetectedResult, 
    SampleMatrix
FROM RankedSamples
WHERE RowNum = 1
ORDER BY StationName;

关键说明： #

• PARTITION BY StationName：将数据按地点分组，每个地点单独处理
• ORDER BY SampleDate_D DESC：对每个地点内的记录按样本日期从新到旧排序
• RowNum = 1：只保留每个地点中排名第一的记录，也就是最新日期的数据

如果你的数据库不支持CTE（公用表表达式），也可以用子查询实现：

SELECT 
    StationName, 
    SampleDate, 
    LongName, 
    Value, 
    ReportingUnits, 
    FlagCode, 
    DetectedResult, 
    SampleMatrix
FROM (
    SELECT 
        StationName, 
        CONVERT(date, SampleDate_D) AS SampleDate, 
        LongName, 
        Value, 
        ReportingUnits, 
        FlagCode, 
        DetectedResult, 
        SampleMatrix,
        ROW_NUMBER() OVER (PARTITION BY StationName ORDER BY SampleDate_D DESC) AS RowNum
    FROM Analyses AS a
    LEFT JOIN Samples AS s ON s.SampleNumber=a.SampleNumber
    LEFT JOIN SampleMatrix AS sm ON s.SampleMatrixCode=sm.SampleMatrixCode
    LEFT JOIN Stations AS st ON st.StationNumber=s.StationNumber
    LEFT JOIN Parameters AS p ON p.ParameterNumber=a.ParameterNumber
    LEFT JOIN StationTypes AS stp ON stp.StationTypeCode=st.StationTypeCode
    LEFT JOIN ReportingUnits AS ru ON ru.ReportingUnitsCode=a.ReportUnitsCode
    WHERE LongName IN ('1,4-Dioxane', 'Chlorobenzene', '1,2-Dichlorobenzene', '1,3-Dichlorobenzene', '1,4-Dichlorobenzene', '1,2,3-Trichlorobenzene', '1,2,4-Trichlorobenzene', 
            '1,3,5-Trichlorobenzene', '1,2,3,4-Tetrachlorobenzene', '1,2,3,5-Tetrachlorobenzene', '1,3,4,5-Tetrachlorobenzene', 'Pentachlorobenzene', 'Hexachlorobenzene')
        AND QCSampleCode = 'O'
) AS SubQuery
WHERE RowNum = 1
ORDER BY StationName;

内容的提问来源于stack exchange，提问作者Maggie Perry