麦林 发表于 2007/11/22 17:22:15 |
create table 用户表(用户ID int,用户名 varchar(10),上次周排行位数 int,上次月排行位数 int,上次季排行位数 int,上次年排行位数 int) insert into 用户表 values(1, 'a ',1,1,3,2) insert into 用户表 values(2, 'b ',3,4,1,1) insert into 用户表 values(3, 'c ',2,5,5,5) insert into 用户表 values(4, 'd ',5,2,2,3) insert into 用户表 values(5, 'e ',4,3,4,4) create table 记录表(记录ID int,记录时间 datetime,记录类型 int,记录属性 int,用户ID int) insert into 记录表 values( 1 , '2007-5-11 14:59:11 ',1,0,1) insert into 记录表 values( 2 , '2007-5-12 14:59:11 ',2,0,2) insert into 记录表 values( 3 , '2007-5-13 14:59:11 ',2,0,1) insert into 记录表 values( 4 , '2007-6-14 14:59:11 ',0,0,1) insert into 记录表 values( 5 , '2007-6-15 14:59:11 ',0,0,1) insert into 记录表 values( 6 , '2007-6-16 14:59:11 ',0,0,3) insert into 记录表 values( 7 , '2007-6-17 14:59:11 ',2,0,1) insert into 记录表 values( 8 , '2007-6-18 14:59:11 ',1,0,1) insert into 记录表 values( 9 , '2007-6-19 14:59:11 ',2,0,3) insert into 记录表 values( 10, '2007-6-20 14:59:11 ',1,0,1) insert into 记录表 values( 11, '2007-6-21 14:59:11 ',0,0,2) insert into 记录表 values( 12, '2007-6-22 14:59:11 ',1,0,1) insert into 记录表 values( 13, '2007-6-23 14:59:11 ',0,0,1) insert into 记录表 values( 14, '2007-6-24 14:59:11 ',1,0,1) insert into 记录表 values( 15, '2007-6-25 14:59:11 ',1,0,2) insert into 记录表 values( 16, '2007-6-26 14:59:11 ',2,0,2) insert into 记录表 values( 17, '2007-7-27 14:59:11 ',1,0,3) insert into 记录表 values( 18, '2007-7-28 14:59:11 ',0,0,1) insert into 记录表 values( 19, '2007-7-29 14:59:11 ',1,0,3) insert into 记录表 values( 20, '2007-7-30 14:59:11 ',1,0,3) go select a.用户ID,a.用户名, '总共 ' + cast(t1.cnt as varchar) + '次,类型0: ' + cast(t2.cnt as varchar) + '次 类型1: ' + cast(t3.cnt as varchar) + '次 类型2: ' + cast(t4.cnt as varchar) + '次 ' 操作次数 , convert(varchar(19),t5.记录时间, 120) + ' 记录类型 ' + cast(t5.记录类型 as varchar) 最后操作,t1.cnt into temp from 用户表 a, (select 用户ID , count(*) cnt from 记录表 group by 用户ID) t1, (select 用户ID , count(*) cnt from 记录表 where 记录类型 = 0 group by 用户ID ) t2, (select 用户ID , count(*) cnt from 记录表 where 记录类型 = 1 group by 用户ID ) t3, (select 用户ID , count(*) cnt from 记录表 where 记录类型 = 2 group by 用户ID ) t4, ( select m1.* from 记录表 m1, (select 用户ID, max(记录时间) 记录时间 from 记录表 group by 用户ID) m2 where m1.用户ID = m2.用户ID and m1.记录时间 = m2.记录时间 ) t5 where a.用户ID = t1.用户ID and a.用户ID = t2.用户ID and a.用户ID = t3.用户ID and a.用户ID = t4.用户ID and a.用户ID = t5.用户ID
select m.用户ID,m.用户名,m.操作次数,m.最后操作 , 状态 = case when m.px - n.上次年排行位数 < 0 then '上升 ' when m.px - n.上次年排行位数= 0 then '平 ' when m.px - n.上次年排行位数 > 0 then '下降 ' end from (SELECT * , px=(SELECT COUNT(cnt) FROM temp WHERE cnt > a.cnt) + 1 FROM temp a) m,用户表 n where m.用户ID = n.用户ID
drop table 用户表,记录表,temp
/* 用户ID 用户名 操作次数 最后操作 状态 ------ ------ -------------------------------------- ----------------------------- ---- 1 a 总共11次,类型0:4次 类型1:5次 类型2:2次 2007-07-28 14:59:11 记录类型0 上升 2 b 总共4次,类型0:1次 类型1:1次 类型2:2次 2007-06-26 14:59:11 记录类型2 下降 3 c 总共5次,类型0:1次 类型1:3次 类型2:1次 2007-07-30 14:59:11 记录类型1 上升
（所影响的行数为 3 行） */
|
.: 
