MSSQL获取id和四个日期中的最小(最早)日期 - MSSQL get id and the min (earliest) of four dates

MSSQL get id and the min (earliest) of four dates

本文关键字：日期最早获取四个 id MSSQL | 更新日期: 2023-09-27

我在mssql(2005)中有一个表，其中有四个日期列和一个ID列。

我想返回所有四列的最小日期和相关的id。

SELECT TOP 1 min(date1), min(date2), min(date3), min(date4)  FROM datatable
WHERE  date1 > CAST(? AS DATETIME) OR 
     date2 > CAST(? AS DATETIME) OR
     date3 > CAST(? AS DATETIME) OR
     date4 > CAST(? AS DATETIME)

我然后排序的日期在php给我的最小行。我不知道如何添加返回id到这个。

您可以将列撤消为行，然后使用TOP 1选择最小值(因为这还允许您找出四列中哪一列是最小值):

SELECT  t.ID, upvt.Date, upvt.ColumnName
FROM    Datatable AS t
        CROSS APPLY
        (   SELECT  TOP 1 ColumnMame, Date
            FROM    (VALUES 
                        ('Date1', Date1),
                        ('Date2', Date2),
                        ('Date3', Date3),
                        ('Date4', Date4)
                    ) AS d (ColumnName, Date)
            ORDER BY d.Date 
        ) AS upvt

编辑

刚才看到这是SQL Server 2005，所以你不能使用表值构造函数来撤消数据，你需要使用SELECT和UNION ALL

SELECT  t.ID, upvt.Date, upvt.ColumnName
FROM    Datatable AS t
        CROSS APPLY
        (   SELECT  TOP 1 ColumnMame, Date
            FROM    (   SELECT Columnname = 'Date1', Date = Date1 UNION ALL
                        SELECT Columnname = 'Date2', Date = Date2 UNION ALL
                        SELECT Columnname = 'Date3', Date = Date3 UNION ALL
                        SELECT Columnname = 'Date4', Date = Date4
                    ) AS d
            ORDER BY d.Date 
        ) AS upvt;

SELECT id_column, date1
FROM datatable
WHERE (date1 < date2) AND (date1 < date3) AND (date1 < date4)
UNION
SELECT id_column, date2
FROM datatable
WHERE (date2 < date1) AND (date2 < date3) AND (date2 < date4)
UNION
SELECT id_column, date3
FROM datatable
WHERE (date3 < date1) AND (date3 < date2) AND (date3 < date4)
UNION
SELECT id_column, date4
FROM datatable
WHERE (date4 < date1) AND (date4 < date2) AND (date4 < date3)