两张表,payment表为充值记录,Consume表为消费记录
create table payment
(PaymentID int IDENTITY(1,1) NOT NULL,
userid int not null,
coin int not null,
PaymentTs datetime not null)
insert into payment values
(1001,100,'2012-09-20 02:50:06.000'),
(1001,50,'2012-09-21 12:17:07.000'),
(1002,100,'2012-09-22 13:31:46.000'),
(1001,20,'2012-09-22 14:51:26.000')
create table consume
(HistoryID bigint IDENTITY(1,1) NOT NULL,
userid int not null,
coin int not null,
[desc] nvarchar(255) null,
CreateTS datetime not null)insert into consume values
(1001,24,'a','2012-09-20 02:55:32.000'),
(1001,68,'b','2012-09-20 03:02:24.000'),
(1001,37,'c','2012-09-21 12:25:06.000'),
(1002,48,'d','2012-09-22 13:42:46.000'),
(1002,39,'e','2012-09-22 13:42:46.000'),
(1001,41,'f','2012-09-22 15:01:06.000')
要求得到userid为1001的用户的消费记录
sumcoin为消费前的金钱,Balance为消费后剩余的金钱
userid sumcoin coin Balance Desc CreateTS
1001 100 24 76 a 2012-09-20 02:55:32.000
1001 76 68 8 b 2012-09-20 03:02:24.000
1001 58 37 21 c 2012-09-21 12:25:06.000
1001 41 41 0 f 2012-09-22 15:01:06.000
create table payment
(PaymentID int IDENTITY(1,1) NOT NULL,
userid int not null,
coin int not null,
PaymentTs datetime not null)
insert into payment values
(1001,100,'2012-09-20 02:50:06.000'),
(1001,50,'2012-09-21 12:17:07.000'),
(1002,100,'2012-09-22 13:31:46.000'),
(1001,20,'2012-09-22 14:51:26.000')
create table consume
(HistoryID bigint IDENTITY(1,1) NOT NULL,
userid int not null,
coin int not null,
[desc] nvarchar(255) null,
CreateTS datetime not null)insert into consume values
(1001,24,'a','2012-09-20 02:55:32.000'),
(1001,68,'b','2012-09-20 03:02:24.000'),
(1001,37,'c','2012-09-21 12:25:06.000'),
(1002,48,'d','2012-09-22 13:42:46.000'),
(1002,39,'e','2012-09-22 13:42:46.000'),
(1001,41,'f','2012-09-22 15:01:06.000')
要求得到userid为1001的用户的消费记录
sumcoin为消费前的金钱,Balance为消费后剩余的金钱
userid sumcoin coin Balance Desc CreateTS
1001 100 24 76 a 2012-09-20 02:55:32.000
1001 76 68 8 b 2012-09-20 03:02:24.000
1001 58 37 21 c 2012-09-21 12:25:06.000
1001 41 41 0 f 2012-09-22 15:01:06.000
解决方案 »
- 如何将上月的余额跟本月的奖励加到一起?生成本月总的奖励费
- 关于数据库得到用户的信息问题(求大神)
- 弱弱的问题之二
- SQL运算降低房间空置率:实现客房预订输入预订时间和离开时间,就将满足房间列表
- 请问各位有没谁做过网络图算法的啊?
- 有没有这样的SQL语句?
- 请问我用企业管理器修改表里的数据时,老报错:建列信息不足或不正确,更新影响多行?
- 再提SQL Sever存储的问题?
- 请问产生全数据库的script文件..............
- 150w数据中对标题进行模糊查询性能问题,用全文搜索速度很快,但找到记录和like查询出来的不同
- 怎样是调用的存储过程执行,但是不显示执行结果
- SQL Server 2000 备份触发器
每一条统计后存放于临时表。最后再全部Select 出来。