主页 / dba / 问题 / 53578
Accepted
Matthew
Asked: 2013-11-20 09:35:36 +0800 CST

“Safe to SUM”的合适 SQL 方法是什么？

  • 772

不幸的是，在 SQL Server 2008 R2+ 中，我们有一些nvarchar列存储数字数据。

在某些情况下，我们希望可以对SUM数据求和。问题是对于一些参数无效的条目返回 1 ...。我明白这里发生了什么，但我正在寻找解决方法。ISNUMERIC()SUM

例如ISNUMERIC('10,1') = 1，但我不能将该值包含在 a 中SUM，所以我希望将该值从SUM... 和任何数据点中排除，事实上，这些数据点不能作为参数SUM而不被按摩...

所以我想知道确定字母数字列的内容是否可以求和的首选方法是什么？

编辑：值得注意的是，这是从 SO 上的这个答案中提取出来的：

https://stackoverflow.com/a/15833014/369775

但我不相信该答案中提供的方法是最佳的。我同意 HLGEM ：

https://stackoverflow.com/a/338292/369775

最好的解决方案是停止在 varchar 列中存储整数。显然存在数据问题，数据可解释为数字但不能转换为数字。

sql-server-2008-r2 aggregate

1 个回答

  1. Best Answer

    依赖ISNUMERIC()于此的挑战在于它可以查看大量无法汇总的数据并通过它。它不是IS_ELIGIBLE_FOR_SUM()- 如果输入可以转换为任何数字类型，它只是返回 true。逗号是允许的，因为转换为MONEY，例如，成功地工作：

    SELECT CONVERT(MONEY,'11,2');

    所以这也有效：

    SELECT SUM(CONVERT(MONEY,'11,2'));

    这可能不是你想要的。我认为在尝试执行操作之前用句点替换逗号会更安全，或者只是排除它们并以这种方式过滤掉行：

    DECLARE @t TABLE(n NVARCHAR(32));

INSERT @t VALUES(N'11,2'),(N'32'),(N'-32.4'),(N'32323'),(N'^'),(N'.'),(N'-');

SELECT SUM(CASE WHEN ISNUMERIC(n) = 1
    AND n NOT LIKE '%[^0-9.-]%' THEN CONVERT(MONEY,n) END)
  FROM @t
  WHERE ISNUMERIC(n) = 1 AND n NOT LIKE '%[^0-9.-]%';

    除了过滤器之外，您还必须执行CASE表达式的原因是您无法控制优化器将如何处理语句 - 没有CASE它可能会尝试在过滤器之前执行转换。

    当然，最明智的做法是首先停止使用错误的数据类型。如果您继续将脏数据存储在不明智的数据类型中，您就不会真正抱怨“最佳”......

    • 4

相关问题