34,576
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE bill
(
Bill_id VARCHAR(20), --单据号
Master_bill_id VARCHAR(20), --主单据号
CreateTime DATETIME ,
Amount DECIMAL ,
Flag INT
)
INSERT INTO bill SELECT 'B0001','','2009-4-1',2000,1
UNION ALL SELECT 'B0002','','2009-4-1',5000,1
UNION ALL SELECT 'B0003','B0001','2009-4-5',1500,0
UNION ALL SELECT 'B0004','B0002','2009-5-5',4000,0
UNION ALL SELECT 'B0005','','2009-5-5',2000,0
注:Master_bill_id 是Bill_id 的新单据
就是变更了之后的 (flag 为1时表示单据作废了金额需要释放出来)
想得到如下结果:
年 月份 , 金额
2009 4 4000 (单据B0003是单据B0001更改后的需要减掉单据B0001的金额)
2009 5 1000 (单据B0004是单据B0002更改后的需要减掉单据B0002的金额)
到底是6500还是4500,6500的话看看4楼的
IF OBJECT_ID('bill') IS NOT NULL
DROP TABLE bill
Go
CREATE TABLE bill
(
Bill_id VARCHAR(20), --单据号
Master_bill_id VARCHAR(20), --主单据号
CreateTime DATETIME ,
Amount DECIMAL ,
Flag INT
)
INSERT INTO bill
SELECT 'B0001','','2009-4-1',2000,1
UNION ALL SELECT 'B0002','','2009-4-1',5000,1
UNION ALL SELECT 'B0003','B0001','2009-4-5',1500,0
UNION ALL SELECT 'B0004','B0002','2009-5-5',4000,0
UNION ALL SELECT 'B0005','','2009-5-5',2000,0
select * from bill
select 年=year(s.createtime),月=month(s.createtime),金额=sum(s.amount)-sum(t.amount)
from bill s left join bill t on t.bill_id=s.Master_bill_id
group by year(s.createtime),month(s.createtime)
年 月 金额
2009 4 6500
2009 5 1000
IF OBJECT_ID('bill') IS NOT NULL
DROP TABLE bill
Go
CREATE TABLE bill
(
Bill_id VARCHAR(20), --单据号
Master_bill_id VARCHAR(20), --主单据号
CreateTime DATETIME ,
Amount DECIMAL ,
Flag INT
)
INSERT INTO bill
SELECT 'B0001','','2009-4-1',2000,1
UNION ALL SELECT 'B0002','','2009-4-1',5000,1
UNION ALL SELECT 'B0003','B0001','2009-4-5',1500,0
UNION ALL SELECT 'B0004','B0002','2009-5-5',4000,0
UNION ALL SELECT 'B0005','','2009-5-5',2000,0
select * from bill
select 年=year(s.createtime),月=month(s.createtime),金额=sum(s.amount)-sum(t.amount)
from bill s left join bill t
on t.bill_id=s.Master_bill_id
group by year(s.createtime),month(s.createtime)