公司内部ERP软件的一个查询商品批号数量的语句
select a.hw,a.ljshl as '批次数量',a.ljshl-isnull((select sum(jm.shl) as shl from jzorder_mx jm
where jm.is_zx='否' and (jm.djbh like 'XSG%' or jm.djbh like 'jht%') and pici=a.pici and spid=a.spid and pihao=a.pihao and hw=a.hw),0) -isnull((select isnull(sum(shl),0) from tmp_dj_XSG212 where spid=a.spid and pihao=a.pihao and hw=b.hw and pici=a.pici),0) as '可开数量',
huowname,a.pihao,a.sxrq,a.pici
from sphwphpc a,huoweizl b,spkfk c,sphwph h
where b.beactive='是' and a.hw=b.hw and (a.spid=isnull('SPH00004414' ,''))
and a.spid=h.spid and a.pihao=h.pihao and a.hw=h.hw
and a.spid=c.spid and h.dangqzht='合格' and a.ljshl>0
order by b.type ,h.sxrq
得到的结果
hw 批次数量 可开数量 huowname pihao sxrq pici
HWI00000004 2.00 0.00 c中成药仓库(1) 101204 2012-12-12 JHAZDA00003545_1
HWI00000004 7.00 7.00 c中成药仓库(1) 110308 2013-03-29 JHAZDA00005927_2
HWI00000004 11.00 11.00 c中成药仓库(1) 111010 2013-10-24 JHAZDA00008584_2
HWI00000004 40.00 40.00 c中成药仓库(1) 111203 2013-12-07 JHAZDA00011343_1
HWI00000020 120.00 120.00 (整)d中成药仓库(1) 111206 2013-12-18 JHAZDA00012187_4
如何将可开数量（黑体部分语句）过滤条件不显示 可开数量为0的
第二个问题 是有没办法做到每个hw只显示一行数据 但不包括 可开数量为0的 达到效果
HWI00000004 7.00 7.00 c中成药仓库(1) 110308 2013-03-29 JHAZDA00005927_2
HWI00000020 120.00 120.00 (整)d中成药仓库(1) 111206 2013-12-18 JHAZDA00012187_4
------解决方案--------------------
select a.hw,a.ljshl as '批次数量',a.ljshl-isnull((select sum(jm.shl) as shl from jzorder_mx jm
where jm.is_zx='否' and (jm.djbh like 'XSG%' or jm.djbh like 'jht%') and pici=a.pici and spid=a.spid and pihao=a.pihao and hw=a.hw),0) -isnull((select isnull(sum(shl),0) from tmp_dj_XSG212 where spid=a.spid and pihao=a.pihao and hw=b.hw and pici=a.pici),0) as '可开数量',
huowname,a.pihao,a.sxrq,a.pici
from sphwphpc a,huoweizl b,spkfk c,sphwph h
where b.beactive='是' and a.hw=b.hw and (a.spid=isnull('SPH00004414' ,''))
and a.spid=h.spid and a.pihao=h.pihao and a.hw=h.hw
and a.spid=c.spid and h.dangqzht='合格' and a.ljshl>0
and a.ljshl-isnull((select sum(jm.shl) as shl from jzorder_mx jm
where jm.is_zx='否' and (jm.djbh like 'XSG%' or jm.djbh like 'jht%') and pici=a.pici and spid=a.spid and pihao=a.pihao and hw=a.hw),0) -isnull((select isnull(sum(shl),0) from tmp_dj_XSG212 where spid=a.spid and pihao=a.pihao and hw=b.hw and pici=a.pici),0) <> 0
order by b.type ,h.sxrq
------解决方案--------------------
--第一个问题，把语句作为子查询，不就完事了么？外面套上条件<>0,且外层排序。
select * from
(select a.hw,a.ljshl as '批次数量',a.ljshl-isnull((select sum(jm.shl) as shl from jzorder_mx jm
where jm.is_zx='否' and (jm.djbh like 'XSG%' or jm.djbh like 'jht%') and pici=a.pici and spid=a.spid and pihao=a.pihao and hw=a.hw),0) -isnull((select isnull(sum(shl),0) from tmp_dj_XSG212 where spid=a.spid and pihao=a.pihao and hw=b.hw and pici=a.pici),0) as '可开数量',
huowname,a.pihao,a.sxrq,a.pici
from sphwphpc a,huoweizl b,spkfk c,sphwph h
where b.beactive='是' and a.hw=b.hw and (a.spid=isnull('SPH00004414' ,''))
and a.spid=h.spid and a.pihao=h.pihao and a.hw=h.hw
and a.spid=c.spid and h.dangqzht='合格' and a.ljshl>0) tb
where 可开数量<>0
order by type ,sxrq
------解决方案--------------------
- SQL code
-->select hw,ljshl,可开数量,huowname,pihao,sxrq,pici from( select a.hw, a.ljshl as '批次数量', a.ljshl-isnull((select sum(jm.shl) as shl from jzorder_mx jm where jm.is_zx='否' and (jm.djbh like 'XSG%' or jm.djbh like 'jht%') and pici=a.pici and spid=a.spid and pihao=a.pihao and hw=a.hw),0) -isnull((select isnull(sum(shl),0) from tmp_dj_XSG212 where spid=a.spid and pihao=a.pihao and hw=b.hw and pici=a.pici),0) as '可开数量', huowname, a.pihao, a.sxrq, a.pici, row_number() over(order by b.type ,h.sxrq) rn from sphwphpc a,huoweizl b,spkfk c,sphwph h where b.beactive='是' and a.hw=b.hw and (a.spid=isnull('SPH00004414' ,'')) and a.spid=h.spid and a.pihao=h.pihao and a.hw=h.hw and a.spid=c.spid and h.dangqzht='合格' and a.ljshl>0 and a.ljshl-isnull((select sum(jm.shl) as shl from jzorder_mx jm where jm.is_zx='否' and (jm.djbh like 'XSG%' or jm.djbh like 'jht%') and pici=a.pici and spid=a.spid and pihao=a.pihao and hw=a.hw),0) -isnull((select isnull(sum(shl),0) from tmp_dj_XSG212 where spid=a.spid and pihao=a.pihao and hw=b.hw and pici=a.pici),0)<>0 ) twhere t.rn=1order by b.type ,h.sxrq