none
sum 的使用 RRS feed

  • 问题

  • 对于下面两个sql,方法二运行没有错误,但其中方法一的sql报错,但是我看逻辑并没有错,计算方法也不走第二个case when,为何会报错呢?

    CREATE TABLE #TEST(
    ID VARCHAR(10),
    PRICE1 FLOAT,
    PRICE2 FLOAT,
    PRICE3 FLOAT,
    UCOMMRATE SMALLINT
    )

    INSERT INTO #TEST
    SELECT 'IU00000003','20','30','40','0'
    INSERT INTO #TEST
    SELECT 'IU00000004','20','30','40','2'
    INSERT INTO #TEST
    SELECT 'IU00000005','21','31','41','0'
    INSERT INTO #TEST
    SELECT 'IC00000006','21','31','41','3'
    INSERT INTO #TEST
    SELECT 'IU00000007','22','32','42','0'
    INSERT INTO #TEST
    SELECT 'IC00000008','23','33','43','4'


    方法一:
    SELECT ID,UCOMMRATE,
    CASE 
    when substring(ID,2,1)='u'  and UCOMMRATE=0 then SUM(PRICE1)
    when substring(ID,2,1)='u' and UCOMMRATE IS NOT NULL AND UCOMMRATE<>0 
    then SUM(PRICE2/UCOMMRATE)
    else SUM(PRICE3) END
    FROM #TEST
    GROUP BY ID,UCOMMRATE

    方法二:

    SELECT ID,UCOMMRATE,
    SUM(CASE 
    when substring(ID,2,1)='u'  and UCOMMRATE=0 then PRICE1
    when substring(ID,2,1)='u' and UCOMMRATE IS NOT NULL AND UCOMMRATE<>0 
    then PRICE2/UCOMMRATE
    else PRICE3 END)
    FROM #TEST
    GROUP BY ID,UCOMMRATE

    2015年9月7日 5:13

