根据时间段分组统计

水妹妹 2013-07-09 09:41:16
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
...................................
...全文
97 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
hackervip1988 2013-07-09
  • 打赏
  • 举报
回复
学习啦 。。。。
hackervip1988 2013-07-09
  • 打赏
  • 举报
回复

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
水妹妹 2013-07-09
  • 打赏
  • 举报
回复
引用 1 楼 sc273607742 的回复:

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的吧
是的 谢谢
唐诗三百首 2013-07-09
  • 打赏
  • 举报
回复

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)
*/
Andy__Huang 2013-07-09
  • 打赏
  • 举报
回复
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
哥眼神纯洁不 2013-07-09
  • 打赏
  • 举报
回复

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

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