ROLLUP函数;用'Total'w / Column数据类型INT不是VARCHAR替换NULL

问题描述

I'm having some problems replacing my ROLLUP NULL with a string value because my column data type is an Integer.

我的问题是用字符串值替换ROLLUP NULL，因为我的列数据类型是整数。 SELECT CASE
GROUPING（Column1）= 1 THEN'Total'
ELSE Column1
END Column1，SUM（Column2）AS MySum
FROM MyTable
GROUP BY Column1 WITH卷起;

SELECT CASEWHEN GROUPING(Column1) = 1 THEN 'Total'ELSE Column1END Column1, SUM(Column2) AS MySumFROM MyTableGROUP BY Column1 WITH ROLLUP;

我可以输入一个数值：

I can put a numeric value in:

WHEN GROUPING(Column1) = 1 THEN '9999'

但我无法弄清楚如果value为NULL，然后用'Total'替换如何转换为varchar。

but I can't figure out how to convert to varchar if value is NULL and then replace with 'Total'.

推荐答案

测试数据

DECLARE @MyTable TABLE (Column1 INT,Column2 INT)
INSERT INTO @MyTable VALUES
(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3)

SELECT CASE
         WHEN GROUPING(Column1) = 1 THEN 'Total'
         ELSE CAST(Column1 AS VARCHAR(10))     --<-- Cast as Varchar
       END  Column1
      , SUM(Column2) AS MySum
FROM @MyTable
GROUP BY Column1 
WITH ROLLUP;

结果集

Result Set

╔═════════╦═══════╗
║ Column1 ║ MySum ║
╠═════════╬═══════╣
║ 1       ║     6 ║
║ 2       ║     6 ║
║ 3       ║     6 ║
║ Total   ║    18 ║
╚═════════╩═══════╝

注意

你不能做你想做的事情是因为当你使用 CASE 语句在每种情况下返回的数据类型应该是相同的。

The reason you couldnt do what you were trying to do is because when you use a CASE statement in each case the returned datatype should be the same.

在上面的查询中，我只是将colum1转换为varchar并且工作。

In above query I have just CAST the colum1 to varchar and it worked.

这篇关于ROLLUP函数;用'Total'w / Column数据类型INT不是VARCHAR替换NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！