已有充值表和消费表,要求统计每个月的充值卡至今的消费总额。举例如下:
充值表(CZ):
卡号(cardno) 充值次数(times) 充值日期(loddate) 充值金额(lodamt)
91034567 1 2010-2-12 100
91034567 2 2010-5-21 100
91034567 3 2010-9-16 200
91045678 1 2010-3-1 200
91045678 2 2010-3-23 300
91056789 1 2010-2-19 300
消费表(XF):
卡号(cardno) 消费金额(amt) 消费日期(date)
91034567 40.00 2010-2-15
91034567 60.00 2010-3-26
91034567 100.00 2010-8-11
91034567 160.00 2010-10-3
91045678 120.00 2010-3-1
91045678 80.00 2010-3-5
91045678 220.00 2010-5-15
91056789 278.00 2010-5-2
统计2月份的数据如下:
消费总额sum(amt)= 378(40+60+278)
因为卡片可以回收,所以存在一个卡号重复使用(如91034567,第一次2月份充值后全部消费完,第二次5月份充值又使用了),请各位大侠帮助一下,怎么写这个统计语句?注意两点:
1、不是月度统计,是统计卡片从充值到当前为止的消费,但要考虑回收问题,如果回收后再次充值,则此后的消费计算在下次充值后的消费统计中
2、同一张卡片前后几次充值的金额可以不同
充值表(CZ):
卡号(cardno) 充值次数(times) 充值日期(loddate) 充值金额(lodamt)
91034567 1 2010-2-12 100
91034567 2 2010-5-21 100
91034567 3 2010-9-16 200
91045678 1 2010-3-1 200
91045678 2 2010-3-23 300
91056789 1 2010-2-19 300
消费表(XF):
卡号(cardno) 消费金额(amt) 消费日期(date)
91034567 40.00 2010-2-15
91034567 60.00 2010-3-26
91034567 100.00 2010-8-11
91034567 160.00 2010-10-3
91045678 120.00 2010-3-1
91045678 80.00 2010-3-5
91045678 220.00 2010-5-15
91056789 278.00 2010-5-2
统计2月份的数据如下:
消费总额sum(amt)= 378(40+60+278)
因为卡片可以回收,所以存在一个卡号重复使用(如91034567,第一次2月份充值后全部消费完,第二次5月份充值又使用了),请各位大侠帮助一下,怎么写这个统计语句?注意两点:
1、不是月度统计,是统计卡片从充值到当前为止的消费,但要考虑回收问题,如果回收后再次充值,则此后的消费计算在下次充值后的消费统计中
2、同一张卡片前后几次充值的金额可以不同
解决方案 »
- oracle 一个select语句的问题
- SQL能实现{将A表中的所有数据复制到B表(B表含有A表中所有字段,只是部分字段名称不同,但增加了5个新的字段,且新增的5个字段被设置为“不能为空”)}吗?
- 请问类似类似在语句中使用"(select uRealname from right_user where uid=approve_STable.eUid) as 申请人"这样的语句效率如何?
- 报表分析
- double类型跑哪去了。
- 这一条查询语句怎么写?
- 关于差异备份的问题
- 请问使用SQL Server编程,当服务器的数据变化时,客户端如何能够得知变化的内容?
- 为了在没有安装Access2K或者只是安装了Access97的机器能够访问Access2K的DB,应该如何?
- 导航里KTV数据库用什么编辑
- 为什么到另一个表中结果与这里的结果不一样?
- 数据库问题啊求解
--不知道理解的对不对,好久么写,写不好了
Create table CZ(cardno varchar(10),times int,loddate datetime,lodamt int)
insert into CZ select '91034567', 1, '2010-2-12', 100
insert into CZ select '91034567', 2, '2010-5-21', 100
insert into CZ select '91034567', 3, '2010-9-16', 200
insert into CZ select '91045678', 1, '2010-3-1' ,200
insert into CZ select '91045678', 2, '2010-3-23', 300
insert into CZ select '91056789', 1, '2010-2-19', 300insert into CZ select 'A', 1, '2010-2-19', 300
insert into CZ select 'A', 2, '2010-3-19', 300
insert into CZ select 'A', 3, '2010-4-19', 300insert into CZ select 'B', 1, '2010-2-11', 300
insert into CZ select 'B', 2, '2010-2-22', 300Create table XF (cardno varchar(10),amt int, date datetime )
insert into XF select '91034567' ,40.00, '2010-2-15'
insert into XF select '91034567', 60.00, '2010-3-26'
insert into XF select '91034567', 100.00, '2010-8-11'
insert into XF select '91034567', 160.00, '2010-10-3'
insert into XF select '91045678', 120.00, '2010-3-1'
insert into XF select '91045678', 80.00, '2010-3-5'
insert into XF select '91045678', 220.00, '2010-5-15'
insert into XF select '91056789', 278.00, '2010-5-2' insert into XF select 'A', 260, '2010-3-2'
insert into XF select 'A', 310, '2010-3-30' insert into XF select 'B', 500, '2010-2-15'
GO;with cte_play
as
(
select a.cardno
,a.loddate
,a.lodamt
,b.amt
,b.date
,case when a.v2-b.V2<=0
then case when a.v2-b.V1<=a.lodamt
then a.v2-b.V1
else a.lodamt end
else case when -(a.v1-b.V2)<= b.amt
then -(a.v1-b.V2)
else b.amt end
end as Lod_xf_amtfrom (
select * ,
isnull((select sum(lodamt) from CZ
where cardno = t.cardno
and loddate< t.loddate ),0) as V1,
(select sum(lodamt) from CZ
where cardno = t.cardno
and loddate<= t.loddate ) as V2
from CZ t
) as a,
(
select *,isnull((select sum(amt) from XF
where cardno = t.cardno
and date< t.date),0) as V1 ,
(select sum(amt) from XF
where cardno = t.cardno
and date<= t.date ) as V2
from XF t
) as b
where a.cardno=b.cardno
and a.V2-b.V1>=0 and a.V1-b.V2<=0
)--要统计的话,只要sum(Lod_xf_amt) 就是2月份
select * from cte_play
where Lod_xf_amt>0
and datediff( month, loddate, '2010-2-1') =0 --几月份统计,定个变量就OKGODrop table CZ,XF
create table CZ(cardno varchar(15),times int,loddate datetime,lodamt decimal(8,2))
insert into cz select '91034567',1,'2010-2-12',100
insert into cz select '91034567',2,'2010-5-21',100
insert into cz select '91034567',3,'2010-9-16',200
insert into cz select '91045678',1,'2010-3-1',200
insert into cz select '91045678',2,'2010-3-23',300
insert into cz select '91056789',1,'2010-2-19',300
create table XF(cardno varchar(15),amt decimal(8,2),date datetime)
insert into xf select '91034567',40.00,'2010-2-15'
insert into xf select '91034567',60.00,'2010-3-26'
insert into xf select '91034567',100.00,'2010-8-11'
insert into xf select '91034567',160.00,'2010-10-3'
insert into xf select '91045678',120.00,'2010-3-1'
insert into xf select '91045678',80.00,'2010-3-5'
insert into xf select '91045678',220.00,'2010-5-15'
insert into xf select '91056789',278.00,'2010-5-2'
go
;with c1 as(
select cardno,times,loddate,lodamt,0.0 amt from cz
union all
select cardno,(select top 1 times from cz where cardno=a.cardno and loddate<=a.date order by loddate desc),date,0.0,amt from xf a
)
select * from c1 order by 1,3,4
/*
cardno times loddate lodamt amt
--------------- ----------- ----------------------- --------------------------------------- ---------------------------------------
91034567 1 2010-02-12 00:00:00.000 100.00 0.00
91034567 1 2010-02-15 00:00:00.000 0.00 40.00
91034567 1 2010-03-26 00:00:00.000 0.00 60.00
91034567 2 2010-05-21 00:00:00.000 100.00 0.00
91034567 2 2010-08-11 00:00:00.000 0.00 100.00
91034567 3 2010-09-16 00:00:00.000 200.00 0.00
91034567 3 2010-10-03 00:00:00.000 0.00 160.00
91045678 1 2010-03-01 00:00:00.000 0.00 120.00
91045678 1 2010-03-01 00:00:00.000 200.00 0.00
91045678 1 2010-03-05 00:00:00.000 0.00 80.00
91045678 2 2010-03-23 00:00:00.000 300.00 0.00
91045678 2 2010-05-15 00:00:00.000 0.00 220.00
91056789 1 2010-02-19 00:00:00.000 300.00 0.00
91056789 1 2010-05-02 00:00:00.000 0.00 278.00(14 行受影响)*/
go
drop table cz,xf
我想得到的是一个统计脚本,或是统计语句:
只要输入相应的月份,就可以统计这个月的充值卡的消费记录。
select sum(amt) ...where loddate = 要统计的月份
我想得到的是一个统计脚本,或是统计语句:
只要输入相应的月份,就可以统计这个月的充值卡的消费记录。
select sum(amt) ...where loddate = 要统计的月份