获取多个列之间的最小值

I'm using SQL Server 2008;

我正在使用SQL Server 2008;

Suppose I have a table 'X' with columns 'Date1', 'Date2', 'Dateblah', all of type DateTime.

假设我有一个表'X',其列有'Date1','Date2','Dateblah',所有类型都是DateTime。

I want to select the min value between the three columns, for example (simplified, with date mm/dd/yyyy)

我想选择三列之间的最小值,例如(简化,日期mm / dd / yyyy)

ID       Date1          Date2           Dateblah
0     09/29/2011      09/20/2011       09/01/2011 
1     01/01/2011      01/05/2011       03/03/2010

ID    MinDate
0    09/01/2011
1    03/03/2010

Is there a bread and butter command to do that ?

有没有面包和黄油的命令呢?

Thanks in advance.

提前致谢。

EDIT: I've seen this question What's the best way to select the minimum value from several columns? but unfortunately it won't suit me as I'm being obligated to do it against normalization because I'm making tfs work item reports, and the 'brute-force' case thing will end up being a pain if I have 6 ou 7 columns.

编辑:我看过这个问题从几列中选择最小值的最佳方法是什么?但不幸的是,它不适合我,因为我有义务反对规范化,因为我正在制作tfs工作项目报告,如果我有6 ou 7,那么'暴力'案例的事情最终将会很痛苦列。

4 个解决方案

#1

There is no built in function to return the min/max of two (or more) columns. You could implement your own scalar function to do this.

没有内置函数来返回两个(或更多)列的最小值/最大值。您可以实现自己的标量函数来执行此操作。

In SQL Server 2005+ you could use UNPIVOT to turn the columns into rows and then use the MIN function:

在SQL Server 2005+中,您可以使用UNPIVOT将列转换为行,然后使用MIN函数:

CREATE TABLE [X]
(
    [ID] INT,
    [Date1] DATETIME,
    [Date2] DATETIME,
    [Date3] DATETIME
)

INSERT  [X]
VALUES  (0, '09/29/2011', '09/20/2011', '09/01/2011'),
        (1, '01/01/2011', '01/05/2011', '03/03/2010')


SELECT [ID], MIN([Date]) AS [MinDate]
FROM [X]
UNPIVOT (
    [Date] FOR d IN
        ([Date1]
        ,[Date2]
        ,[Date3])
) unpvt
GROUP BY [ID]

#2

based on scalar function (from Tom Hunter):

基于标量函数(来自Tom Hunter):

SELECT ID, (SELECT MIN([date]) FROM (VALUES(Date1),(Date2),(Dateblah)) x([date])) MinDate
FROM TableName

#3

Implementing a scalar function:

实现标量函数:

CREATE FUNCTION [dbo].[MIN](@a SQL_VARIANT, @b SQL_VARIANT)
RETURNS SQL_VARIANT
AS 
BEGIN
    RETURN (
        SELECT MIN([x])
        FROM (VALUES(@a),(@b)) x([x])
    )   
END
GO

DECLARE @a DATETIME = '12 JUL 2011', @b DATETIME = '20 AUG 2011'
SELECT [dbo].[MIN](@a, @b)

DECLARE @c INT = 12, @d INT = 32
SELECT [dbo].[MIN](@c, @d)

#4

Simply lets say the table where your dates are is called sells and it has two date fields Date1 and Date2 from which you want the minimum.

简单地说,您的日期所在的表称为卖出,它有两个日期字段Date1和Date2,您希望从中得到最小值。

SELECT ( 
   SELECT MIN([x]) 
   FROM (VALUES(Date1),(Date2)) x([x])
) as minimum
FROM sells

#1