ISNULL() 表示完全连接中的计算列.SQL Server

Question

我目前在计算 SQL Server 语句中的标准偏差时遇到问题。

我

的问题：我有两张桌子。

T1：

Reg_Month
---------   
1
2
3
...

T2：

Product  Month   Consumption
-------------------------------
ProdA      1       200
ProdB      1        10
ProdA      1       300
ProdC      2       100
ProdA      2       200
...

现在我想要的是这样的东西，用于计算我一年的 STDEV：

Reg_Month   Product   Month   Sum
---------------------------------
   1        ProdA       1     500
   1        ProdB       1      10
   1        ProdC       1       0
   2        ProdA       2     200
   2        ProdB       2       0
   2        ProdC       2       0

所以现在我不需要设置这个表，但我需要计算每个产品的"总和"列的 STDEV 和 AVG。问题是包含空值。

这为我提供了我想要的 ProdA 表，但只要没有消耗，就会使用 NULLS：

SELECT * 
FROM T1
FULL JOIN (SELECT Product, Month, SUM(Consumption) AS Sum, 
           FROM T2
           WHERE (Product = 'ProdA')
           GROUP BY Month, Product) sub ON (T1.Reg_Month = T2.Month)`

这给了我 STDEV：

SELECT Stdev(Sum) 
FROM 
    (SELECT * 
     FROM T1
     FULL JOIN (SELECT Product, Month, SUM(Consumption) AS Sum, 
                FROM T2
                WHERE (Product = 'ProdA')
                GROUP BY Month, Product) sub ON (T1.Reg_Month = T2.Month)) sub 
WHERE Product = 'ProdA'`

但问题是，如果有一个月份没有消耗，它不会给我全年的正确 STDEV，因为 NULL（由于连接而出现）被忽略。

我的方法：

ISNULL() ：

SELECT Stdev(Sum) 
FROM 
    (SELECT * 
     FROM T1
     FULL JOIN (SELECT Product, Month, ISNULL(SUM(Consumption), 0) AS Sum, 
                FROM T2
                WHERE (Product = 'ProdA')
                GROUP BY Month, Product) sub ON (T1.Reg_Month = T2.Month)) sub 
WHERE Product = 'ProdA'`

不起作用（也许是因为连接后生成了空值？

CASE ：

SELECT Stdev(Sum) 
FROM 
    (SELECT * 
     FROM T1
     FULL JOIN (SELECT Product, Month, CASE WHEN SUM(Consumption) IS NULL THEN 0 ELSE Sum(Consumption) END AS Sum, 
                FROM T2
                WHERE (Product = 'ProdA')
                GROUP BY Month, Product) sub ON (T1.Reg_Month = T2.Month)) sub 
WHERE Product = 'ProdA'

不起作用（可能出于同样的原因）

我希望我能够正确地说明我的例子。现在，您知道如何为 STDEV 获得正确的结果吗？

您的意见将不胜感激！

多谢

克莱门斯

Answer 1

如果你一次只做一个产品

SELECT sum(isnull(T2month.MonthSum ,0))
     , Stdev(isnull(T2month.MonthSum ,0))
  FROM T1
  LEFT JOIN
       (select Month, sum(Consumption) as MonthSum 
          from T2
         where Product = 'ProdA' 
         group by Month) T2month 
    ON T1.Reg_Month = T2month.Month

适用于所有产品

SELECT Product.Name  
     , sum(isnull(T2month.MonthSum ,0))
     , Stdev(isnull(T2month.MonthSum ,0))
  FROM T1 
  cross apply  
       (values ('ProdA'), ('ProdB'), ('ProdC')) Product(Name)
  LEFT JOIN
       (select Product, Month, sum(Consumption) as MonthSum 
          from T2
         group by Product, Month) T2month 
    ON T1.Reg_Month = T2month.Month 
   AND T2month.Product = Product.Name 
 Group By Product.Name

嘿 OP 左加入不会遗漏句点
这就是左联接的作用

select lj.val, isnull(jj.val,0)
  from ( values (1), (2), (3), (4) ) lj(val) 
  left join ( values (1), (2), (4) ) jj(val) 
    on lj.val = jj.val 
 order by lj.val