1.4w+
社区成员
25.3w+
社区内容
- 主页
- 基础类
- 应用实例
- 新技术前沿
求助存储过程
现在做一张日报表
如：
日期：05-1-1
型号 入库数 出库数
A材料 A 100 50
A材料 A 100 0
A材料 A+ 0 10
B材料 B 100 0
C材料 C 50 0
C材料 C -50 0
D材料 D 0 10
D材料 D 0 -10
。
。
要求进行汇总，结果如下：
日期：05-1-1
型号 入库数 出库数
A材料 A 200 50
A材料 A+ 0 10
B材料 B 100 0
。
。
也就是说只要入库数和出库数汇总后同时为零的，报表不显示此存货。
我写了一个存储过程，可是只能实现入库数和出库数同时为零的不过滤出来，而遇到象C材料和D材料这样的情况，却无法实现，请教各位高手这样的存储过程该如何实现。
如：
日期：05-1-1
型号 入库数 出库数
A材料 A 100 50
A材料 A 100 0
A材料 A+ 0 10
B材料 B 100 0
C材料 C 50 0
C材料 C -50 0
D材料 D 0 10
D材料 D 0 -10
。
。
要求进行汇总，结果如下：
日期：05-1-1
型号 入库数 出库数
A材料 A 200 50
A材料 A+ 0 10
B材料 B 100 0
。
。
也就是说只要入库数和出库数汇总后同时为零的，报表不显示此存货。
我写了一个存储过程，可是只能实现入库数和出库数同时为零的不过滤出来，而遇到象C材料和D材料这样的情况，却无法实现，请教各位高手这样的存储过程该如何实现。
...全文
rose76219 2005年03月04日
上面的发的是另一个问题，希望各位高手也能帮忙看一下，哪里不对（要求是查询出发生的业务情况）。
第一个提问的问题存储过程如下：
if exists(select name from sysobjects where name='hmyclsfc')
drop procedure hmyclsfc
go
CREATE PROCEDURE hmyclsfc
( --条件参数
@chrWhere varchar(1000)
)
AS
declare
@sdate varchar(10),@edate varchar(10),@invcode1 varchar(20),@invcode2 varchar(20),@cwhcode1 varchar(20),@cwhcode2 varchar(20),
@A1 INT,@A2 INT,@A3 INT,@A4 INT,@A5 INT,@A6 INT
--获取、分解系统传进来的变量
begin
set @sdate=convert(varchar(4),right(db_name(),4))+'-01-01'
set @edate=convert(varchar(4),right(db_name(),4))+'-12-31'
set @invcode1='0'
set @invcode2='zzzzzzzzzzzzzzz'
set @cwhcode1='0'
set @cwhcode2='zzzzz'
if ltrim(rtrim(@chrwhere))<>''
begin
set @A1=charindex('date>=',@chrwhere)+7
set @A2=charindex('date<=',@chrwhere)+7
set @A3=charindex('invcode>=',@chrwhere)+10
set @A4=charindex('invcode<=',@chrwhere)+10
set @A5=charindex('cwhcode>=',@chrwhere)+10
set @A6=charindex('cwhcode<=',@chrwhere)+10
if @A1>7 set @sdate=substring(@chrwhere,@A1,charindex('''',@chrwhere,@A1)-@A1)
if @A2>7 set @edate=substring(@chrwhere,@A2,charindex('''',@chrwhere,@A2)-@A2)
if @A3>10 set @invcode1=substring(@chrwhere,@A3,charindex('''',@chrwhere,@A3)-@A3)
if @A4>10 set @invcode2=substring(@chrwhere,@A4,charindex('''',@chrwhere,@A4)-@A4)
if @A5>10 set @cwhcode1=substring(@chrwhere,@A5,charindex('''',@chrwhere,@A5)-@A5)
if @A6>10 set @cwhcode2=substring(@chrwhere,@A6,charindex('''',@chrwhere,@A6)-@A6)
end
--建立临时表
If exists(select name from tempdb..sysobjects where name ='yclsfcb')
Drop table tempdb..yclsfcb
CREATE TABLE [tempdb]..[yclsfcb] (
[cinvcode] [varchar](20) NULL ,
[cinvname] [varchar] (30) NULL ,
[cbatch] [varchar] (20) NULL,
[cinvaddcode] [varchar] (20) NULL ,
[cinvstd] [varchar](20) NULL ,
[cinvccode] [varchar] (20) NULL ,
[cInvm_Unit] [varchar] (20) NULL,
[ibeginqtty] [float] NULL ,
[iinqtty] [float] NULL ,
[ioutqtty] [float] NULL ,
[disnum] [float] NULL
) ON [PRIMARY]
----期初数
INSERT INTO [tempdb].[dbo].[yclsfcb]([cinvcode], [cinvname],[cbatch], [cinvaddcode], [cinvstd], [cinvccode],[cInvm_Unit], [ibeginqtty], [iinqtty], [ioutqtty], [disnum])
SELECT DISTINCT I.cInvCode, MAX(I.cInvName) AS cInvName,Rs.cbatch, MAX(I.cInvAddCode) AS cInvAddCode,
MAX(I.cInvStd) AS cInvStd, MAX(IC.cInvCCode) AS cInvCCode,max(CU_G.ccomunitname),
SUM( CASE WHEN R.bISSTQc = 1 Or
R.dDate < @sdate THEN (CASE WHEN R.bRdFlag = 1 THEN ISNULL(Rs.iQuantity,0) ELSE
-ISNULL(Rs.iQuantity,0) END) ELSE 0 END) AS iBeginQtty,0 AS iInQtty,0 AS iOutQtty,0 as disnum
FROM dbo.Inventory I LEFT
OUTER JOIN dbo.ComputationUnit CU_G ON CU_G.cComUnitCode=I.cComUnitCode
INNER JOIN dbo.RdRecords Rs ON I.cInvCode = Rs.cInvCode INNER JOIN dbo.RdRecord R ON Rs.ID = R.ID
LEFT OUTER JOIN dbo.InventoryClass IC ON I.cInvCCode = IC.cInvCCode LEFT OUTER JOIN dbo.Warehouse W
ON R.cWhCode = W.cWhCode LEFT OUTER JOIN dbo.ComputationUnit CU_M ON I.cComUnitCode = CU_M.cComunitCode
LEFT OUTER JOIN dbo.ComputationUnit CU_A ON I.cAssComUnitCode = CU_A.cComunitCode
WHERE ((R.dDate < '2004-01-01' And IsNull(R.bIsSTQc,0) = 1)
Or (R.dDate >= '2004-01-01'
And IsNull(R.bPUFirst,0) = 0
And IsNull(R.bIAFirst,0) = 0
And (not (R.cBusType = '假退料'and R.cVouchType = 11))
)
) and (I.cinvcode>=@invcode1 and I.cinvcode<=@invcode2) and (W.cWhCode>=@cwhcode1 and W.cWhCode<=@cwhcode2)
GROUP BY I.cInvCode,Rs.cbatch
HAVING abs(SUM( CASE WHEN R.bISSTQc = 1 or
R.dDate < @sdate THEN (CASE WHEN R.bRdFlag = 1 THEN ISNULL(Rs.iQuantity,0) ELSE -
ISNULL(Rs.iQuantity,0) END) ELSE 0 END))>0.001 OR SUM(CASE WHEN R.bISSTQc = 0 And R.dDate >= @sdate
AND R.dDate <= @edate THEN 1 ELSE 0 END)> 0 Order By I.cInvCode
---收发存出入库
insert into [tempdb].[dbo].[yclsfcb] ( cinvcode,cinvname,cbatch,cinvaddcode,cinvccode,cInvm_Unit,ibeginqtty,iinqtty,ioutqtty,disnum)
SELECT DISTINCT Rs.cInvCode,max(I.cinvname),Rs.cbatch,max(cinvaddcode),max(cinvccode),max(CU_G.ccomunitname),0,
SUM(CASE WHEN R.bRdFlag = 1 THEN
ISNULL(Rs.iQuantity,0) ELSE NULL END) AS iInQtty, sum( CASE WHEN R.bRdFlag = 0 THEN
ISNULL(Rs.iQuantity,0) ELSE NULL END) AS iOutQtty,0
FROM dbo.Inventory I LEFT OUTER JOIN dbo.ComputationUnit CU_G ON CU_G.cComUnitCode=I.cComUnitCode
RIGHT OUTER JOIN dbo.Vendor V RIGHT OUTER JOIN dbo.Customer C RIGHT OUTER JOIN
dbo.RdRecord R LEFT OUTER JOIN dbo.Person P1 ON R.cPersonCode = P1.cPersonCode LEFT OUTER JOIN
dbo.Department DP ON R.cDepCode = DP.cDepCode LEFT OUTER JOIN dbo.Warehouse W ON R.cWhCode = W.cWhCode
LEFT OUTER JOIN dbo.RdRecords Rs ON R.ID = Rs.ID LEFT OUTER JOIN dbo.VouchType VT ON
R.cVouchType = VT.cVouchType LEFT OUTER JOIN dbo.Rd_Style Rd ON R.cRdCode = Rd.cRdCode ON
C.cCusCode = R.cCusCode ON V.cVenCode = R.cVenCode ON I.cInvCode = Rs.cInvCode left outer join
Person CheckPerson on CheckPerson.cPersonCode = Rs.cCheckPersonCode WHERE ((R.dDate < '2004-01-01'
And IsNull(R.bIsSTQc,0) = 1)
Or (R.dDate >= '2004-01-01'
And IsNull(R.bPUFirst,0) = 0
And IsNull(R.bIAFirst,0) = 0
And (not (R.cBusType = '假退料'and R.cVouchType = 11))
)
) AND IsNull(R.bIsStQc,0) = 0 AND( R.dDate >= @sdate AND
R.dDate <= @edate) and (Rs.cinvcode>=@invcode1 and Rs.cinvcode<=@invcode2) and (W.cWhCode>=@cwhcode1 and W.cWhCode<=@cwhcode2)
group by rs.cinvcode,Rs.cbatch
ORDER BY Rs.cinvcode
---到货未入库
insert into [tempdb].[dbo].[yclsfcb] ( cinvcode,cinvname,cbatch,cinvaddcode,cinvccode,cInvm_Unit,disnum)
select pu2.cinvcode as cinvcode,inv.cinvname ,pu2.cbatch,inv.cinvaddcode,inv.cinvccode,comp_unit.ccomunitname,
sum(pu2.iquantity)-sum(pu2.fvalidinquan) as disnum from PU_ArrivalVouch pu1
inner join PU_ArrivalVouchs pu2 on pu1.id=pu2.id
inner join inventory inv
on inv.cinvcode=pu2.cinvcode inner join ComputationUnit comp_unit
on comp_unit.cComUnitCode=inv.cComUnitCode inner join ComputationGroup comp_group
on comp_unit.cGroupCode=comp_group.cGroupCode right outer join warehouse W on pu2.cwhcode=W.cwhcode
where (inv.cinvcode>=@invcode1 and inv.cinvcode<=@invcode2) and (W.cWhCode>=@cwhcode1 and W.cWhCode<=@cwhcode2)
group by pu2.cinvcode,inv.cinvname ,pu2.cbatch,inv.cinvaddcode,inv.cinvccode,comp_unit.ccomunitname
having (sum(pu2.iquantity)-sum(pu2.fvalidinquan))>0
order by pu2.cinvcode
--select * from tempdb..yclsfcb where cinvaddcode like 'FM%' order by cinvaddcode
INSERT INTO [tempdb].[dbo].[yclsfcb]([cinvcode], [cinvname],[cbatch], [cinvaddcode], [cinvstd], [cinvccode],[cInvm_Unit], [ibeginqtty], [iinqtty], [ioutqtty], [disnum])
select distinct cinvcode, max(cinvname),'合计数', max(cinvaddcode), null, max(cinvccode),max(cInvm_Unit), sum(isnull(ibeginqtty,0)), sum(isnull(iinqtty,0)), sum(isnull(ioutqtty,0)), sum(isnull(disnum,0))
from tempdb..yclsfcb where cinvaddcode like 'FM%'
group by cinvcode
order by cinvcode
end
第一个提问的问题存储过程如下：
if exists(select name from sysobjects where name='hmyclsfc')
drop procedure hmyclsfc
go
CREATE PROCEDURE hmyclsfc
( --条件参数
@chrWhere varchar(1000)
)
AS
declare
@sdate varchar(10),@edate varchar(10),@invcode1 varchar(20),@invcode2 varchar(20),@cwhcode1 varchar(20),@cwhcode2 varchar(20),
@A1 INT,@A2 INT,@A3 INT,@A4 INT,@A5 INT,@A6 INT
--获取、分解系统传进来的变量
begin
set @sdate=convert(varchar(4),right(db_name(),4))+'-01-01'
set @edate=convert(varchar(4),right(db_name(),4))+'-12-31'
set @invcode1='0'
set @invcode2='zzzzzzzzzzzzzzz'
set @cwhcode1='0'
set @cwhcode2='zzzzz'
if ltrim(rtrim(@chrwhere))<>''
begin
set @A1=charindex('date>=',@chrwhere)+7
set @A2=charindex('date<=',@chrwhere)+7
set @A3=charindex('invcode>=',@chrwhere)+10
set @A4=charindex('invcode<=',@chrwhere)+10
set @A5=charindex('cwhcode>=',@chrwhere)+10
set @A6=charindex('cwhcode<=',@chrwhere)+10
if @A1>7 set @sdate=substring(@chrwhere,@A1,charindex('''',@chrwhere,@A1)-@A1)
if @A2>7 set @edate=substring(@chrwhere,@A2,charindex('''',@chrwhere,@A2)-@A2)
if @A3>10 set @invcode1=substring(@chrwhere,@A3,charindex('''',@chrwhere,@A3)-@A3)
if @A4>10 set @invcode2=substring(@chrwhere,@A4,charindex('''',@chrwhere,@A4)-@A4)
if @A5>10 set @cwhcode1=substring(@chrwhere,@A5,charindex('''',@chrwhere,@A5)-@A5)
if @A6>10 set @cwhcode2=substring(@chrwhere,@A6,charindex('''',@chrwhere,@A6)-@A6)
end
--建立临时表
If exists(select name from tempdb..sysobjects where name ='yclsfcb')
Drop table tempdb..yclsfcb
CREATE TABLE [tempdb]..[yclsfcb] (
[cinvcode] [varchar](20) NULL ,
[cinvname] [varchar] (30) NULL ,
[cbatch] [varchar] (20) NULL,
[cinvaddcode] [varchar] (20) NULL ,
[cinvstd] [varchar](20) NULL ,
[cinvccode] [varchar] (20) NULL ,
[cInvm_Unit] [varchar] (20) NULL,
[ibeginqtty] [float] NULL ,
[iinqtty] [float] NULL ,
[ioutqtty] [float] NULL ,
[disnum] [float] NULL
) ON [PRIMARY]
----期初数
INSERT INTO [tempdb].[dbo].[yclsfcb]([cinvcode], [cinvname],[cbatch], [cinvaddcode], [cinvstd], [cinvccode],[cInvm_Unit], [ibeginqtty], [iinqtty], [ioutqtty], [disnum])
SELECT DISTINCT I.cInvCode, MAX(I.cInvName) AS cInvName,Rs.cbatch, MAX(I.cInvAddCode) AS cInvAddCode,
MAX(I.cInvStd) AS cInvStd, MAX(IC.cInvCCode) AS cInvCCode,max(CU_G.ccomunitname),
SUM( CASE WHEN R.bISSTQc = 1 Or
R.dDate < @sdate THEN (CASE WHEN R.bRdFlag = 1 THEN ISNULL(Rs.iQuantity,0) ELSE
-ISNULL(Rs.iQuantity,0) END) ELSE 0 END) AS iBeginQtty,0 AS iInQtty,0 AS iOutQtty,0 as disnum
FROM dbo.Inventory I LEFT
OUTER JOIN dbo.ComputationUnit CU_G ON CU_G.cComUnitCode=I.cComUnitCode
INNER JOIN dbo.RdRecords Rs ON I.cInvCode = Rs.cInvCode INNER JOIN dbo.RdRecord R ON Rs.ID = R.ID
LEFT OUTER JOIN dbo.InventoryClass IC ON I.cInvCCode = IC.cInvCCode LEFT OUTER JOIN dbo.Warehouse W
ON R.cWhCode = W.cWhCode LEFT OUTER JOIN dbo.ComputationUnit CU_M ON I.cComUnitCode = CU_M.cComunitCode
LEFT OUTER JOIN dbo.ComputationUnit CU_A ON I.cAssComUnitCode = CU_A.cComunitCode
WHERE ((R.dDate < '2004-01-01' And IsNull(R.bIsSTQc,0) = 1)
Or (R.dDate >= '2004-01-01'
And IsNull(R.bPUFirst,0) = 0
And IsNull(R.bIAFirst,0) = 0
And (not (R.cBusType = '假退料'and R.cVouchType = 11))
)
) and (I.cinvcode>=@invcode1 and I.cinvcode<=@invcode2) and (W.cWhCode>=@cwhcode1 and W.cWhCode<=@cwhcode2)
GROUP BY I.cInvCode,Rs.cbatch
HAVING abs(SUM( CASE WHEN R.bISSTQc = 1 or
R.dDate < @sdate THEN (CASE WHEN R.bRdFlag = 1 THEN ISNULL(Rs.iQuantity,0) ELSE -
ISNULL(Rs.iQuantity,0) END) ELSE 0 END))>0.001 OR SUM(CASE WHEN R.bISSTQc = 0 And R.dDate >= @sdate
AND R.dDate <= @edate THEN 1 ELSE 0 END)> 0 Order By I.cInvCode
---收发存出入库
insert into [tempdb].[dbo].[yclsfcb] ( cinvcode,cinvname,cbatch,cinvaddcode,cinvccode,cInvm_Unit,ibeginqtty,iinqtty,ioutqtty,disnum)
SELECT DISTINCT Rs.cInvCode,max(I.cinvname),Rs.cbatch,max(cinvaddcode),max(cinvccode),max(CU_G.ccomunitname),0,
SUM(CASE WHEN R.bRdFlag = 1 THEN
ISNULL(Rs.iQuantity,0) ELSE NULL END) AS iInQtty, sum( CASE WHEN R.bRdFlag = 0 THEN
ISNULL(Rs.iQuantity,0) ELSE NULL END) AS iOutQtty,0
FROM dbo.Inventory I LEFT OUTER JOIN dbo.ComputationUnit CU_G ON CU_G.cComUnitCode=I.cComUnitCode
RIGHT OUTER JOIN dbo.Vendor V RIGHT OUTER JOIN dbo.Customer C RIGHT OUTER JOIN
dbo.RdRecord R LEFT OUTER JOIN dbo.Person P1 ON R.cPersonCode = P1.cPersonCode LEFT OUTER JOIN
dbo.Department DP ON R.cDepCode = DP.cDepCode LEFT OUTER JOIN dbo.Warehouse W ON R.cWhCode = W.cWhCode
LEFT OUTER JOIN dbo.RdRecords Rs ON R.ID = Rs.ID LEFT OUTER JOIN dbo.VouchType VT ON
R.cVouchType = VT.cVouchType LEFT OUTER JOIN dbo.Rd_Style Rd ON R.cRdCode = Rd.cRdCode ON
C.cCusCode = R.cCusCode ON V.cVenCode = R.cVenCode ON I.cInvCode = Rs.cInvCode left outer join
Person CheckPerson on CheckPerson.cPersonCode = Rs.cCheckPersonCode WHERE ((R.dDate < '2004-01-01'
And IsNull(R.bIsSTQc,0) = 1)
Or (R.dDate >= '2004-01-01'
And IsNull(R.bPUFirst,0) = 0
And IsNull(R.bIAFirst,0) = 0
And (not (R.cBusType = '假退料'and R.cVouchType = 11))
)
) AND IsNull(R.bIsStQc,0) = 0 AND( R.dDate >= @sdate AND
R.dDate <= @edate) and (Rs.cinvcode>=@invcode1 and Rs.cinvcode<=@invcode2) and (W.cWhCode>=@cwhcode1 and W.cWhCode<=@cwhcode2)
group by rs.cinvcode,Rs.cbatch
ORDER BY Rs.cinvcode
---到货未入库
insert into [tempdb].[dbo].[yclsfcb] ( cinvcode,cinvname,cbatch,cinvaddcode,cinvccode,cInvm_Unit,disnum)
select pu2.cinvcode as cinvcode,inv.cinvname ,pu2.cbatch,inv.cinvaddcode,inv.cinvccode,comp_unit.ccomunitname,
sum(pu2.iquantity)-sum(pu2.fvalidinquan) as disnum from PU_ArrivalVouch pu1
inner join PU_ArrivalVouchs pu2 on pu1.id=pu2.id
inner join inventory inv
on inv.cinvcode=pu2.cinvcode inner join ComputationUnit comp_unit
on comp_unit.cComUnitCode=inv.cComUnitCode inner join ComputationGroup comp_group
on comp_unit.cGroupCode=comp_group.cGroupCode right outer join warehouse W on pu2.cwhcode=W.cwhcode
where (inv.cinvcode>=@invcode1 and inv.cinvcode<=@invcode2) and (W.cWhCode>=@cwhcode1 and W.cWhCode<=@cwhcode2)
group by pu2.cinvcode,inv.cinvname ,pu2.cbatch,inv.cinvaddcode,inv.cinvccode,comp_unit.ccomunitname
having (sum(pu2.iquantity)-sum(pu2.fvalidinquan))>0
order by pu2.cinvcode
--select * from tempdb..yclsfcb where cinvaddcode like 'FM%' order by cinvaddcode
INSERT INTO [tempdb].[dbo].[yclsfcb]([cinvcode], [cinvname],[cbatch], [cinvaddcode], [cinvstd], [cinvccode],[cInvm_Unit], [ibeginqtty], [iinqtty], [ioutqtty], [disnum])
select distinct cinvcode, max(cinvname),'合计数', max(cinvaddcode), null, max(cinvccode),max(cInvm_Unit), sum(isnull(ibeginqtty,0)), sum(isnull(iinqtty,0)), sum(isnull(ioutqtty,0)), sum(isnull(disnum,0))
from tempdb..yclsfcb where cinvaddcode like 'FM%'
group by cinvcode
order by cinvcode
end
rose76219 2005年03月04日
续：
if exists(select 1 from tempdb..sysobjects where name='sp_ywmxb' And xtype='U')
Drop table tempdb..sp_ywmxb
SELECT sum([iMonth]) as [iMonth],sum([iDay]) as [iDay],max([cPzNum]) as [cPzNum],[cDwCode],
max([cDwName]) as [cDwName],max([cDeptCode]) as [cDeptCode],max([cDepName]) as [cDepName],
max([cPerson]) as [cPerson],max([cPersonName]) as [cPersonName],[cInvCode],max([cInvName]) as [cInvName],
max([cDwCCode]) as [cDwCCode],max([cDWCName]) as [cDWCName],max([cDwDCode]) as [cDwDCode],
max([cDCName]) as [cDCName],max([cHDwCode]) as [cHDwCode],max([cHDwName]) as [cHDwName],
max([cHDptCode]) as [cHDptCode],max([cHDepName]) as [cHDepName],max([cHPsnCode]) as [cHPsnCode],
max([cHPersonName]) as [cHPersonName],max([cInvCCode]) as [cInvCCode],max([cInvCName]) as [cInvCName],
max([cCode]) as [cCode],max([cCode_Name]) as [cCode_Name],max([cItem_Class]) as [cItem_Class],
max([cItem_Name]) as [cItem_Name],max([cItemCode]) as [cItemCode],max([cItemName]) as [cItemName],
max([dgst]) as [dgst],[vtype],[vid],[cCancelNo],max([cpzid]) as [cpzid],max([exchname]) as [exchname],
sum([price]) as [price],sum([Rate]) as [Rate],sum([jf_s]) as [jf_s],sum([jf_f]) as [jf_f],
sum([jf]) as [jf],sum([df_s]) as [df_s],sum([df_f]) as [df_f],sum([df]) as [df],sum([jf_s2]) as [jf_s2],
sum([jf_f2]) as [jf_f2],sum([jf2]) as [jf2],sum([df_s2]) as [df_s2],sum([df_f2]) as [df_f2],
sum([df2]) as [df2],
sum(case left(cinvcode,2) when '21'then [ye_s] else 0 end) as xia,
sum(case left(cinvcode,2) when '22'then [ye_s] else 0 end) as man,
sum(case left(cinvcode,2) when '23'then [ye_s] else 0 end) as bie,
sum(case left(cinvcode,2) when '24'then [ye_s] else 0 end) as wa,
sum(case left(cinvcode,2) when '25'then [ye_s] else 0 end) as yu,
sum(case left(cinvcode,3) when '291'then [ye_s] else 0 end) +sum(case left(cinvcode,3) when '292'then [ye_s] else 0 end) as qt,
sum(case left(cinvcode,3) when '299'then [ye] else 0 end) as yun,
sum([ye_s]) as [ye_s],
sum([ye_f]) as [ye_f],sum([ye]) as [ye],[csysid],
sum([Auto_Id]) as [Auto_Id],max([dRDate]) as [dRDate] Into tempdb..sp_ywmxb
FROM tempdb..sp_ywmxzb
where isnull(dgst,0)<>'核销'
GROUP BY [cDwCode], [cInvCode], [vtype], [vid], [cCancelNo], [csysid]
以上运行是没有问题，可是不知为何却将期初数（ye_s)也过滤出来。我条件已经写出来了呀。
if exists(select 1 from tempdb..sysobjects where name='sp_ywmxb' And xtype='U')
Drop table tempdb..sp_ywmxb
SELECT sum([iMonth]) as [iMonth],sum([iDay]) as [iDay],max([cPzNum]) as [cPzNum],[cDwCode],
max([cDwName]) as [cDwName],max([cDeptCode]) as [cDeptCode],max([cDepName]) as [cDepName],
max([cPerson]) as [cPerson],max([cPersonName]) as [cPersonName],[cInvCode],max([cInvName]) as [cInvName],
max([cDwCCode]) as [cDwCCode],max([cDWCName]) as [cDWCName],max([cDwDCode]) as [cDwDCode],
max([cDCName]) as [cDCName],max([cHDwCode]) as [cHDwCode],max([cHDwName]) as [cHDwName],
max([cHDptCode]) as [cHDptCode],max([cHDepName]) as [cHDepName],max([cHPsnCode]) as [cHPsnCode],
max([cHPersonName]) as [cHPersonName],max([cInvCCode]) as [cInvCCode],max([cInvCName]) as [cInvCName],
max([cCode]) as [cCode],max([cCode_Name]) as [cCode_Name],max([cItem_Class]) as [cItem_Class],
max([cItem_Name]) as [cItem_Name],max([cItemCode]) as [cItemCode],max([cItemName]) as [cItemName],
max([dgst]) as [dgst],[vtype],[vid],[cCancelNo],max([cpzid]) as [cpzid],max([exchname]) as [exchname],
sum([price]) as [price],sum([Rate]) as [Rate],sum([jf_s]) as [jf_s],sum([jf_f]) as [jf_f],
sum([jf]) as [jf],sum([df_s]) as [df_s],sum([df_f]) as [df_f],sum([df]) as [df],sum([jf_s2]) as [jf_s2],
sum([jf_f2]) as [jf_f2],sum([jf2]) as [jf2],sum([df_s2]) as [df_s2],sum([df_f2]) as [df_f2],
sum([df2]) as [df2],
sum(case left(cinvcode,2) when '21'then [ye_s] else 0 end) as xia,
sum(case left(cinvcode,2) when '22'then [ye_s] else 0 end) as man,
sum(case left(cinvcode,2) when '23'then [ye_s] else 0 end) as bie,
sum(case left(cinvcode,2) when '24'then [ye_s] else 0 end) as wa,
sum(case left(cinvcode,2) when '25'then [ye_s] else 0 end) as yu,
sum(case left(cinvcode,3) when '291'then [ye_s] else 0 end) +sum(case left(cinvcode,3) when '292'then [ye_s] else 0 end) as qt,
sum(case left(cinvcode,3) when '299'then [ye] else 0 end) as yun,
sum([ye_s]) as [ye_s],
sum([ye_f]) as [ye_f],sum([ye]) as [ye],[csysid],
sum([Auto_Id]) as [Auto_Id],max([dRDate]) as [dRDate] Into tempdb..sp_ywmxb
FROM tempdb..sp_ywmxzb
where isnull(dgst,0)<>'核销'
GROUP BY [cDwCode], [cInvCode], [vtype], [vid], [cCancelNo], [csysid]
以上运行是没有问题，可是不知为何却将期初数（ye_s)也过滤出来。我条件已经写出来了呀。
rose76219 2005年03月04日
我是这样写的储存过程：
if exists(select name from sysobjects where name='sp_ywmxb')
drop procedure sp_ywmxb
go
CREATE PROCEDURE sp_ywmxb
( --条件参数
@chrWhere varchar(1000)
)
AS
declare @cdwcode1 varchar(10),@cdwcode2 varchar(10),@sdate datetime,@edate datetime,
@A1 int,@A2 int,@A3 int,@A4 int
begin
set @sdate=convert(varchar(4),right(db_name(),4))+'-01-01'
set @edate=convert(varchar(4),right(db_name(),4))+'-12-31'
set @cdwcode1='0'
set @cdwcode2='zzzzzzzzz'
end
if ltrim(rtrim(@chrwhere))<>''
begin
set @A1=charindex('date<=',@chrwhere)+7
set @A2=charindex('date>=',@chrwhere)+7
set @A3=charindex('cdwcode>=',@chrwhere)+10
set @A4=charindex('cdwcode<=',@chrwhere)+10
if @A1>7 set @sdate=substring(@chrwhere,@A1,charindex('''',@chrwhere,@A1)-@A1)
if @A2>7 set @edate=substring(@chrwhere,@A2,charindex('''',@chrwhere,@A2)-@A2)
if @A3>10 set @cdwcode1=substring(@chrwhere,@A3,charindex('''',@chrwhere,@A3)-@A3)
if @A4>10 set @cdwcode2=substring(@chrwhere,@A4,charindex('''',@chrwhere,@A4)-@A4)
end
if exists(select 1 from tempdb..sysobjects where name='sp_ywmxzb' And xtype='U')
drop table tempdb..sp_ywmxzb
Create Table tempdb..sp_ywmxzb (iMonth tinyint,iDay tinyint,cDwCode varchar(20),
cDwName varchar(120),cDeptCode varchar(20),cDepName varchar(120),cPerson varchar(20),
cPersonName varchar(120), cInvCode varchar(20), cInvName varchar(120),cDwCCode varchar(20),
cDWCName varchar(120),cDwDCode varchar(20),cDCName varchar(120),cHDwCode varchar(20),
cHDwName varchar(120),cHDptCode varchar(20),cHDepName varchar(120),cHPsnCode varchar(20),
cHPersonName varchar(120),cInvCCode varchar(20),cInvCName varchar(120),cCode varchar(20),
cCode_Name varchar(120),cItem_Class varchar(2),cItem_Name varchar(20),cItemCode varchar(20),
cItemName varchar(60),cPzNum varchar(15),dgst varchar(60),vtype varchar(20),vid varchar(30),
cCancelNo varchar(40),cpzid varchar(30),exchname varchar(8),price float,Rate Float,jf_f money,
jf_s float,jf money,df_f money,df_s float,df money,jf_f2 money,jf_s2 float,jf2 money,df_f2 money,
df_s2 float,df2 money,ye_f money,ye_s float,ye money,csysid varchar(2),Auto_Id int,dRDate datetime)
insert into tempdb..sp_ywmxzb(imonth,iday,cDwCode,cDeptCode,cPerson,cInvCode,cCode,cDwName,
cDWCCode,cDWDCode,cHDWCode,cHDptCode,cHPsnCode,cItem_Class,cItemCode,cItemName,dgst,vtype,vid,cpzid,
jf_f,jf_s,jf,df_f,df_s,df,exchname,price,rate,csysid) select null as imonth,null as iDay,
max(cDwCode) as cDwCode,max(cDeptCode) as cDeptCode,max(cPerson) as cPerson,max(cInvCode) as cInvCode,
max(cCode) as cCode,max(cDwName) as cDwName,max(cDWCCode) as cDWCCode,max(cDWDCode) as cDWDCode,
max(cHDWCode) as cHDWCode,max(cHDptCode) as cHDWCode,max(cHPsnCode) as cHPsnCode,
max(cItem_Class) as cItem_Class,max(cItemCode) as cItemCode,max(cItemName) as cItemName,
'期初余额' as dgst,null as vtype,null as vid, null as cpzid,sum(case when cexch_name='人民币' then 0
else idamount_f end) as jf_f,sum(idamount_s) as jf_s,sum(idamount) as jf,sum(case when
cexch_name='人民币' then 0 else icamount_f end) as df_f,sum(icamount_s) as df_s,sum(icamount)
as df,null as exchname,0 as price,0 as rate,'AR' From Ap_DetailCust_V where dRegDate<@sdate And cFlag='AR'
and iflag<3 And cDwCode>=@cdwcode1 And cDwCode<=@cdwcode2 Group by cdwcode,cinvcode
Having sum(idamount - icamount) <> 0 Or sum(idamount_f - icamount_f) <> 0
Order By cdwcode,cinvcode
insert into tempdb..sp_ywmxzb(imonth,iday,cDwCode,cDeptCode,cPerson,cInvCode,cCode,cDwName,
cDWCCode,cDWDCode,cHDWCode,cHDptCode,cHPsnCode,cItem_Class,cItemCode,cItemName,cPzNum,dgst , vtype,
vid, cCancelNo, cpzid, jf_f,jf_s,jf,df_f,df_s,df,exchname,price,rate,csysid,Auto_Id,dRdate)
select month(min(dregdate)) as imonth,Day(min(dregdate)) as iDay,max(cDwCode) as cDwCode,
max(cDeptCode) as cDeptCode,max(cPerson) as cPerson,max(cInvCode) as cInvCode,max(cCode) as cCode,
max(cDwName) as cDwName,max(cDWCCode) as cDWCCode,max(cDWDCode) as cDWDCode,max(cHDWCode) as cHDWCode,
max(cHDptCode) as cHDWCode,max(cHPsnCode) as cHPsnCode,max(cItem_Class) as cItem_Class,
max(cItemCode) as cItemCode,max(cItemName) as cItemName,
Max(cGlsign+'-'+REPLICATE(0,4-len(iGLno_id))+convert(varchar,iGLno_id)) as cPzNum,
max(case when ccovouchtype=cprocstyle Or iFlag=1 Or cProcStyle='XJ' then cdigest
else cprocname end) as dgst,max(case when ccovouchtype=cprocstyle or iFlag=1 Or cProcStyle='XJ'
then cTypeName else cProcName end) as vtype,max(case when ccovouchtype=cprocstyle or iFlag=1 Or
cProcStyle='XJ' then cCoVouchId else cCancelNo end) as vid,max(cCancelNo) as ccancelno, max(cpzid)
as cpzid,sum(case when cexch_name='人民币' then 0 else idamount_f end) as jf_f,sum(idamount_s) as jf_s,
sum(idamount) as jf,sum(case when cexch_name='人民币' then 0 else icamount_f end) as df_f,
sum(icamount_s) as df_s,sum(icamount) as df,max(cexch_name) as exchname,max(iprice) as price,
max(iexchrate) As Rate,'AR',Max(Auto_Id),min(dRegDate) From Ap_DetailCust_V where dRegDate>=@sdate and
dRegDate<=@edate And cFlag='AR' And cDwCode>=@cdwcode1 And cDwCode<=@cdwcode2 and iflag<3 Group by cdwcode,
cinvcode,ccancelno,cExch_Name,iFlag having sum(idamount)<>0 or sum(icamount) <> 0 Or sum(idamount_f)<>0
or sum(icamount_f) <> 0 Order By cdwcode,cinvcode,MAX(Auto_id)
update tempdb..sp_ywmxzb set jf_f=(case when jf_f is null then 0 else jf_f end),
jf_s=(case when jf_s is null then 0 else jf_s end),jf=(case when jf is null then 0 else jf end),
df_f=(case when df_f is null then 0 else df_f end),df_s=(case when df_s is null then 0 else df_s end),
df=(case when df is null then 0 else df end),jf_f2=(case when jf_f2 is null then 0 else jf_f2 end),
jf_s2=(case when jf_s2 is null then 0 else jf_s2 end),jf2=(case when jf2 is null then 0 else jf2 end),
df_f2=(case when df_f2 is null then 0 else df_f2 end),df_s2=(case when df_s2 is null then 0 else df_s2
end),df2=(case when df2 is null then 0 else df2 end)
Update tempdb..sp_ywmxzb set ye_f=jf_f-df_f-jf_f2+df_f2,ye_s=jf_s-df_s-jf_s2+df_s2,
ye=jf-df-jf2+df2
delete tempdb..sp_ywmxzb Where jf_f=0 and jf=0 and df_f=0 and df=0 and jf_f2=0 and jf2=0
and df_f2=0 and df2=0
Update tempdb..sp_ywmxzb set jf_f=null,jf_s=null,jf=null,df_f=null,df_s=null,df=null,jf_f2=null,
jf_s2=null,jf2=null,df_f2=null,df_s2=null,df2=null,csysid=Null where dgst='期初余额'
Update tempdb..sp_ywmxzb set cInvName=Inventory.cInvName From Inventory
Where tempdb..sp_ywmxzb.cInvcode=Inventory.cInvCode
if exists(select name from sysobjects where name='sp_ywmxb')
drop procedure sp_ywmxb
go
CREATE PROCEDURE sp_ywmxb
( --条件参数
@chrWhere varchar(1000)
)
AS
declare @cdwcode1 varchar(10),@cdwcode2 varchar(10),@sdate datetime,@edate datetime,
@A1 int,@A2 int,@A3 int,@A4 int
begin
set @sdate=convert(varchar(4),right(db_name(),4))+'-01-01'
set @edate=convert(varchar(4),right(db_name(),4))+'-12-31'
set @cdwcode1='0'
set @cdwcode2='zzzzzzzzz'
end
if ltrim(rtrim(@chrwhere))<>''
begin
set @A1=charindex('date<=',@chrwhere)+7
set @A2=charindex('date>=',@chrwhere)+7
set @A3=charindex('cdwcode>=',@chrwhere)+10
set @A4=charindex('cdwcode<=',@chrwhere)+10
if @A1>7 set @sdate=substring(@chrwhere,@A1,charindex('''',@chrwhere,@A1)-@A1)
if @A2>7 set @edate=substring(@chrwhere,@A2,charindex('''',@chrwhere,@A2)-@A2)
if @A3>10 set @cdwcode1=substring(@chrwhere,@A3,charindex('''',@chrwhere,@A3)-@A3)
if @A4>10 set @cdwcode2=substring(@chrwhere,@A4,charindex('''',@chrwhere,@A4)-@A4)
end
if exists(select 1 from tempdb..sysobjects where name='sp_ywmxzb' And xtype='U')
drop table tempdb..sp_ywmxzb
Create Table tempdb..sp_ywmxzb (iMonth tinyint,iDay tinyint,cDwCode varchar(20),
cDwName varchar(120),cDeptCode varchar(20),cDepName varchar(120),cPerson varchar(20),
cPersonName varchar(120), cInvCode varchar(20), cInvName varchar(120),cDwCCode varchar(20),
cDWCName varchar(120),cDwDCode varchar(20),cDCName varchar(120),cHDwCode varchar(20),
cHDwName varchar(120),cHDptCode varchar(20),cHDepName varchar(120),cHPsnCode varchar(20),
cHPersonName varchar(120),cInvCCode varchar(20),cInvCName varchar(120),cCode varchar(20),
cCode_Name varchar(120),cItem_Class varchar(2),cItem_Name varchar(20),cItemCode varchar(20),
cItemName varchar(60),cPzNum varchar(15),dgst varchar(60),vtype varchar(20),vid varchar(30),
cCancelNo varchar(40),cpzid varchar(30),exchname varchar(8),price float,Rate Float,jf_f money,
jf_s float,jf money,df_f money,df_s float,df money,jf_f2 money,jf_s2 float,jf2 money,df_f2 money,
df_s2 float,df2 money,ye_f money,ye_s float,ye money,csysid varchar(2),Auto_Id int,dRDate datetime)
insert into tempdb..sp_ywmxzb(imonth,iday,cDwCode,cDeptCode,cPerson,cInvCode,cCode,cDwName,
cDWCCode,cDWDCode,cHDWCode,cHDptCode,cHPsnCode,cItem_Class,cItemCode,cItemName,dgst,vtype,vid,cpzid,
jf_f,jf_s,jf,df_f,df_s,df,exchname,price,rate,csysid) select null as imonth,null as iDay,
max(cDwCode) as cDwCode,max(cDeptCode) as cDeptCode,max(cPerson) as cPerson,max(cInvCode) as cInvCode,
max(cCode) as cCode,max(cDwName) as cDwName,max(cDWCCode) as cDWCCode,max(cDWDCode) as cDWDCode,
max(cHDWCode) as cHDWCode,max(cHDptCode) as cHDWCode,max(cHPsnCode) as cHPsnCode,
max(cItem_Class) as cItem_Class,max(cItemCode) as cItemCode,max(cItemName) as cItemName,
'期初余额' as dgst,null as vtype,null as vid, null as cpzid,sum(case when cexch_name='人民币' then 0
else idamount_f end) as jf_f,sum(idamount_s) as jf_s,sum(idamount) as jf,sum(case when
cexch_name='人民币' then 0 else icamount_f end) as df_f,sum(icamount_s) as df_s,sum(icamount)
as df,null as exchname,0 as price,0 as rate,'AR' From Ap_DetailCust_V where dRegDate<@sdate And cFlag='AR'
and iflag<3 And cDwCode>=@cdwcode1 And cDwCode<=@cdwcode2 Group by cdwcode,cinvcode
Having sum(idamount - icamount) <> 0 Or sum(idamount_f - icamount_f) <> 0
Order By cdwcode,cinvcode
insert into tempdb..sp_ywmxzb(imonth,iday,cDwCode,cDeptCode,cPerson,cInvCode,cCode,cDwName,
cDWCCode,cDWDCode,cHDWCode,cHDptCode,cHPsnCode,cItem_Class,cItemCode,cItemName,cPzNum,dgst , vtype,
vid, cCancelNo, cpzid, jf_f,jf_s,jf,df_f,df_s,df,exchname,price,rate,csysid,Auto_Id,dRdate)
select month(min(dregdate)) as imonth,Day(min(dregdate)) as iDay,max(cDwCode) as cDwCode,
max(cDeptCode) as cDeptCode,max(cPerson) as cPerson,max(cInvCode) as cInvCode,max(cCode) as cCode,
max(cDwName) as cDwName,max(cDWCCode) as cDWCCode,max(cDWDCode) as cDWDCode,max(cHDWCode) as cHDWCode,
max(cHDptCode) as cHDWCode,max(cHPsnCode) as cHPsnCode,max(cItem_Class) as cItem_Class,
max(cItemCode) as cItemCode,max(cItemName) as cItemName,
Max(cGlsign+'-'+REPLICATE(0,4-len(iGLno_id))+convert(varchar,iGLno_id)) as cPzNum,
max(case when ccovouchtype=cprocstyle Or iFlag=1 Or cProcStyle='XJ' then cdigest
else cprocname end) as dgst,max(case when ccovouchtype=cprocstyle or iFlag=1 Or cProcStyle='XJ'
then cTypeName else cProcName end) as vtype,max(case when ccovouchtype=cprocstyle or iFlag=1 Or
cProcStyle='XJ' then cCoVouchId else cCancelNo end) as vid,max(cCancelNo) as ccancelno, max(cpzid)
as cpzid,sum(case when cexch_name='人民币' then 0 else idamount_f end) as jf_f,sum(idamount_s) as jf_s,
sum(idamount) as jf,sum(case when cexch_name='人民币' then 0 else icamount_f end) as df_f,
sum(icamount_s) as df_s,sum(icamount) as df,max(cexch_name) as exchname,max(iprice) as price,
max(iexchrate) As Rate,'AR',Max(Auto_Id),min(dRegDate) From Ap_DetailCust_V where dRegDate>=@sdate and
dRegDate<=@edate And cFlag='AR' And cDwCode>=@cdwcode1 And cDwCode<=@cdwcode2 and iflag<3 Group by cdwcode,
cinvcode,ccancelno,cExch_Name,iFlag having sum(idamount)<>0 or sum(icamount) <> 0 Or sum(idamount_f)<>0
or sum(icamount_f) <> 0 Order By cdwcode,cinvcode,MAX(Auto_id)
update tempdb..sp_ywmxzb set jf_f=(case when jf_f is null then 0 else jf_f end),
jf_s=(case when jf_s is null then 0 else jf_s end),jf=(case when jf is null then 0 else jf end),
df_f=(case when df_f is null then 0 else df_f end),df_s=(case when df_s is null then 0 else df_s end),
df=(case when df is null then 0 else df end),jf_f2=(case when jf_f2 is null then 0 else jf_f2 end),
jf_s2=(case when jf_s2 is null then 0 else jf_s2 end),jf2=(case when jf2 is null then 0 else jf2 end),
df_f2=(case when df_f2 is null then 0 else df_f2 end),df_s2=(case when df_s2 is null then 0 else df_s2
end),df2=(case when df2 is null then 0 else df2 end)
Update tempdb..sp_ywmxzb set ye_f=jf_f-df_f-jf_f2+df_f2,ye_s=jf_s-df_s-jf_s2+df_s2,
ye=jf-df-jf2+df2
delete tempdb..sp_ywmxzb Where jf_f=0 and jf=0 and df_f=0 and df=0 and jf_f2=0 and jf2=0
and df_f2=0 and df2=0
Update tempdb..sp_ywmxzb set jf_f=null,jf_s=null,jf=null,df_f=null,df_s=null,df=null,jf_f2=null,
jf_s2=null,jf2=null,df_f2=null,df_s2=null,df2=null,csysid=Null where dgst='期初余额'
Update tempdb..sp_ywmxzb set cInvName=Inventory.cInvName From Inventory
Where tempdb..sp_ywmxzb.cInvcode=Inventory.cInvCode
qxq321 2005年02月27日
是要分开的，有时候冲销单据这样的就不能理解为入库是负数。
select 名称,型号,sum(入库数) ,sum(出库数)
from tb
group by 名称,型号
having sum(入库数)+sum(出库数)>0
select 名称,型号,sum(入库数) ,sum(出库数)
from tb
group by 名称,型号
having sum(入库数)+sum(出库数)>0
rose76219 2005年02月27日
zheninchangjiang(我爱燕子) 说的对，为什么这样做是因为有时存货出库后，又做了退货处理，所以必须这样写。
cocopww 2005年02月27日
sum(入库数)+ sum(出库数)<>0 好象有点不对，如果存在入库数量为 10，但出库的数据也为10 的数据，不就显示不出来了吗？
cocopww 2005年02月27日
select 名称,型号,sum(入库数) as 入库数量,sum(出库数) as 出库数量
from #A1
group by 名称,型号
having sum(入库数)<>0 or sum(出库数)<>0
from #A1
group by 名称,型号
having sum(入库数)<>0 or sum(出库数)<>0
zhangzs8896 2005年02月26日
select 名称,型号,入库=sum(入库数) ,出库=sum(出库数)
from 表
group by 名称,型号
having sum(入库数)+sum(出库数)>0
与
select * from (
select 名称,型号,入库=sum(入库数) ,出库=sum(出库数)
from 表
group by 名称,型号
)a
where (a.入库+a.出库)<>0
基本一样，但是一般还是用一个字段来表示出入库的。正表示入，负表示出。汇总后就是库存量。
from 表
group by 名称,型号
having sum(入库数)+sum(出库数)>0
与
select * from (
select 名称,型号,入库=sum(入库数) ,出库=sum(出库数)
from 表
group by 名称,型号
)a
where (a.入库+a.出库)<>0
基本一样，但是一般还是用一个字段来表示出入库的。正表示入，负表示出。汇总后就是库存量。
zhangzs8896 2005年02月26日
或者也可以这样：
select * from (
select 名称,型号,入库=sum(入库数) ,出库=sum(出库数)
from @a
group by 名称,型号
)a
where (a.入库+a.出库)<>0
也就是where与having的不同了。
select * from (
select 名称,型号,入库=sum(入库数) ,出库=sum(出库数)
from @a
group by 名称,型号
)a
where (a.入库+a.出库)<>0
也就是where与having的不同了。
zhangzs8896 2005年02月26日
同意冷月无声
zzlazio 2005年02月26日
up
Softlee81307 2005年02月26日
select 名称,型号,sum(入库数) ,sum(出库数)
from tb where 日期='05-1-1'
group by 名称,型号
having sum(入库数)+sum(出库数)>0
from tb where 日期='05-1-1'
group by 名称,型号
having sum(入库数)+sum(出库数)>0
didoleo 2005年02月26日
victorycy(中海) 说的对
其实只要一个字段 (出/入库)
正的代表入库,负的代表出库不就可以了吗?
其实只要一个字段 (出/入库)
正的代表入库,负的代表出库不就可以了吗?
zheninchangjiang 2005年02月26日
有的是冲单据,所以不可以说正负数的实际意义
zheninchangjiang 2005年02月26日
having sum(入库数)<> and sum(出库数)<>0
victorycy 2005年02月26日
你的例子中，C和D只是写法不同而已，负的入库数即是出库数，负的出库数即是入库数吧。
点点星灯 2005年02月26日
楼上正解!!! 学习!!!
declare @a table(名称 varchar(20),型号 varchar(10),入库数 int,出库数 int)
insert @a(名称,型号,入库数,出库数)
values('A材料','A',100,50)
insert @a(名称,型号,入库数,出库数)
values('A材料','A',100,0)
insert @a(名称,型号,入库数,出库数)
values('A材料','A+',0,10)
insert @a(名称,型号,入库数,出库数)
values('B材料','B',100,0)
insert @a(名称,型号,入库数,出库数)
values('C材料','C',50,0)
insert @a(名称,型号,入库数,出库数)
values('C材料','C',-50,0)
insert @a(名称,型号,入库数,出库数)
values('D材料','D',0,10)
insert @a(名称,型号,入库数,出库数)
values('D材料','D',0,-10)
select * from @a
select 名称,型号,sum(入库数) ,sum(出库数)
from @a
group by 名称,型号
having sum(入库数)+sum(出库数)>0
declare @a table(名称 varchar(20),型号 varchar(10),入库数 int,出库数 int)
insert @a(名称,型号,入库数,出库数)
values('A材料','A',100,50)
insert @a(名称,型号,入库数,出库数)
values('A材料','A',100,0)
insert @a(名称,型号,入库数,出库数)
values('A材料','A+',0,10)
insert @a(名称,型号,入库数,出库数)
values('B材料','B',100,0)
insert @a(名称,型号,入库数,出库数)
values('C材料','C',50,0)
insert @a(名称,型号,入库数,出库数)
values('C材料','C',-50,0)
insert @a(名称,型号,入库数,出库数)
values('D材料','D',0,10)
insert @a(名称,型号,入库数,出库数)
values('D材料','D',0,-10)
select * from @a
select 名称,型号,sum(入库数) ,sum(出库数)
from @a
group by 名称,型号
having sum(入库数)+sum(出库数)>0
didoleo 2005年02月26日
select 名称,型号,sum(入库数) ,sum(出库数)
from tb
group by 名称,型号
having sum(入库数)+sum(出库数)>0
from tb
group by 名称,型号
having sum(入库数)+sum(出库数)>0