34,575
社区成员
发帖
与我相关
我的任务
分享
drop table ta
drop table tb
create table ta
(
User_id int ,
user_level int ,
log_datetime datetime
)
Insert into ta
Select 1 , 2 , '2013-06-26 10:54:22'
union
Select 1 , 3 , '2013-06-26 10:54:35'
union select 1 , 4 , '2013-06-26 10:54:47'
union select 1 , 5 , '2013-06-26 10:55:07'
Create table tb
(
user_id int ,
silver_use int ,
log_datetime datetime
)
Insert into tb
select 1 , 40 , '2013-06-26 10:54:22'
union select 1 , 60 , '2013-06-26 10:54:25'
union select 1 , 20 , '2013-06-26 10:54:35'
union select 1 , 30 , '2013-06-26 10:54:39'
union select 1 , 80 , '2013-06-26 10:54:47'
union select 1 , 70 , '2013-06-26 10:54:51'
union select 1 , 80 , '2013-06-26 10:55:05'
select a.User_id ,RTRIM(a.user_level) + '~' + RTRIM(b.user_level) as Level,
(Select SUM(silver_use) from tb where tb.user_id = a.User_id and tb.log_datetime >= a.log_datetime and tb.log_datetime <= b.log_datetime)
From ta a inner Join ta b on a.User_id = b.User_id and a.user_level = b.user_level - 1
create table ta
(user_id int, user_level int, log_datetime datetime)
insert into ta
select 1, 2, '2013-06-26 10:54:22' union all
select 1, 3, '2013-06-26 10:54:35' union all
select 1, 4, '2013-06-26 10:54:47' union all
select 1, 5, '2013-06-26 10:55:07'
create table tb
(user_id int, silver_use int, log_datetime datetime)
insert into tb
select 1, 40, '2013-06-26 10:54:22' union all
select 1, 60, '2013-06-26 10:54:25' union all
select 1, 20, '2013-06-26 10:54:35' union all
select 1, 30, '2013-06-26 10:54:39' union all
select 1, 80, '2013-06-26 10:54:47' union all
select 1, 70, '2013-06-26 10:54:51' union all
select 1, 80, '2013-06-26 10:55:05'
select a.user_id,
rtrim(a.user_level)+'~'+rtrim(b.user_level) 'user_level',
(select sum(silver_use) from tb c
where c.user_id=a.user_id
and c.log_datetime>a.log_datetime and c.log_datetime<=b.log_datetime) 'silver_use'
from ta a
inner join ta b on a.user_id=b.user_id and a.user_level=b.user_level-1
/*
user_id user_level silver_use
----------- ------------------------- -----------
1 2~3 80
1 3~4 110
1 4~5 150
(3 row(s) affected)
*/
with ta(user_id,user_level,log_datetime)as(
select 1,2,'2013-06-26 10:54:22' union
select 1,3,'2013-06-26 10:54:35' union
select 1,4,'2013-06-26 10:54:47' union
select 1,5,'2013-06-26 10:55:07')
,tb(user_id,silver_use,log_datetime)as(
select 1,40,'2013-06-26 10:54:22' union
select 1,60,'2013-06-26 10:54:25' union
select 1,20,'2013-06-26 10:54:35' union
select 1,30,'2013-06-26 10:54:39' union
select 1,80,'2013-06-26 10:54:47' union
select 1,70,'2013-06-26 10:54:51' union
select 1,80,'2013-06-26 10:55:05')
select a.user_id,user_level,SUM(silver_use) from (
select a.user_id,
user_level=CONVERT(varchar,a.user_level)+'~'+CONVERT(varchar,b.user_level),
a.log_datetime stime,b.log_datetime etime
from ta a,ta b where b.user_level-a.user_level=1)a
left join tb on tb.log_datetime >stime and tb.log_datetime<=etime
group by a.user_id,user_level
TB表的第一条数据应该不能算在2级里吧...应该是1~2的吧