tab表中原始数据
id datetime vid nid
1 06-7-1 30 1
2 06-6-5 40 1
3 06-7-2 67 2
4 06-7-3 10 1
5 06-6-8 82 2
6 06-7-7 20 1
7 06-7-7 20 2
现在列出nid为1的用户从当前月算起到当前月-12的各月分排名希望的结果 月份 排名 总访问
05-8 0 0
05-9 0 0
05-10 0 0
05-11 0 0
05-12 0 0
06-1 0 0
06-2 0 0
06-3 0 0
06-4 0 0
06-5 0 0
06-6 2 40
06-7 1 60先谢过了~
id datetime vid nid
1 06-7-1 30 1
2 06-6-5 40 1
3 06-7-2 67 2
4 06-7-3 10 1
5 06-6-8 82 2
6 06-7-7 20 1
7 06-7-7 20 2
现在列出nid为1的用户从当前月算起到当前月-12的各月分排名希望的结果 月份 排名 总访问
05-8 0 0
05-9 0 0
05-10 0 0
05-11 0 0
05-12 0 0
06-1 0 0
06-2 0 0
06-3 0 0
06-4 0 0
06-5 0 0
06-6 2 40
06-7 1 60先谢过了~
drop table tb
gocreate table tb
(
id int,
[datetime] datetime,
vid int,
nid int
)insert into tb(id, [datetime] ,vid, nid) values(1 ,'06-7-1', 30, 1)
insert into tb(id, [datetime] ,vid, nid) values(2 ,'06-6-5', 40, 1)
insert into tb(id, [datetime] ,vid, nid) values(3 ,'06-7-2', 67, 2)
insert into tb(id, [datetime] ,vid, nid) values(4 ,'06-7-3', 10, 1)
insert into tb(id, [datetime] ,vid, nid) values(5 ,'06-6-8', 82, 2)
insert into tb(id, [datetime] ,vid, nid) values(6 ,'06-7-7', 20, 1)
insert into tb(id, [datetime] ,vid, nid) values(7 ,'06-7-7', 20, 2)--建立一个时间表
if object_id('pubs..mydt') is not null
drop table mydt
go
create table mydt(mydt char(7))declare @dt as datetime
declare @i as int
set @dt = '2006-07-01'
set @i = 1
while @i <= 12
begin
insert into mydt(mydt) values(convert(varchar(7),@dt,120))
set @dt = dateadd(Month , -1 , @dt)
set @i = @i + 1
end
select nid,convert(varchar(7),[datetime],120) as mydt , sum(vid) as vid
into test
from tb
group by nid,convert(varchar(7),[datetime],120)select a.mydt as 月份 , isnull(b.px ,0) as 排名 , isnull(b.vid ,0) as 总访问
from mydt a
left join (select nid , mydt , vid , px=(select count(1) from test where mydt = t.mydt and vid > t.vid) + 1 from test t) b
on a.mydt = b.mydt
order by a.mydtdrop table tb
drop table mydt
drop table test月份 排名 总访问
------- ----------- -----------
2005-08 0 0
2005-09 0 0
2005-10 0 0
2005-11 0 0
2005-12 0 0
2006-01 0 0
2006-02 0 0
2006-03 0 0
2006-04 0 0
2006-05 0 0
2006-06 2 40
2006-06 1 82
2006-07 2 60
2006-07 1 87(所影响的行数为 14 行)
if object_id('pubs..tb') is not null
drop table tb
go
if object_id('pubs..test') is not null
drop table test
gocreate table tb
(
id int,
[datetime] datetime,
vid int,
nid int
)insert into tb(id, [datetime] ,vid, nid) values(1 ,'06-7-1', 30, 1)
insert into tb(id, [datetime] ,vid, nid) values(2 ,'06-6-5', 40, 1)
insert into tb(id, [datetime] ,vid, nid) values(3 ,'06-7-2', 67, 2)
insert into tb(id, [datetime] ,vid, nid) values(4 ,'06-7-3', 10, 1)
insert into tb(id, [datetime] ,vid, nid) values(5 ,'06-6-8', 82, 2)
insert into tb(id, [datetime] ,vid, nid) values(6 ,'06-7-7', 20, 1)
insert into tb(id, [datetime] ,vid, nid) values(7 ,'06-7-7', 20, 2)--建立一个时间表
if object_id('pubs..mydt') is not null
drop table mydt
go
create table mydt(mydt char(7))declare @dt as datetime
declare @i as int
set @dt = '2006-07-01'
set @i = 1
while @i <= 12
begin
insert into mydt(mydt) values(convert(varchar(7),@dt,120))
set @dt = dateadd(Month , -1 , @dt)
set @i = @i + 1
end
select nid,convert(varchar(7),[datetime],120) as mydt , sum(vid) as vid
into test
from tb
group by nid,convert(varchar(7),[datetime],120)select a.mydt as 月份 , isnull(b.px ,0) as 排名 , isnull(b.vid ,0) as 总访问
from mydt a
left join (select nid , mydt , vid , px=(select count(1) from test where mydt = t.mydt and vid > t.vid) + 1 from test t) b
on a.mydt = b.mydt and b.nid = 1
order by a.mydtdrop table tb
drop table mydt
drop table test
月份 排名 总访问
------- ----------- -----------
2005-08 0 0
2005-09 0 0
2005-10 0 0
2005-11 0 0
2005-12 0 0
2006-01 0 0
2006-02 0 0
2006-03 0 0
2006-04 0 0
2006-05 0 0
2006-06 2 40
2006-07 2 60(所影响的行数为 12 行)
先谢谢您!
您上面写的这些我基本能看明白(不排除装懂的可能),但是我从来没用过存储过程和临时表(主要是不会),我是个asp初学者,刚从access转到sql,请问这些怎么在asp程序里实现啊!
难道我要再建个月份表mydt和临时表test吗,这里面的test是个临时表吧?能不能用一条语句只读出tab里涉汲的月份,其它的我可以在程序里实现。
月份表mydt和临时表test