高分悬赏，求高手指教

Question

公司内部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   

Answer 1

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

Answer 2

--第一个问题，把语句作为子查询，不就完事了么？外面套上条件<>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

Answer 3

-->
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
 
) t
where t.rn=1
order by b.type ,h.sxrq

Answer 4

把最后面order by的b.和h.去掉，我忘了删了

Answer 5

--第二个问题。如果想改得简单。也可以这样：
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

Answer 6

spiritofdragon 大哥 通过你的指导 第一个问题已经完美解决
但是二个的话好像测试不行 服务器: 消息 156，级别 15，状态 1，行 1
在关键字 'with' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 12
第 12 行: ',' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 13
第 13 行: ',' 附近有语法错误。
服务器: 消息 195，级别 15，状态 1，行 14
'ROW_NUMBER' 不是可以识别的 函数名。
服务器: 消息 170，级别 15，状态 1，行 16
第 16 行: '可开数量' 附近有语法错误。

Answer 7

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

Answer 8

可以用临时表+identity的方法来实现declare @test table
(
id int identity(1,1),
hw varchar(30),
批次数量 int, 
可开数量 int,
huowname varchar(30),
pihao varchar(30),
scrq varchar(30),
pici varchar(30)
)
insert into @test(hw,批次数量,可开数量,huowname,pihao,sxrq,pici)
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,
b.type
      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
) t
where t.可开数量<>0
order by t.type ,t.sxrqselect * from @test
where id=1

Answer 9

把上面语句的最后一行改成select * from @test t
where not exists(select 1 from @test where t.hw=hw and t.id<id)