我先发下这个表的结构:
ID UserID QiHao ZJmoney ZJtime
1 aaaaaa 20110219001 100 2011-02-19 13:00:00
2 aaaaaa 20110219002 50 2011-02-19 13:01:00
2 aaaaaa 20110219003 30 2011-02-19 13:05:00
4 bbbbbb 20110219001 50 2011-02-19 13:10:00
5 bbbbbb 20110219002 50 2011-02-19 13:20:00
6 bbbbbb 20110219002 50 2011-02-19 13:24:00
7 bbbbbb 20110219002 50 2011-02-19 13:25:00
8 cccccc 20110219001 50 2011-02-19 13:32:00
9 cccccc 20110219003 50 2011-02-19 13:35:00
10 dddddd 20110219001 50 2011-02-19 13:40:00其中 UserID使用用户名,QiHao是中奖期号,ZJmoney是中奖金额,ZJtime中奖时间现在要根据这个表制作一个排行榜,这个排行榜是用户中奖金额排行榜,因为每个用户可能有多期都中奖,这个就需要计算这个用户的总金额再进行排行,这个排行榜又分今日排行,周排行,月排行,总排行!我的分不多,希望大大们帮帮我!
ID UserID QiHao ZJmoney ZJtime
1 aaaaaa 20110219001 100 2011-02-19 13:00:00
2 aaaaaa 20110219002 50 2011-02-19 13:01:00
2 aaaaaa 20110219003 30 2011-02-19 13:05:00
4 bbbbbb 20110219001 50 2011-02-19 13:10:00
5 bbbbbb 20110219002 50 2011-02-19 13:20:00
6 bbbbbb 20110219002 50 2011-02-19 13:24:00
7 bbbbbb 20110219002 50 2011-02-19 13:25:00
8 cccccc 20110219001 50 2011-02-19 13:32:00
9 cccccc 20110219003 50 2011-02-19 13:35:00
10 dddddd 20110219001 50 2011-02-19 13:40:00其中 UserID使用用户名,QiHao是中奖期号,ZJmoney是中奖金额,ZJtime中奖时间现在要根据这个表制作一个排行榜,这个排行榜是用户中奖金额排行榜,因为每个用户可能有多期都中奖,这个就需要计算这个用户的总金额再进行排行,这个排行榜又分今日排行,周排行,月排行,总排行!我的分不多,希望大大们帮帮我!
--
select row_number() over(order by jiangjin) as no,*
from (select UserID,sum(ZJmoney) as jiangjin
where convert(varchar(10),ZJtime,120)=convert(varchar(10),getdate(),120)
----周,月。。条件自己可以写
from tb group byUserID ) a
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ID int, UserID varchar(8), QiHao bigint, ZJmoney int, ZJtime datetime)
insert into #
select 1, 'aaaaaa', 20110219001, 100, '2011-02-19 13:00:00' union all
select 2, 'aaaaaa', 20110219002, 50, '2011-02-19 13:01:00' union all
select 2, 'aaaaaa', 20110219003, 30, '2011-02-19 13:05:00' union all
select 4, 'bbbbbb', 20110219001, 50, '2011-02-19 13:10:00' union all
select 5, 'bbbbbb', 20110219002, 50, '2011-02-19 13:20:00' union all
select 6, 'bbbbbb', 20110219002, 50, '2011-02-19 13:24:00' union all
select 7, 'bbbbbb', 20110219002, 50, '2011-02-19 13:25:00' union all
select 8, 'cccccc', 20110219001, 50, '2011-02-19 13:32:00' union all
select 9, 'cccccc', 20110219003, 50, '2011-02-19 13:35:00' union all
select 10, 'dddddd', 20110219001, 50, '2011-02-19 13:40:00'-- total
select pm = row_number()over(order by sum(ZJmoney) desc), UserID from # group by UserID-- today
select pm = row_number()over(order by sum(ZJmoney) desc), UserID from # where datediff(day,ZJtime,getdate())=0 group by UserID-- week
select pm = row_number()over(order by sum(ZJmoney) desc), UserID from # where datediff(week,ZJtime,getdate())=0 group by UserID-- month
select pm = row_number()over(order by sum(ZJmoney) desc), UserID from # where datediff(month,ZJtime,getdate())=0 group by UserID
insert into tb select 1,'aaaaaa','20110219001',100,'2011-02-19 13:00:00'
insert into tb select 2,'aaaaaa','20110219002',50,'2011-02-19 13:01:00'
insert into tb select 3,'aaaaaa','20110219003',30,'2011-02-19 13:05:00'
insert into tb select 4,'bbbbbb','20110219001',50,'2011-02-19 13:10:00'
insert into tb select 5,'bbbbbb','20110219002',50,'2011-02-19 13:20:00'
insert into tb select 6,'bbbbbb','20110219002',50,'2011-02-19 13:24:00'
insert into tb select 7,'bbbbbb','20110219002',50,'2011-02-19 13:25:00'
insert into tb select 8,'cccccc','20110219001',50,'2011-02-19 13:32:00'
insert into tb select 9,'cccccc','20110219003',50,'2011-02-19 13:35:00'
insert into tb select 10,'dddddd','20110219001',50,'2011-02-19 13:40:00'
insert into tb select 11,'aaaaaa','20110219001',100,'2011-02-21 13:00:00'
insert into tb select 12,'aaaaaa','20110219002',50,'2011-02-21 13:01:00'
insert into tb select 13,'aaaaaa','20110219003',30,'2011-02-22 13:05:00'
insert into tb select 14,'bbbbbb','20110219001',50,'2011-02-22 13:10:00'
insert into tb select 15,'bbbbbb','20110219002',50,'2011-02-22 13:20:00'
insert into tb select 16,'bbbbbb','20110219002',50,'2011-02-25 13:24:00'
insert into tb select 17,'bbbbbb','20110219002',50,'2011-02-25 13:25:00'
insert into tb select 18,'cccccc','20110219001',50,'2011-02-25 13:32:00'
insert into tb select 19,'cccccc','20110219003',50,'2011-02-25 13:35:00'
insert into tb select 20,'dddddd','20110219001',50,'2011-02-26 13:40:00'
go
--日
select userid,sum(zjmoney),convert(varchar(10),zjtime,120) as zjtime from tb group by userid,convert(varchar(10),zjtime,120) order by 2 desc
--周
select userid,sum(zjmoney),datepart(wk,zjtime)as zjtime from tb group by userid,datepart(wk,zjtime) order by 2 desc
--月
select userid,sum(zjmoney),convert(varchar(7),zjtime,120) as zjtime from tb group by userid,convert(varchar(7),zjtime,120) order by 2 desc
--总
select userid,sum(zjmoney) from tb group by userid order by 2 desc
go
drop table tb
/*
userid zjtime
---------- ----------- ----------
bbbbbb 200 2011-02-19
aaaaaa 180 2011-02-19
aaaaaa 150 2011-02-21
cccccc 100 2011-02-19
bbbbbb 100 2011-02-22
bbbbbb 100 2011-02-25
cccccc 100 2011-02-25
dddddd 50 2011-02-26
dddddd 50 2011-02-19
aaaaaa 30 2011-02-22(10 行受影响)userid zjtime
---------- ----------- -----------
bbbbbb 200 8
bbbbbb 200 9
aaaaaa 180 8
aaaaaa 180 9
cccccc 100 8
cccccc 100 9
dddddd 50 9
dddddd 50 8(8 行受影响)userid zjtime
---------- ----------- -------
bbbbbb 400 2011-02
aaaaaa 360 2011-02
cccccc 200 2011-02
dddddd 100 2011-02(4 行受影响)userid
---------- -----------
bbbbbb 400
aaaaaa 360
cccccc 200
dddddd 100(4 行受影响)*/
[code=SQ]create table tb(ID int,UserID nvarchar(10),QiHao nvarchar(20),ZJmoney int,ZJtime datetime)
insert into tb select 1,'aaaaaa','20110219001',100,'2011-02-19 13:00:00'
insert into tb select 2,'aaaaaa','20110219002',50,'2011-02-19 13:01:00'
insert into tb select 3,'aaaaaa','20110219003',30,'2011-02-19 13:05:00'
insert into tb select 4,'bbbbbb','20110219001',50,'2011-02-19 13:10:00'
insert into tb select 5,'bbbbbb','20110219002',50,'2011-02-19 13:20:00'
insert into tb select 6,'bbbbbb','20110219002',50,'2011-02-19 13:24:00'
insert into tb select 7,'bbbbbb','20110219002',50,'2011-02-19 13:25:00'
insert into tb select 8,'cccccc','20110219001',50,'2011-02-19 13:32:00'
insert into tb select 9,'cccccc','20110219003',50,'2011-02-19 13:35:00'
insert into tb select 10,'dddddd','20110219001',50,'2011-02-19 13:40:00'
insert into tb select 11,'aaaaaa','20110219001',100,'2011-02-21 13:00:00'
insert into tb select 12,'aaaaaa','20110219002',50,'2011-02-21 13:01:00'
insert into tb select 13,'aaaaaa','20110219003',30,'2011-02-22 13:05:00'
insert into tb select 14,'bbbbbb','20110219001',50,'2011-02-22 13:10:00'
insert into tb select 15,'bbbbbb','20110219002',50,'2011-02-22 13:20:00'
insert into tb select 16,'bbbbbb','20110219002',50,'2011-02-25 13:24:00'
insert into tb select 17,'bbbbbb','20110219002',50,'2011-02-25 13:25:00'
insert into tb select 18,'cccccc','20110219001',50,'2011-02-25 13:32:00'
insert into tb select 19,'cccccc','20110219003',50,'2011-02-25 13:35:00'
insert into tb select 20,'dddddd','20110219001',50,'2011-02-26 13:40:00'
go
--日
select userid,sum(zjmoney)je,convert(varchar(10),zjtime,120) as zjtime from tb group by userid,convert(varchar(10),zjtime,120) order by 3,2 desc
--周
select userid,sum(zjmoney)je,datepart(wk,zjtime)as zjtime from tb group by userid,datepart(wk,zjtime) order by 3,2 desc
--月
select userid,sum(zjmoney)je,convert(varchar(7),zjtime,120) as zjtime from tb group by userid,convert(varchar(7),zjtime,120) order by 3,2 desc
--总
select userid,sum(zjmoney)je from tb group by userid order by 2 desc
go
drop table tb
/*
userid je zjtime
---------- ----------- ----------
bbbbbb 200 2011-02-19
aaaaaa 180 2011-02-19
cccccc 100 2011-02-19
dddddd 50 2011-02-19
aaaaaa 150 2011-02-21
bbbbbb 100 2011-02-22
aaaaaa 30 2011-02-22
bbbbbb 100 2011-02-25
cccccc 100 2011-02-25
dddddd 50 2011-02-26(10 行受影响)userid je zjtime
---------- ----------- -----------
bbbbbb 200 8
aaaaaa 180 8
cccccc 100 8
dddddd 50 8
bbbbbb 200 9
aaaaaa 180 9
cccccc 100 9
dddddd 50 9(8 行受影响)userid je zjtime
---------- ----------- -------
bbbbbb 400 2011-02
aaaaaa 360 2011-02
cccccc 200 2011-02
dddddd 100 2011-02(4 行受影响)userid je
---------- -----------
bbbbbb 400
aaaaaa 360
cccccc 200
dddddd 100(4 行受影响)*/[/code]