这个sql语句怎么写呢?

[liangjianshi]

qcyeb:(期初表: 编号 数量 余额)
wpbh kcsl yuer
001 100 300
002 200 100
003 50 32
004 10 60

liushuizh:(业务表: 编号 操作类型 数量 金额)
wpbh czlx sl jine
001 采购 10 50
002 领用 50 25
001 领用 20 30
003 采购 50 30
002 采购 30 60


想得到这样的结果:
001 90 320
002 180 135
003 100 62
004 10 60

不知道sql语句怎么写啊.

[liangjianshi]

对存储过程不熟悉,请问用sql 语句,怎么得到如上的结果
请大家帮忙看看.

[WangZWang]

select isnull(a.wpbh,b.wpbh),
kcsl=sum(isnull(a.kcsl,0)+isNULL((case when b.czlx='采购' then
sl else sl*-1 end),0),
yuer=sum(isnull(a.yuer,0)+isNULL((case when b.czlx='采购' then
jine else jine*-1 end),0)
from qcyeb a full join liushuizh b on a.wpbh=b.wpbh
group by a.wpbh,b.wpbh

[拓狼]

select wpbh,sum(kcsl) as kcsl,sum(yuer) as yuer from(
select * from qcyeb
union all
select wpbh,sl=case when czlx='采购' then sl else -sl end,
jine =case when czlx='采购' then jine else -jine end,
from liushuizh)a
group by wpbh

[mschen]

--have a try!

select wpbh,
(kcsl+(select sum(case czlx when '采购' then sl when '领用' then -sl end)
from wpbh
where wpbh=a.wpbh)) as kcsl,
(yuer+(select sum(case czlx when '采购' then jine when '领用' then -jine end)
from wpbh
where wpbh=a.wpbh)) as yuer
from qcyeb a

[liangjianshi]

现在操作类型是这样的化，该怎么写呢
liushuizh:(业务表: 编号 操作类型 数量 金额)
wpbh czlx sl jine
001 采购入库 10 50
002 领用出库 50 25
001 领用退库 20 30
003 采购退库 50 30
002 采购退库 30 60

[zlp321002]

--测试环境
create table qcyeb (编号 varchar(20),数量 int,余额 int)
insert into qcyeb select '001',100,300
union all select '002',200,100
union all select '003',50,32
union all select '004',10,60

create table liushuizh (编号 varchar(20),操作类型 varchar(20),数量 int,余额 int)
insert into liushuizh select '001','采购入库',10,50
union all select '002','领用出库',50,25
union all select '001','领用退库',20,30
union all select '003','采购退库',50,30
union all select '002','采购退库',30,60

--查询
with liushuizhCTE
as
(
select 编号,操作类型,
数量=sum(case when 操作类型='采购入库' then B.数量
when 操作类型='领用出库' then -B.数量 end
),
余额=sum(case when 操作类型='采购入库' then 余额
when 操作类型='领用出库' then -B.余额 end
)
from liushuizh B
group by 编号,操作类型
)
select a.编号,数量=sum(isnull(a.数量+b.数量,a.数量)),余额=sum(isnull(a.余额+b.余额,a.余额)) from qcyeb A left join liushuizhCTE B
on A.编号=b.编号
group by a.编号

--结果
/*
编号 数量 余额
-------------------- ----------- -----------
001 210 650
002 350 175
003 50 32
004 10 60
*/
--删除测试环境
drop table qcyeb,liushuizh

[liangjianshi]

to andy1995(拓狼（一一）) :from 附近有语法错误
to WangZWang(阿来):"="号附近有语法错误

[WangZWang]

--少个括号
select isnull(a.wpbh,b.wpbh),
kcsl=sum(isnull(a.kcsl,0)+isNULL((case when b.czlx='采购' then
sl else sl*-1 end),0)),
yuer=sum(isnull(a.yuer,0)+isNULL((case when b.czlx='采购' then
jine else jine*-1 end),0))
from qcyeb a full join liushuizh b on a.wpbh=b.wpbh
group by a.wpbh,b.wpbh

[liangjianshi]

zlp321002(茫茫人海一次擦肩,魂系梦牵度日如年,相见恨晚心有何怨)
怎么运行你的程序啊？

[liangjianshi]

to WangZWang(阿来):列名"czlx"无效

[zlp321002]

--------如果是SQL 2005 以上
--------如果是SQL 2000 以下
select a.编号,数量=sum(isnull(a.数量+b.数量,a.数量)),余额=sum(isnull(a.余额+b.余额,a.余额)) from qcyeb A
left join (select 编号,操作类型,
数量=sum(case when 操作类型='采购入库' then B.数量
when 操作类型='领用出库' then -B.数量 end
),
余额=sum(case when 操作类型='采购入库' then 余额
when 操作类型='领用出库' then -B.余额 end
)
from liushuizh B
group by 编号,操作类型) B
on A.编号=b.编号
group by a.编号

[liuyinbo0109]

select A.编号,数量=a.数量+sum(case 操作类型 when '采购入库' then b.数量
when '领用退库' then b.数量
when '领用入库' then (-1)*b.数量
when '采购退库' then (-1)*b.数量
else 0 end),
余额=a.余额+sum(case 操作类型 when '采购入库' then b.余额
when '领用退库' then b.余额
when '领用入库' then (-1)*b.余额
when '采购退库' then (-1)*b.余额
else 0 end)
from qcyeb A left join liushuizh B on a.编号=b.编号
group by a.编号,a.数量,a.余额
order by a.编号

[zhouhaihe]

--测试环境

--drop table qcyeb,liushuizh
create table qcyeb (编号 varchar(20),数量 int,余额 int)
insert into qcyeb select '001',100,300
union all select '002',200,100
union all select '003',50,32
union all select '004',10,60

create table liushuizh (编号 varchar(20),操作类型 varchar(20),数量 int,金额 int)
insert into liushuizh
select '001','采购',10,50
union all select '002','领用',50,25
union all select '001','领用',20,30
union all select '003','采购',50,30
union all select '002','采购',30,60


select a.编号,a.数量+isnull((select sum(case 操作类型 when '采购' then 数量 when '领用' then -数量 else 0 end)
from liushuizh where 编号=a.编号),0) as 数量,
a.余额+isnull((select sum(case 操作类型 when '采购' then 金额 when '领用' then -金额 else 0 end)
from liushuizh where 编号=a.编号),0) as 金额
from qcyeb a


--删除测试环境
drop table qcyeb,liushuizh

[liangjianshi]

数据量很大，请问大家的写法效率会怎么样呢？