一个人员进销存借物SQL
SQL SERVER 2005
表：
编号，用户ID，物料编号，物料名，数量，出入，时间
1 333 B123 XX 10 -1 20110101
2 333 B123 XX 5 1 20110105
3 333 B111 X1 30 -1 20110102
4 333 B112 X2 13 -1 20110106
5 333 B112 X2 13 1 20110109
出入：-1是借出，1是归还。
现在想统计333这个用户个人借物情况，统计表如下。
用户ID，物料编号，物料名，借出数量，归还数量，未归还数量。
333 B123 XX 10 5 5
333 B111 X1 30 0 30
333 B112 X2 13 13 0
有没有效率高一点的SQL
[解决办法]
with cte(编号,用户ID,物料编号,物料名,数量,出入,时间)
as
(
select 1 ,333 ,'B123' ,'XX' ,10 ,-1 ,20110101 union all
select 2 ,333 ,'B123' ,'XX' ,5 ,1 ,20110105 union all
select 3 ,333 ,'B111' ,'X1' ,30 ,-1 ,20110102 union all
select 4 ,333 ,'B112' ,'X2' ,13 ,-1 ,20110106 union all
select 5 ,333 ,'B112' ,'X2' ,13 ,1 ,20110109
)
select 用户ID,物料编号,物料名,
sum(case 出入 when -1 then 数量 else 0 end) 借出数量,
sum(case 出入 when 1 then 数量 else 0 end) 归还数量,
sum(case 出入 when -1 then 数量 else 0 end)-sum(case 出入 when 1 then 数量 else 0 end) 未归还数量
from
cte
where 用户ID=333
group by 用户ID,物料编号,物料名
if object_id('Log201305') is not null
drop table Log201305
go
create table Log201305
(
id int,
usercode int,
wlcode nvarchar(20),
wlname nvarchar(20),
qty int,
inout int,
date datetime
)
go
insert into Log201305
select 1,333,'B123','XX',10,-1,'20110101' union all
select 2,333,'B123','XX',5,1,'20110105' union all
select 3,333,'B111','X1',30,-1,'20110102' union all
select 4,333,'B112','X2',13,-1,'20110106' union all
select 5,333,'B112','X2',13,1,'20110109'
select * from Log201305
go
--用户ID，物料编号，物料名，借出数量，归还数量，未归还数量。
-- 333 B123 XX 10 5 5
-- 333 B111 X1 30 0 30
-- 333 B112 X2 13 13 0
select *,(借出数量 -归还数量)未归还数量
from
(
select
usercode,wlcode,wlname,
max(case when inout = '-1' then qty else 0 end)借出数量,
max(case when inout = '1' then qty else 0 end)归还数量
from Log201305
group by usercode,wlcode,wlname
)a
--不想在外面再用子查询的话，可以改成
select
usercode,wlcode,wlname,
max(case when inout = '-1' then qty else 0 end)借出数量,
max(case when inout = '1' then qty else 0 end)归还数量,
(max(case when inout = '-1' then qty else 0 end) -max(case when inout = '1' then qty else 0 end))未归还数量
from Log201305
group by usercode,wlcode,wlname
