刷卡充值、消费和余额记录的生成

Question

Date                 CardNo Name     Recharge  Consume   Surplus  Operator
2010-10-26 10:46:14  02471  王珲 0.0000 1.0000  425.0000   管理员
2010-10-26 10:34:48  02471  王珲 0.0000 1.0000  426.0000   管理员2010-10-25 18:21:00  02471  王珲 0.0000 2.0000  427.0000   管理员
2010-10-25 11:38:00  02471  王珲     6.0000 0.0000  429.0000   管理员
2010-10-25 07:02:00  02471  王珲     3.0000 0.0000  423.0000   管理员Recharge：充值金额
Consume：消费金额
Surplus：余额
本人分不多，希望各位帮帮忙。请问：怎么用SQL获取  
时间          充值金额  消费金额   余额
2010-10-25     9.0000   2.0000    427.0000
2010-10-26     0.0000   2.0000    425.0000王珲本来有余额：420.0000。要求是计算出每天的数据，充值金额和消费金额是按每天累加，而余额则是每天最后时间的余额。意思就是每天的分开计算。假设应该很明白，本人分不多，希望各位帮帮忙。。

Answer 1

group by cast([Date] as date),CardNo 

Answer 2

create table tb(Date datetime,CardNo varchar(10),Name varchar(10),Recharge decimal(18,4),Consume decimal(18,4),Surplus decimal(18,4),Operator varchar(10))
insert into tb values('2010-10-26 10:46:14', '02471', '王珲', 0.0000, 1.0000, 425.0000 ,'管理员') 
insert into tb values('2010-10-26 10:34:48', '02471', '王珲', 0.0000, 1.0000, 426.0000 ,'管理员')
insert into tb values('2010-10-25 18:21:00', '02471', '王珲', 0.0000, 2.0000, 427.0000 ,'管理员')
insert into tb values('2010-10-25 11:38:00', '02471', '王珲', 6.0000, 0.0000, 429.0000 ,'管理员') 
insert into tb values('2010-10-25 07:02:00', '02471', '王珲', 3.0000, 0.0000, 423.0000 ,'管理员')
go select m.* , n.Surplus 余额 from
(
select convert(varchar(10),Date,120) 时间 ,
       sum(Recharge) 充值金额,
       sum(Consume) 消费金额 
from tb group by convert(varchar(10),Date,120)
) m,
(
select t.* from tb t where Date =(select max(Date) from tb where datediff(day,date,t.date) = 0)
) n
where datediff(day , m.时间,n.date) = 0drop table tb/*
时间         充值金额                                     消费金额                                     余额                   
---------- ---------------------------------------- ---------------------------------------- -------------------- 
2010-10-25 9.0000                                   2.0000                                   427.0000
2010-10-26 .0000                                    2.0000                                   425.0000（所影响的行数为 2 行）
*/

Answer 3

--> 测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
GO 
create table #TB (Date datetime,CardNo varchar(5),Name varchar(4),Recharge numeric(5,4),Consume numeric(5,4),Surplus numeric(7,4),Operator varchar(6))
insert into #TB
select '2010-10-26 10:46:14','02471','王珲',0.0000,1.0000,425.0000,'管理员' union all
select '2010-10-26 10:34:48','02471','王珲',0.0000,1.0000,426.0000,'管理员' union all
select '2010-10-25 18:21:00','02471','王珲',0.0000,2.0000,427.0000,'管理员' union all
select '2010-10-25 11:38:00','02471','王珲',6.0000,0.0000,429.0000,'管理员' union all
select '2010-10-25 07:02:00','02471','王珲',3.0000,0.0000,423.0000,'管理员'select CONVERT(VARCHAR(10),DATE,120) AS 时间,
 SUM(Recharge) AS 充值金额,
 SUM(Consume) AS 消费金额, 
 420+ (SELECT SUM(ISNULL(Recharge,0))-SUM(ISNULL(Consume,0)) FROM #TB WHERE CONVERT(VARCHAR(10),DATE,120)<=CONVERT(VARCHAR(10),T.DATE,120) ) AS 余额 
from #TB T
GROUP BY  CONVERT(VARCHAR(10),DATE,120) 
 
时间         充值金额                                    消费金额                                    余额
---------- --------------------------------------- --------------------------------------- ---------------------------------------
2010-10-25 9.0000                                  2.0000                                  427.0000
2010-10-26 0.0000                                  2.0000                                  425.0000

Answer 4

三楼的datediff(day,date,t.date) = 0，精彩，佩服。

Answer 5

要不要显示姓名啊！create table #TB (Date datetime,CardNo varchar(5),Name varchar(4),Recharge numeric(5,4),Consume numeric(5,4),Surplus numeric(7,4),Operator varchar(6))
insert into #TB
select '2010-10-26 10:46:14','02471','王珲',0.0000,1.0000,425.0000,'管理员' union all
select '2010-10-26 10:34:48','02471','王珲',0.0000,1.0000,426.0000,'管理员' union all
select '2010-10-25 18:21:00','02471','王珲',0.0000,2.0000,427.0000,'管理员' union all
select '2010-10-25 11:38:00','02471','王珲',6.0000,0.0000,429.0000,'管理员' union all
select '2010-10-25 07:02:00','02471','王珲',3.0000,0.0000,423.0000,'管理员'
GOselect a.时间,a.充值金额,a.消费金额,#tb.surplus 余额 from 
(select convert(varchar(10),date,120) as 时间,sum(Recharge) 充值金额,sum(Consume) 消费金额,max(date) maxdate from #tb group by convert(varchar(10),date,120) ) a
,#tb where  datediff(second,a.maxdate,#tb.date)=0 order by a.时间 asc
GO
时间         充值金额                                    消费金额                                    余额
---------- --------------------------------------- --------------------------------------- ---------------------------------------
2010-10-25 9.0000                                  2.0000                                  427.0000
2010-10-26 0.0000                                  2.0000                                  425.0000(2 行受影响)

Answer 6

--借用#6的建表及演示语句。
WITH #t AS
(
SELECT CardNo, Name, CONVERT(varchar(10),Date,120) AS Date, SUM(Recharge) AS Recharge, SUM(Consume) AS Consume 
FROM #TB a 
GROUP BY CONVERT(varchar(10),Date,120),CardNo, Name
)
SELECT a.CardNo, a.Name, a.Date AS 时间, a.Recharge, a.Consume, 420 + SUM(b.Recharge) - SUM(b.Consume) AS 余额
FROM #t a 
JOIN #t b ON a.CardNo = b.CardNo AND a.Date >= b.Date
GROUP BY a.Date, a.CardNo, a.Name, a.Recharge, a.Consume---------------------------------------------------------
02471 王珲 2010-10-25 9.0000 2.0000 427.0000
02471 王珲 2010-10-26 0.0000 2.0000 425.0000