根据时间段分组统计

Question

ta(用户等级日志表)
user_id user_level log_datetime
1       2          2013-06-26 10:54:22
1       3          2013-06-26 10:54:35
1       4          2013-06-26 10:54:47
1       5          2013-06-26 10:55:07
......................................
tb(用户银币使用表)
user_id  silver_use  log_datetime
1        40          2013-06-26 10:54:22
1        60          2013-06-26 10:54:25
1        20          2013-06-26 10:54:35
1        30          2013-06-26 10:54:39
1        80          2013-06-26 10:54:47
1        70          2013-06-26 10:54:51
1        80          2013-06-26 10:55:05
........................................
统计用户在升级过程中各消耗多少银币 如
user_id user_level  silver_use
1       2~3         120
1       3~4         110
1       4~5         150
...................................

Answer 1

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的吧

Answer 2

select a.user_id, '2~3' as user_level,sum(b.silver_use) as silver_use
from ta a,tb b 
where a.user_level>=2 and a.user_level <=3 and log_datetime in (select log_datetime from ta where user_level>=2 and user_level <=3)
group by a.user_id
union all 
select a.user_id, '3~4' as user_level,sum(b.silver_use) as silver_use
from ta a,tb b 
where a.user_level>3 and a.user_level <=4 and log_datetime in (select log_datetime from ta where user_level>3 and user_level <=4)
group by a.user_id
union all
select a.user_id, '4~5' as user_level,sum(b.silver_use) as silver_use
from ta a,tb b 
where a.user_level>=4 and a.user_level <=5 and log_datetime in (select log_datetime from ta where user_level>=4 and user_level <=5)
group by a.user_id

Answer 3

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)
*/

Answer 4

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