代码之家 › 专栏 › 技术社区 › MartW

计算SQL Server中N列的最大/最小值的最佳方法

tsql sql-server sql

MartW · 技术社区 · 17 年前

好的,首先我看到 this thread . 但没有一个解决方案是非常令人满意的。被提名的答案看起来是空的,而评分最高的答案看起来很难维持。

CREATE FUNCTION GetMaxDates
(
    @dte1 datetime,
    @dte2 datetime,
    @dte3 datetime,
    @dte4 datetime,
    @dte5 datetime
)
RETURNS datetime
AS
BEGIN
    RETURN (SELECT Max(TheDate)
        FROM
        (
            SELECT @dte1 AS TheDate
            UNION ALL
            SELECT @dte2 AS TheDate
            UNION ALL
            SELECT @dte3 AS TheDate
            UNION ALL
            SELECT @dte4 AS TheDate
            UNION ALL
            SELECT @dte5 AS TheDate) AS Dates
        )
END
GO

6 回复 | 直到 9 年前

Lukasz Lysik 17 年前

SELECT ISNULL(@dte1,0) AS TheDate
UNION ALL
SELECT ISNULL(@dte2,0) AS TheDate
UNION ALL
SELECT ISNULL(@dte3,0) AS TheDate
UNION ALL
SELECT ISNULL(@dte4,0) AS TheDate
UNION ALL
SELECT ISNULL(@dte5,0) AS TheDate) AS Dates

但它只适用于MAX函数。

这是 : http://www.sommarskog.se/arrays-in-sql-2005.html

它们建议使用字符串形式的逗号分隔值。

该函数需要 尽可能多的参数

CREATE FUNCTION GetMaxDate
(
 @p_dates VARCHAR(MAX)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @pos INT, @nextpos INT, @date_tmp DATETIME, @max_date DATETIME, @valuelen INT

SELECT @pos = 0, @nextpos = 1
SELECT @max_date = CONVERT(DATETIME,0)


WHILE @nextpos > 0
BEGIN
   SELECT @nextpos = charindex(',', @p_dates, @pos + 1)
   SELECT @valuelen = CASE WHEN @nextpos > 0
      THEN @nextpos
      ELSE len(@p_dates) + 1
      END - @pos - 1
   SELECT @date_tmp = CONVERT(DATETIME, substring(@p_dates, @pos + 1, @valuelen))

    IF @date_tmp > @max_date
SET @max_date = @date_tmp

SELECT @pos = @nextpos
END

RETURN @max_date
END

并致电:

DECLARE @dt1 DATETIME
DECLARE @dt2 DATETIME
DECLARE @dt3 DATETIME
DECLARE @dt_string VARCHAR(MAX)

SET @dt1 = DATEADD(HOUR,3,GETDATE())
SET @dt2 = DATEADD(HOUR,-3,GETDATE())
SET @dt3 = DATEADD(HOUR,5,GETDATE())

SET @dt_string = CONVERT(VARCHAR(50),@dt1,21)+','+CONVERT(VARCHAR(50),@dt2,21)+','+CONVERT(VARCHAR(50),@dt3,21)
SELECT dbo.GetMaxDate(@dt_string)

Jeff Hornby 17 年前

为什么不只是:

SELECT Max(TheDate)        
FROM        
(
    SELECT @dte1 AS TheDate WHERE @dte1 IS NOT NULL
    UNION ALL
    SELECT @dte2 AS TheDate WHERE @dte2 IS NOT NULL
    UNION ALL
    SELECT @dte3 AS TheDate WHERE @dte3 IS NOT NULL
    UNION ALL
    SELECT @dte4 AS TheDate WHERE @dte4 IS NOT NULL
    UNION ALL
    SELECT @dte5 AS TheDate WHERE @dte5 IS NOT NULL) AS Dates

这应该在不引入任何新值的情况下解决null问题

Chris Chilvers 17 年前

更好的选择是重新构造数据以支持基于列的min/max/avg,因为这是SQL最擅长的。

在SQLServer2005中,可以使用 UNPIVOT

并非总是适用于所有问题,但如果您可以使用它,则可以使事情变得更容易。

http://msdn.microsoft.com/en-us/library/ms177410.aspx http://blogs.msdn.com/craigfr/archive/2007/07/17/the-unpivot-operator.aspx

OMG Ponies 17 年前

我将以XML形式传递日期(可以使用varchar/etc,也可以转换为XML数据类型):

DECLARE @output DateTime
DECLARE @test XML
    SET @test = '<VALUES><VALUE>1</VALUE><VALUE>2</VALUE></VALUES>'

DECLARE @docHandle int 
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc 

SET @output = SELECT MAX(TheDate)
                FROM (SELECT t.value('./VALUE[1]','DateTime') AS 'TheDate'
                        FROM OPENXML(@docHandle, '//VALUES', 1) t)

EXEC sp_xml_removedocument @docHandle

RETURN @output

这将解决处理尽可能多的可能性的问题,而且我不会费心在xml中设置null。

我将使用一个单独的参数来指定日期类型,而不是自定义xml&每次都支持代码,但您可能需要使用动态SQL才能使其正常工作。

Niikola 17 年前

如果你必须只做一排,你将如何做并不重要(一切都会足够快)。

对于选择每行几列的最小/最大/平均值,使用UNPIVOT的解决方案应该比UDF快得多

stefano m 9 年前

CREATE TYPE [Maps].[TblListInt] AS TABLE(   [ID] [INT] NOT NULL )

那么,

CREATE FUNCTION dbo.GetMax(@ids maps.TblListInt READONLY) RETURNS INT
BEGIN
RETURN (select max(id) from @ids)
END