DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red', -23
UNION ALL SELECT 'bb','Cup' ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red', -90--统计及排序
SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
ORDER BY GROUPING(Groups),
CASE WHEN GROUPING(Groups)=1 THEN '' ELSE Groups END DESC,
GROUPING(Item),
CASE WHEN GROUPING(Item)=1 THEN '' ELSE Item END DESC,
GROUPING(Color),
CASE WHEN GROUPING(Color)=1 THEN '' ELSE Color END DESC,
Quantity DESC
/*--结果
Groups Item Color Quantity
--------- -------------- ----------------------- -----------
bb Table Red -23
Table 小计 -23
bb Cup Green -23
Cup 小计 -23
bb 合计 -46
aa Table Blue 124
Table 小计 124
aa Chair Red -90
aa Chair Blue 101
Chair 小计 11
aa 合计 135
总计 89参考下吧
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red', -23
UNION ALL SELECT 'bb','Cup' ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red', -90--统计及排序
SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
ORDER BY GROUPING(Groups),
CASE WHEN GROUPING(Groups)=1 THEN '' ELSE Groups END DESC,
GROUPING(Item),
CASE WHEN GROUPING(Item)=1 THEN '' ELSE Item END DESC,
GROUPING(Color),
CASE WHEN GROUPING(Color)=1 THEN '' ELSE Color END DESC,
Quantity DESC
/*--结果
Groups Item Color Quantity
--------- -------------- ----------------------- -----------
bb Table Red -23
Table 小计 -23
bb Cup Green -23
Cup 小计 -23
bb 合计 -46
aa Table Blue 124
Table 小计 124
aa Chair Red -90
aa Chair Blue 101
Chair 小计 11
aa 合计 135
总计 89参考下吧
CREATE TABLE Stocks(Item varchar(10),Period int,Balance int)
INSERT Stocks SELECT 'aa',200501,100
UNION ALL SELECT 'cc',200501,100--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
Item varchar(10), --产品编号
Quantity int, --交易数量
Flag bit, --交易标志,1代表入库,0代表出库,这样可以有效区分退货(负数)
Date datetime) --交易日期
INSERT tb SELECT 'aa',100,1,'2005-1-1'
UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
UNION ALL SELECT 'aa',55 ,0,'2005-2-1'
UNION ALL SELECT 'aa',-10,1,'2005-2-2'
UNION ALL SELECT 'aa',-5 ,0,'2005-2-3'
UNION ALL SELECT 'aa',200,1,'2005-2-2'
UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
UNION ALL SELECT 'bb',95 ,1,'2005-2-2'
UNION ALL SELECT 'bb',65 ,0,'2005-2-3'
UNION ALL SELECT 'bb',-15,1,'2005-2-5'
UNION ALL SELECT 'bb',-20,0,'2005-2-5'
UNION ALL SELECT 'bb',100,1,'2005-2-7'
UNION ALL SELECT 'cc',100,1,'2005-1-7'
GO--查询时间段定义
DECLARE @dt1 datetime,@dt2 datetime
SELECT @dt1='2005-2-2',@dt2='2005-2-10'--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @Period int,@dt datetime
SELECT @Period=CONVERT(CHAR(6),DATEADD(Month,-1,@dt1),112),
@dt=DATEADD(Day,1-Day(@dt1),@dt1)--查询期初库存
SELECT Item=ISNULL(a.Item,b.Item),
Date=ISNULL(b.Date,CONVERT(char(10),@dt1,120)),
Opening=ISNULL(a.Balance,0)+ISNULL(b.Opening,0),
[IN]=ISNULL(b.[IN],0),
[IN_Retrun]=ISNULL(b.[IN_Retrun],0),
[OUT]=ISNULL(b.[OUT],0),
[OUT_Return]=ISNULL(b.[OUT_Return],0),
Balance=ISNULL(a.Balance,0)+ISNULL(b.Opening,0)+ISNULL(b.Amount,0)
FROM(
--期初数
SELECT Item,Balance FROM Stocks WHERE Period=@Period
)a FULL JOIN(
--统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)
SELECT Item,
Date=CONVERT(char(10),@dt1,120),
Opening=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END),
[IN]=0,
[IN_Retrun]=0,
[OUT]=0,
[OUT_Return]=0,
Amount=0
FROM tb a
WHERE Date>=@dt AND Date<@dt1
AND NOT EXISTS(
SELECT * FROM tb WHERE Item=a.Item AND Date>@dt1 AND Date<DATEADD(Day,1,@dt2))
GROUP BY Item
UNION ALL
--指定时间段内有交易发生的数据
SELECT Item,
Date=CONVERT(char(10),Date,120),
Opening=(SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
FROM tb WHERE Item=a.Item AND Date>=@dt AND Date<MIN(a.Date)),
[IN]=SUM(CASE WHEN Flag=1 AND Quantity>0 THEN Quantity END),
[IN_Retrun]=SUM(CASE WHEN Flag=1 AND Quantity<0 THEN -Quantity END),
[OUT]=SUM(CASE WHEN Flag=0 AND Quantity>0 THEN Quantity END),
[OUT_Return]=SUM(CASE WHEN Flag=0 AND Quantity<0 THEN -Quantity END),
Amount=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
FROM tb a
WHERE Date>=@dt1 AND Date<DATEADD(Day,1,@dt2)
GROUP BY CONVERT(char(10),Date,120),Item
)b ON a.Item=b.Item
ORDER BY Item,Date
/*--结果
Item Date Opening IN IN_Retrun OUT OUT_Return Balance
---------- ---------------- -------------- ----------- ---------------- ----------- ------------------- -----------
aa 2005-02-02 225 200 10 0 0 415
aa 2005-02-03 415 0 0 0 5 420
bb 2005-02-02 0 95 0 0 0 95
bb 2005-02-03 95 0 0 65 0 30
bb 2005-02-05 30 0 15 0 20 35
bb 2005-02-07 35 100 0 0 0 135
cc 2005-02-01 100 0 0 0 0 100
--*/
,(select sun(数量) from 表 where 出入库=1)as 出库,
(select sun(数量) from 表)as 结算
from 表
where 日期 between 2009-01-31 and 2009-02-28 group by 货物名称写不全,关注,学习一下
-- Author:happyflystone
-- Version:V1.001
-- Date:2009-02-16 23:27:12
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int identity(1,1),货物名称 nvarchar(2),日期 smalldatetime,
数量 int,单价 int,金额 int,出入库 int)
Go
Insert into ta
-- 2009-01-07期初数据 select '钢笔','2009-01-07',10,0,0,0 union all
select '钢笔','2009-01-08',5,0,0,0 union all
select '钢笔','2009-01-09',5,'6','30',1 union all
select '钢笔','2009-01-09',20,'0','0',0 union all
select '钢笔','2009-01-10',30,'8','160',1 union all
select '铅笔','2009-01-07',20,'0','0',0 union all
select '铅笔','2009-01-09',20,'0','0',0 union all
select '铅笔','2009-01-09',10,'0','0',0 union all
select '铅笔','2009-01-12',30,'10','300',1 union all
select '铅笔','2009-01-12',20,'14','280',1
Go
--Start
Select 0 as pd,
货物名称,
convert(char(10),日期,120) as 日期,
期初= (select sum(case when 出入库 = '0' then 数量 else -1 * 数量 end ) from ta where 货物名称 = a.货物名称 and id < a.id) ,
本期入库 = (case when 出入库 = '0' then 数量 else 0 end),
本期发出 = (case when 出入库 = '1' then 数量 else 0 end),
期末结存= (select sum(case when 出入库 = '0' then 数量 else -1 * 数量 end ) from ta where 货物名称 = a.货物名称 and id <= a.id)
from ta a
where 日期 > '2009-01-07'
union all
select 1 as od,货物名称+'合计' as t,
'' as t2,
t3 = (select sum(数量) from ta where 货物名称 = a.货物名称 and id <= a.id ),
t4 = (select sum(数量) from ta where 货物名称 = a.货物名称 and 出入库 = '0' and id > a.id ),
t5 = (select sum(数量) from ta where 货物名称 = a.货物名称 and 出入库 = '1' and id > a.id ),
t6 = (select sum(case when 出入库 = '0' then 数量 else -1 * 数量 end) from ta where 货物名称 = a.货物名称 )
from ta a
where 日期 = '2009-01-07'
group by 货物名称 ,id
order by 2,1
--Result:
/*pd 货物名称 日期 期初 本期入库 本期发出 期末结存
----------- ------ ---------- ----------- ----------- ----------- -----------
0 钢笔 2009-01-08 10 5 0 15
0 钢笔 2009-01-09 15 0 5 10
0 钢笔 2009-01-09 10 20 0 30
0 钢笔 2009-01-10 30 0 30 0
1 钢笔合计 10 25 35 0
0 铅笔 2009-01-09 20 20 0 40
0 铅笔 2009-01-09 40 10 0 50
0 铅笔 2009-01-12 50 0 30 20
0 铅笔 2009-01-12 20 0 20 0
1 铅笔合计 20 30 50 0(所影响的行数为 10 行)*/
--End
这是根据哦的理解做的,参考一下
select pp = 0,货物名称,日期
,期初 = (select sum(数量* case when 出入库 = 0 then 1 else -1 end) from ta where 货物名称= t.货物名称 and 日期 < t.日期 and id < t.id)
,本期入库 = (case when 出入库 = 0 then 数量 else null end)
,本期发出 = (case when 出入库 = 1 then 数量 else null end)
,期末结存 = (select sum(数量* case when 出入库 = 0 then 1 else -1 end) from ta where 货物名称= t.货物名称 and 日期 <= t.日期 and id <= t.id )
,单价
,金额
from ta t
union all -------获得统计值
select pp = 1 ,货物名称,日期 = 0
,期初 = (select sum(数量* case when 出入库 = 0 then 1 else -1 end) from ta where 货物名称= t.货物名称)
,本期入库 = sum(case when 出入库 = 0 then 数量 else null end)
,本期发出 = sum(case when 出入库 = 1 then 数量 else null end)
,期末结存 = (sum(数量* case when 出入库 = 0 then 1 else -1 end))
,单价 = 0
,金额 = sum(金额)
from ta t
group by 货物名称order by 货物名称,pp
pp 货物名称 日期 期初 本期入库 本期发出 期末结存 单价 金额
----------- ---- ----------------------- ----------- ----------- ----------- ----------- ----------- -----------
0 钢笔 2009-01-07 00:00:00 NULL 10 NULL 10 0 0
0 钢笔 2009-01-08 00:00:00 10 5 NULL 15 0 0
0 钢笔 2009-01-09 00:00:00 15 NULL 5 10 6 30
0 钢笔 2009-01-09 00:00:00 15 20 NULL 30 0 0
0 钢笔 2009-01-10 00:00:00 30 NULL 30 0 8 160
1 钢笔 1900-01-01 00:00:00 0 35 35 0 0 190
0 铅笔 2009-01-07 00:00:00 NULL 20 NULL 20 0 0
0 铅笔 2009-01-09 00:00:00 20 20 NULL 40 0 0
0 铅笔 2009-01-09 00:00:00 20 10 NULL 50 0 0
0 铅笔 2009-01-12 00:00:00 50 NULL 30 20 10 300
0 铅笔 2009-01-12 00:00:00 50 NULL 20 0 14 280
1 铅笔 1900-01-01 00:00:00 0 50 50 0 0 580