不同的聚合函数取决于数据类型

I have a T-SQL script that returns all columns in a table, along with datatype and max value MAX(DATALENGTH)) fetching it from sys.columns and sys.types.

我有一个T-SQL脚本,它返回表中的所有列,以及数据类型和最大值MAX(DATALENGTH))从sys.columns和sys.types中获取它。

However the max value will always be 4 for ints, since ints uses 4 bytes. In this case I'd rather have the highest numeric value of the column.

但是,对于整数,最大值将始终为4,因为整数使用4个字节。在这种情况下,我宁愿拥有该列的最高数值。

I figured I might change my query to use DataLength for string-based columns, and a MAX() for number based columns, however I run into some problems before I even get there:

我想我可能会更改我的查询以使用DataLength作为基于字符串的列,并使用MAX()作为基于数字的列,但是在我到达之前我遇到了一些问题:

Minified example code

DECLARE @A bit = 1
SELECT CASE WHEN 1=1 THEN MAX(DATALENGTH(@A)) ELSE MAX(@A) END

I would expect to receive the number 1 given that 1=1 is true. Instead I get an error

鉴于1 = 1是真的,我希望得到数字1。相反,我得到一个错误

Operand data type bit is invalid for max operator.

操作数数据类型位对于max运算符无效。

I understand that you can't run MAX(@A) on a bit, but that's not what I'm trying to do. My goal is to run different aggregate functions depending on the datatype.

我知道你不能稍微运行MAX(@A),但这不是我想要做的。我的目标是根据数据类型运行不同的聚合函数。

How can I solve this?

我怎么解决这个问题?

2 个解决方案

#1

My goal is to run different aggregate functions depending on the datatype.

我的目标是根据数据类型运行不同的聚合函数。

This will fail because you will get invalid cast errors or will get implicit conversions to the highest precedence data type

这将失败,因为您将获得无效的强制转换错误或将获得最高优先级数据类型的隐式转换

Your use of bit is irrelevant here

你对bit的使用与此无关

smalldatetime has the highest precedence so this code gives odd results when mixing datatypes

smalldatetime具有最高优先级,因此此代码在混合数据类型时会产生奇怪的结果

DECLARE @foo table (
 intval int,
 floatval float,
 datetimeval smalldatetime)

 INSERT @foo VALUES
 (1, 1.567E2, '2017-07-31'),
 (2, 2.0, '2017-08-01');

 DECLARE @Switch int;
 SELECT 
        CASE 
            WHEN @Switch=1 THEN MAX(intval)
            WHEN @Switch=2 THEN MAX(floatval)
            ELSE MAX(datetimeval) 
        END
FROM 
    @foo

 SET @Switch = 1
 1900-01-03 00:00:00

 SET @Switch = 2
 1900-06-06 16:48:00

 SET @Switch = 3
 2017-08-01 00:00:00

#2

In this case, you are missing a cast :

在这种情况下,您缺少一个演员:

SELECT CASE WHEN 1=1 THEN MAX(DATALENGTH(@A)) ELSE MAX(CAST(@A as bigint)) END

#1