高分悬赏

高分悬赏，求高手指教
公司内部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.000.00c中成药仓库(1)101204 2012-12-12JHAZDA00003545_1  
HWI00000004 7.007.00c中成药仓库(1)110308 2013-03-29JHAZDA00005927_2  
HWI00000004 11.0011.00c中成药仓库(1)111010 2013-10-24JHAZDA00008584_2  
HWI00000004 40.0040.00c中成药仓库(1)111203 2013-12-07JHAZDA00011343_1  
HWI00000020 120.00120.00(整)d中成药仓库(1)111206 2013-12-18JHAZDA00012187_4  
如何将可开数量（黑体部分语句）过滤条件不显示 可开数量为0的

第二个问题 是有没办法做到每个hw只显示一行数据 但不包括 可开数量为0的 达到效果
HWI00000004 7.007.00c中成药仓库(1)110308 2013-03-29JHAZDA00005927_2  
HWI00000020 120.00120.00(整)d中成药仓库(1)111206 2013-12-18JHAZDA00012187_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 
 
[解决办法]
探讨

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 j……

[解决办法]
--第二个问题。如果想改得简单。也可以这样：
with tb as (
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
)
,tb2 as (select * from tb where 可开数量<>0)
,tb3 as (
select *,ROW_NUMBER()over(partition by hw order by sxrq) rownum from tb2  
)
select hw 批次数量 可开数量 huowname pihao sxrq pici from tb3 where rownum=1
[解决办法]
探讨

我的是SQL2000 好像是不支持'ROW_NUMBER'