SQL选择日期相等或给出最大日期 [英] SQL select when dates are equal or give the max date

问题描述

你好，



我有以下数据集



ID_NO DateOfDeath PostDate

123 201501 201412

123 201501 201501

568 200601 201504

568 200601 201512



当DateOfDeath等于PostDate时，我需要选择ID_no。如果不是那么它应该选择具有最大日期的ID_no。



结果应该是：

ID_NO DateOfDeath PostDate
123 201501 201501

568 200601 201512

解决方案

如果我们可以假设PostDate永远不会大于DateOfDeath那么简单group by将：

  SELECT  ID_NO，DateOfDeath，Max（PostDate） as  PostDate  FROM  yourtable  GROUP   BY  ID_NO，DateOfDeath 

假设Microsoft SQL Server， ROW_NUMBER 功能 [ ^ ]可以解决问题：

< pre lang =sql> WITH cteOrderedData As
（
SELECT
ID_NO，
DateOfDeath，
PostDate，
ROW_NUMBER（） OVER
（
PARTITION BY
ID_NO
ORDER BY
CASE
WHEN DateOfDeath = PostDate 那么 0
ELSE 1
END ，
PostDate DESC
） As RN
FROM
YourTable
）
SELECT
ID_NO，
DateOfDeath，
PostDate
FROM
cteOrderedData
WHERE
RN = 1
;

Hi there,

I have the following dataset

ID_NO DateOfDeath PostDate
123 201501 201412
123 201501 201501
568 200601 201504
568 200601 201512

I need to select the ID_no when the DateOfDeath is equal to the PostDate. If it is not then it should select the ID_no with the maximum date.

The result should be:
ID_NO DateOfDeath PostDate
123 201501 201501
568 200601 201512

解决方案

If we can assume that PostDate can never be greater than DateOfDeath then simple group by will do:

SELECT ID_NO, DateOfDeath, Max(PostDate) as PostDate FROM yourtable GROUP BY ID_NO, DateOfDeath

Assuming Microsoft SQL Server, the ROW_NUMBER function[^] will do the trick:

WITH cteOrderedData As
(
    SELECT
        ID_NO,
        DateOfDeath,
        PostDate,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                ID_NO
            ORDER BY
                CASE
                    WHEN DateOfDeath = PostDate THEN 0
                    ELSE 1
                END,
                PostDate DESC
        ) As RN
    FROM
        YourTable
)
SELECT
    ID_NO,
    DateOfDeath,
    PostDate
FROM
    cteOrderedData
WHERE
    RN = 1
;

这篇关于SQL选择日期相等或给出最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！