SQL语句如下:
select productnum, product, p_unit ,
[期初数量] = sum(case when qichu=1 then account else 0 end),
[进仓数量] = sum(case when (inoroutid='101') then account else 0 end),
[出仓数量] = sum(case when (inoroutid='102') then account else 0 end)*(-1),
[期末数量] = sum( account )
from view_huizong
group by productnum, product, p_unit
问:
但是这样的查询出来的数据,有些数量(期初,进仓,出仓,期末)全是0的,
我想查询结果全是0的不要,如何修改这个SQL呢?
select productnum, product, p_unit ,
[期初数量] = sum(case when qichu=1 then account else 0 end),
[进仓数量] = sum(case when (inoroutid='101') then account else 0 end),
[出仓数量] = sum(case when (inoroutid='102') then account else 0 end)*(-1),
[期末数量] = sum( account )
from view_huizong
group by productnum, product, p_unit
问:
但是这样的查询出来的数据,有些数量(期初,进仓,出仓,期末)全是0的,
我想查询结果全是0的不要,如何修改这个SQL呢?
select * from (
select productnum, product, p_unit ,
[期初数量] = sum(case when qichu=1 then account else 0 end),
[进仓数量] = sum(case when (inoroutid='101') then account else 0 end),
[出仓数量] = sum(case when (inoroutid='102') then account else 0 end)*(-1),
[期末数量] = sum( account )
from view_huizong
group by productnum, product, p_unit
)t where [期初数量]<>0 or [进仓数量]<>0 or [出仓数量]<>0 or [期末数量]<>0
条件应该是这样吧? where [期初数量]<>0 and [进仓数量]<>0 and [出仓数量]<>0 and [期末数量]<>0
[期初数量] = sum(case when qichu=1 then account else 0 end),
[进仓数量] = sum(case when (inoroutid='101') then account else 0 end),
[出仓数量] = sum(case when (inoroutid='102') then account else 0 end)*(-1),
[期末数量] = sum( account )
from view_huizong
group by productnum, product, p_unit
where
sum(case when qichu=1 then account else 0 end)
+sum(case when (inoroutid='101') then account else 0 end)
+sum(case when (inoroutid='102') then account else 0 end)*(-1)
+sum( account )
>0
select productnum, product, p_unit ,
[期初数量] = sum(case when qichu=1 then account else 0 end),
[进仓数量] = sum(case when (inoroutid='101') then account else 0 end),
[出仓数量] = sum(case when (inoroutid='102') then account else 0 end)*(-1),
[期末数量] = sum( account )
from view_huizong
group by productnum, product, p_unit
)t where [期初数量]+ [进仓数量]+ [出仓数量]+[期末数量]>0这个会比上面的看着帅点。
那么:
where [期初数量]+ [进仓数量]+ [出仓数量]+[期末数量]>0
与
where [期初数量]<>0 or [进仓数量]<>0 or [出仓数量]<>0 or [期末数量]<>0
哪种好些呢?执行效率高呢?
不过,我通常用第一种方法where [期初数量]+ [进仓数量]+ [出仓数量]+[期末数量]>0
,没有太注意性能问题