答案

  • 处理顺序问题,看执行计划,倒数第2行,优先处理的是 PRICE2/UCOMMRATE, 也就是说,在 CASE 和 SUM 之前,SQL Server 先把这个表达式的结果计算出来,用于后面的 CASE WHEN 中的 SUM, 既然是先算这个表达式,所以出现除0的错误就可以解释了

     |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN substring([tempdb].[dbo].[#TEST].[ID] as [a].[ID],(2),(1))='u' AND [tempdb].[dbo].[#TEST].[UCOMMRATE] as [a].[UCOMMRATE]=(0) THEN [Expr1002] ELSE CASE WHEN substring([tempdb].[dbo].[#TEST].[ID] as [a].[ID],(2),(1))='u' AND [tempdb].[dbo].[#TEST].[UCOMMRATE] as [a].[UCOMMRATE] IS NOT NULL AND [tempdb].[dbo].[#TEST].[UCOMMRATE] as [a].[UCOMMRATE]<>(0) THEN [Expr1003] ELSE [Expr1004] END END))
           |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Expr1019]=(0) THEN NULL ELSE [Expr1020] END, [Expr1003]=CASE WHEN [Expr1021]=(0) THEN NULL ELSE [Expr1022] END, [Expr1004]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END))
                |--Stream Aggregate(GROUP BY:([a].[UCOMMRATE], [a].[ID]) DEFINE:([Expr1019]=COUNT_BIG([tempdb].[dbo].[#TEST].[PRICE1] as [a].[PRICE1]), [Expr1020]=SUM([tempdb].[dbo].[#TEST].[PRICE1] as [a].[PRICE1]), [Expr1021]=COUNT_BIG([Expr1006]), [Expr1022]=SUM([Expr1006]), [Expr1023]=COUNT_BIG([tempdb].[dbo].[#TEST].[PRICE3] as [a].[PRICE3]), [Expr1024]=SUM([tempdb].[dbo].[#TEST].[PRICE3] as [a].[PRICE3])))
                     |--Sort(ORDER BY:([a].[UCOMMRATE] ASC, [a].[ID] ASC))
                          |--Compute Scalar(DEFINE:([Expr1006]=[tempdb].[dbo].[#TEST].[PRICE2] as [a].[PRICE2]/CONVERT_IMPLICIT(float(53),[tempdb].[dbo].[#TEST].[UCOMMRATE] as [a].[UCOMMRATE],0)))
                               |--Table Scan(OBJECT:([tempdb].[dbo].[#TEST] AS [a]))

    • 已标记为答案 啵啵猪 2015年9月8日 2:24
    2015年9月7日 6:04

全部回复

  • 处理顺序问题,看执行计划,倒数第2行,优先处理的是 PRICE2/UCOMMRATE, 也就是说,在 CASE 和 SUM 之前,SQL Server 先把这个表达式的结果计算出来,用于后面的 CASE WHEN 中的 SUM, 既然是先算这个表达式,所以出现除0的错误就可以解释了

     |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN substring([tempdb].[dbo].[#TEST].[ID] as [a].[ID],(2),(1))='u' AND [tempdb].[dbo].[#TEST].[UCOMMRATE] as [a].[UCOMMRATE]=(0) THEN [Expr1002] ELSE CASE WHEN substring([tempdb].[dbo].[#TEST].[ID] as [a].[ID],(2),(1))='u' AND [tempdb].[dbo].[#TEST].[UCOMMRATE] as [a].[UCOMMRATE] IS NOT NULL AND [tempdb].[dbo].[#TEST].[UCOMMRATE] as [a].[UCOMMRATE]<>(0) THEN [Expr1003] ELSE [Expr1004] END END))
           |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Expr1019]=(0) THEN NULL ELSE [Expr1020] END, [Expr1003]=CASE WHEN [Expr1021]=(0) THEN NULL ELSE [Expr1022] END, [Expr1004]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END))
                |--Stream Aggregate(GROUP BY:([a].[UCOMMRATE], [a].[ID]) DEFINE:([Expr1019]=COUNT_BIG([tempdb].[dbo].[#TEST].[PRICE1] as [a].[PRICE1]), [Expr1020]=SUM([tempdb].[dbo].[#TEST].[PRICE1] as [a].[PRICE1]), [Expr1021]=COUNT_BIG([Expr1006]), [Expr1022]=SUM([Expr1006]), [Expr1023]=COUNT_BIG([tempdb].[dbo].[#TEST].[PRICE3] as [a].[PRICE3]), [Expr1024]=SUM([tempdb].[dbo].[#TEST].[PRICE3] as [a].[PRICE3])))
                     |--Sort(ORDER BY:([a].[UCOMMRATE] ASC, [a].[ID] ASC))
                          |--Compute Scalar(DEFINE:([Expr1006]=[tempdb].[dbo].[#TEST].[PRICE2] as [a].[PRICE2]/CONVERT_IMPLICIT(float(53),[tempdb].[dbo].[#TEST].[UCOMMRATE] as [a].[UCOMMRATE],0)))
                               |--Table Scan(OBJECT:([tempdb].[dbo].[#TEST] AS [a]))

    • 已标记为答案 啵啵猪 2015年9月8日 2:24
    2015年9月7日 6:04
  • 这种也不好说是 BUG,这种处理顺序先后导致的问题在其他场景下也有

    比如 where  a=1 and b=2 这样一个简单条件,假设 a 是 int, b 是字符串, a=1中的所有数据,b列都能转换为 int, 而 a=1之外的数据,有不能转换为数字的, 正常情况下,如果是先处理条件 a=1, 那么查询是正常的,但有时候也可能是先处理条件 b=2,这就会出来数据类型转换失败的错误了

    2015年9月7日 6:08
  • 谢谢邹大哥,根据你的提示,通过查看执行计划就可以找到原因了。
    2015年9月8日 2:24