表table里有几个字段
客户编码 出入库日期 出入库数量
custid indate quantity
12 2009-07-03 300
13 2009-08-12 593
15 2009-07-19 234
12 2009-07-22 200
12 2009-08-15 -150
12 2009-09-23 135
13 2009-09-23 75
……………………我现在想获得这样一组数值,每月统计客户的库存量(等于所有时间早于当月的quantity值累加),如果当月没有出入库,则按上月的计算
custid 月份 sum(quantity)
12 07 500
12 08 350
12 09 485
13 08 593
13 09 668
15 07 234
15 08 234
15 09 234
请问该怎么写SQL?
客户编码 出入库日期 出入库数量
custid indate quantity
12 2009-07-03 300
13 2009-08-12 593
15 2009-07-19 234
12 2009-07-22 200
12 2009-08-15 -150
12 2009-09-23 135
13 2009-09-23 75
……………………我现在想获得这样一组数值,每月统计客户的库存量(等于所有时间早于当月的quantity值累加),如果当月没有出入库,则按上月的计算
custid 月份 sum(quantity)
12 07 500
12 08 350
12 09 485
13 08 593
13 09 668
15 07 234
15 08 234
15 09 234
请问该怎么写SQL?
1、创建一临时表,客户ID,上月结余,本月入库,本月出库。
2、统计客户ID,上月结余到临时表中。(单据日期在本月之前的,所有进出结余)
3、统计客户ID,本月入库到临时表中。(单据日期在本月之内的,入库数据)
4、统计客户ID,本月出库到临时表中。(单据日期在本月之内的,出库数据)
5、统计结果,SELECT 客户ID,SUM(上月结余),SUM(本月入库),SUM(本月出库),
SUM(IsNull(上月结余,0)+IsNull(本月入库,0)-IsNull(本月出库,0))
FROM 临时表
GROUP BY 客户ID.
6、当然,这个还可以增加栏位,比如材料编号等。比较随意。
有时间限制吧,要有个开始的时间。
from tb group by custid,month(indate)
order by custid, month(indate)/*
custid indate quantity
----------- ----------- -----------
12 7 500
12 8 -150
12 9 135
13 8 593
13 9 75
15 7 234(所影响的行数为 6 行)
*/
-----------不知道是不是你要的结果----------
GOOD IDEA,但这个统计出来的只是当前月的库存吧,如果我想获得的结果是从期初时间开始(比如6月)以来每个客户各个月份库存情况。咋整呢
declare @t table(custid int,indate datetime,quantity int)
insert @t select
12, '2009-07-03', 300 union all select
13, '2009-08-12', 593 union all select
15, '2009-07-19', 234 union all select
12, '2009-07-22', 200 union all select
12, '2009-08-15', -150 union all select
12, '2009-09-23', 135 union all select
13 , '2009-09-23', 75 select custid,yuefen=month(indate),num=sum(quantity)
into #t
from @t
group by custid, month(indate)
select custid,yuefen ,
num=(select sum(num) from #t where custid=t.custid and yuefen<=t.yuefen)
from #t t
group by custid,yuefen
custid yuefen num
----------- ----------- -----------
12 7 500
12 8 350
12 9 485
13 8 593
13 9 668
15 7 234(6 行受影响)drop table #t
---不用临时表declare @t table(custid int,indate datetime,quantity int)
insert @t select
12, '2009-07-03', 300 union all select
13, '2009-08-12', 593 union all select
15, '2009-07-19', 234 union all select
12, '2009-07-22', 200 union all select
12, '2009-08-15', -150 union all select
12, '2009-09-23', 135 union all select
13 , '2009-09-23', 75
select custid,yuefen ,
num=(select sum(quantity) from @t where custid=t.custid and month(indate)<=t.yuefen)
from
(select custid,yuefen=month(indate),num=sum(quantity)
from @t
group by custid, month(indate)
) t
group by custid,yuefen
custid yuefen num
----------- ----------- -----------
12 7 500
12 8 350
12 9 485
13 8 593
13 9 668
15 7 234(6 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([custid] int,[indate] datetime,[quantity] int)
insert [tb]
select 12,'2009-07-03',300 union all
select 13,'2009-08-12',593 union all
select 15,'2009-07-19',234 union all
select 12,'2009-07-22',200 union all
select 12,'2009-08-15',-150 union all
select 12,'2009-09-23',135 union all
select 13,'2009-09-23',75;
with cte1 as --sql2005 如是sql2000,改成临时表
(
select * from (select distinct custid from tb) t1 , (select distinct month(indate) indate from tb) t2
)select * from
(
select custid,[indate],[sum(quantity)]=(select sum([quantity]) from tb where custid=t.custid and month(indate)<=t.indate)
from cte1 t
) t2 where [sum(quantity)] is not null
order by custid/*
custid indate sum(quantity)
----------- ----------- -------------
12 7 500
12 8 350
12 9 485
13 8 593
13 9 668
15 7 234
15 8 234
15 9 234(8 行受影响)
*/
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-10-23 08:03:52
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (custid INT,indate DATETIME,quantity INT)
INSERT INTO [tb]
SELECT 12,'2009-07-03',300 UNION ALL
SELECT 13,'2009-08-12',593 UNION ALL
SELECT 15,'2009-07-19',234 UNION ALL
SELECT 12,'2009-07-22',200 UNION ALL
SELECT 12,'2009-08-15',-150 UNION ALL
SELECT 12,'2009-09-23',135 UNION ALL
SELECT 13,'2009-09-23',75select * from [tb]
;with
wang as (select * from (select distinct month=datepart(month,indate) from tb ) K
cross join (select distinct custid from tb)L
),
wang1 as (select t.*,库存=(select sum(quantity) from tb where datepart(month,indate)<=datepart(month,t.indate) and custid=t.custid) from tb t)select wang.custid,month,库存=isnull(库存,(select top 1 库存 from wang1 t where t.custid=wang.custid and datepart(month,indate)<wang.month order by t.indate))
from wang left join wang1 on wang.custid=wang1.custid and wang.month=datepart(month,wang1.indate)12 7 500
12 7 500
12 8 350
12 9 485
13 7 NULL
13 8 593
13 9 668
15 7 234
15 8 234
15 9 234
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-10-23 08:03:52
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (custid INT,indate DATETIME,quantity INT)
INSERT INTO [tb]
SELECT 12,'2009-07-03',300 UNION ALL
SELECT 13,'2009-08-12',593 UNION ALL
SELECT 15,'2009-07-19',234 UNION ALL
SELECT 12,'2009-07-22',200 UNION ALL
SELECT 12,'2009-08-15',-150 UNION ALL
SELECT 12,'2009-09-23',135 UNION ALL
SELECT 13,'2009-09-23',75select * from [tb]
;with
wang as (select * from (select distinct month=datepart(month,indate) from tb ) K
cross join (select distinct custid from tb)L
)select wang.*,库存=(select sum(quantity) from tb where month(indate)<=month and custid=wang.custid)
from wang left join tb on wang.custid=tb.custid and wang.month=month(indate)
select distinct 0,1,dept_id,1,owner_user_id,0,0,
getdate(),getdate(),null,0,he.zzdtkc_refid01,he.库存,zzdtkc_char01,'历史库存',getdate() ,1,he.month
from tcu_zzdtkc tz,(
select distinct wang.*,库存=(select sum(zzdtkc_dec01) from tcu_zzdtkc where month(zzdtkc_date01)<=month and zzdtkc_refid01=wang.zzdtkc_refid01)
from (select * from (select distinct month=datepart(month,zzdtkc_date01) from tcu_zzdtkc ) K
cross join (select distinct zzdtkc_refid01 from tcu_zzdtkc)L
) as wang left join tcu_zzdtkc on wang.zzdtkc_refid01=tcu_zzdtkc.zzdtkc_refid01 and wang.month=month(zzdtkc_date01)) as he where tz.zzdtkc_refid01 = he.zzdtkc_refid01
前面加了个distinct后,半天查询不出来啊,不加的话很快就出来了,就是很多重复的,是啥问题呀