SQL以排除NULL或值0

Question

我有下面的SQL代码，

SELECT s.[CusNo] Supp, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50))) AS Name,
s.[ConNo] Con, 
RTRIM(CAST(s.[ConN] AS VARCHAR(50))) AS ConN,       
sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
then s.SELL else 0 end) ActualSales
FROM [dbo].[Reports] s
WHERE s.BOX = 2
AND   s.SELL <> 0
GROUP BY s.[CusNo], s.[Customer], s.ConNo, s.ConN

当我执行上面的查询时，我得到ActualSales列中的所有值(包括0值(。

如何删除值为0的行？

Answer 1

使用having子句

SELECT s.[CusNo] Supp, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50)) ) AS Name,
s.[ConNo] Con, 
RTRIM(CAST(s.[ConN] AS VARCHAR(50)) ) AS ConN,
sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
then s.SELL else 0 end) ActualSales
FROM [dbo].[Reports] s
WHERE s.BOX = 2 and s.SELL <> 0
GROUP BY s.[CusNo], s.[Customer], s.ConNo, s.ConN
having sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
then s.SELL else 0 end)<>0

或者您可以使用子查询

select * from
(
SELECT s.[CusNo] Supp, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50)) ) AS Name,
s.[ConNo] Con, 
RTRIM(CAST(s.[ConN] AS VARCHAR(50)) ) AS ConN,
sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
then s.SELL else 0 end) ActualSales
FROM [dbo].[Reports] s
WHERE s.BOX = 2 and s.SELL <> 0
GROUP BY s.[CusNo], s.[Customer], s.ConNo, s.ConN
)A where ActualSales<>0

Answer 2

我认为您可以通过将case条件移动到where子句来获得所需内容：

SELECT s.[CusNo] as Supp, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50)) ) AS Name,
s.[ConNo] as Con, 
RTRIM(CAST(s.[ConN] AS VARCHAR(50)) ) AS ConN,
SUM(s.SELL) as ActualSales
FROM [dbo].[Reports] s
WHERE s.BOX = 2 AND s.SELL <> 0 AND
s.Date BETWEEN convert(date, dateadd(wk, datediff(week, 0, getdate()) - 1, 0) - 1) and
convert(date,dateadd(wk, datediff(week, 0, getdate()) - 1, 0) + 5)
GROUP BY s.[CusNo], s.[Customer], s.ConNo, s.ConN;

ActualSales可能仍然是0(如果Sales可能是负的。在这种情况下，你会想要一个HAVING子句：HAVING SUM(Sales) <> 0。

因为这种方法在聚合之前进行过滤，所以它也可能有利于性能。