| id | uid | adId | appId | create_time | state |
+----+----------------------------------+-------+-------+---------------------+-------+
| 19 | 1 | 20 | 2 | 2011-10-20 09:20:25 | 0 |
| 20 | 1 | 20 | 2 | 2011-10-20 09:49:02 | 0 |
| 21 | 1 | 20000 | 2 | 2011-10-20 09:49:11 | 0 |
| 14 | 42bbb623e6275f85aeaaeb929c81200b | 11 | 2 | 2011-10-14 17:28:44 | 0 |
| 15 | 42bbb623e6275f85aeaaeb929c81200b | 14 | 2 | 2011-10-14 17:30:47 | 0 |
| 16 | 42bbb623e6275f85aeaaeb929c81200b | 13 | 2 | 2011-10-17 09:50:00 | 0 |
| 17 | 42bbb623e6275f85aeaaeb929c81200b | 15 | 2 | 2011-10-17 09:50:46 | 0 |
| 18 | 42bbb623e6275f85aeaaeb929c81200b | 18 | 2 | 2011-10-17 11:40:16 | 0 |
+----+----------------------------------+-------+-------+---------------------+-------+各位大佬们,我有这样一列数据,我需要根据uid分组查询统计次数,而且create_time是每半小时才算一次有效数字,最终结果如下:appId count as 次数
2 5 不知道这样是否可以有sql完成,网上的一些方法好像可以,但是我没有成功,希望大佬们做过的指导一下,也可以加我
QQ:408409463
+----+----------------------------------+-------+-------+---------------------+-------+
| 19 | 1 | 20 | 2 | 2011-10-20 09:20:25 | 0 |
| 20 | 1 | 20 | 2 | 2011-10-20 09:49:02 | 0 |
| 21 | 1 | 20000 | 2 | 2011-10-20 09:49:11 | 0 |
| 14 | 42bbb623e6275f85aeaaeb929c81200b | 11 | 2 | 2011-10-14 17:28:44 | 0 |
| 15 | 42bbb623e6275f85aeaaeb929c81200b | 14 | 2 | 2011-10-14 17:30:47 | 0 |
| 16 | 42bbb623e6275f85aeaaeb929c81200b | 13 | 2 | 2011-10-17 09:50:00 | 0 |
| 17 | 42bbb623e6275f85aeaaeb929c81200b | 15 | 2 | 2011-10-17 09:50:46 | 0 |
| 18 | 42bbb623e6275f85aeaaeb929c81200b | 18 | 2 | 2011-10-17 11:40:16 | 0 |
+----+----------------------------------+-------+-------+---------------------+-------+各位大佬们,我有这样一列数据,我需要根据uid分组查询统计次数,而且create_time是每半小时才算一次有效数字,最终结果如下:appId count as 次数
2 5 不知道这样是否可以有sql完成,网上的一些方法好像可以,但是我没有成功,希望大佬们做过的指导一下,也可以加我
QQ:408409463
| 20 | 1 | 20 | 2 | 2011-10-20 09:49:02 | 0 |
| 21 | 1 | 20 | 2 | 2011-10-20 09:49:11 | 0 |
| 14 | 4 | 11 | 2 | 2011-10-14 17:28:44 | 0 |
| 15 | 4 | 14 | 2 | 2011-10-14 17:30:47 | 0 |
| 16 | 4 | 13 | 2 | 2011-10-17 09:50:00 | 0 |
| 17 | 4 | 15 | 2 | 2011-10-17 09:50:46 | 0 |
| 18 | 4 | 18 | 2 | 2011-10-17 11:40:16 | 0 |结果
appId | count as 次数 2 | 5
select appid,count(*) as cnt
from(
select appid,datediff(mi,create_time,getdate())/30 as mt,count(*) ct
from tb
group by appid,datediff(mi,create_time,getdate())/30
)t
group by appid
create table tb(appid int,create_time datetime)
insert into tb
select 2,'2011-10-20 09:20:25' union all
select 2,'2011-10-20 09:49:02' union all
select 2,'2011-10-20 09:49:11' union all
select 2,'2011-10-14 17:28:44' union all
select 2,'2011-10-14 17:30:47' union all
select 2,'2011-10-17 09:50:00' union all
select 2,'2011-10-17 09:50:46' union all
select 2,'2011-10-17 11:40:16'
goselect appid,count(*) as cnt
from(
select appid,datediff(mi,create_time,getdate())/30 as mt,count(*) ct
from tb
group by appid,datediff(mi,create_time,getdate())/30
)t
group by appiddrop table tb/***********appid cnt
----------- -----------
2 5(1 行受影响)
insert into tb select 19,'1',20,2,'2011-10-20 09:20:25',0
insert into tb select 20,'1',20,2,'2011-10-20 09:49:02',0
insert into tb select 21,'1',20000,2,'2011-10-20 09:49:11',0
insert into tb select 14,'42bbb623e6275f85aeaaeb929c81200b',11,2,'2011-10-14 17:28:44',0
insert into tb select 15,'42bbb623e6275f85aeaaeb929c81200b',14,2,'2011-10-14 17:30:47',0
insert into tb select 16,'42bbb623e6275f85aeaaeb929c81200b',13,2,'2011-10-17 09:50:00',0
insert into tb select 17,'42bbb623e6275f85aeaaeb929c81200b',15,2,'2011-10-17 09:50:46',0
insert into tb select 18,'42bbb623e6275f85aeaaeb929c81200b',18,2,'2011-10-17 11:40:16',0
go
select appid,count(*)ct from(
select distinct appid,convert(varchar(14),create_time,120)+'0'+ltrim(((right(convert(varchar(5),create_time,108),2)+1)/30))dt from tb
)t group by appid
/*
appid ct
----------- -----------
2 6(1 行受影响)*/
go
drop table tb这个:
| 14 | 42bbb623e6275f85aeaaeb929c81200b | 11 | 2 | 2011-10-14 17:28:44 | 0 |
| 15 | 42bbb623e6275f85aeaaeb929c81200b | 14 | 2 | 2011-10-14 17:30:47 | 0 |
应该算两次吧.
语法:
TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
说明:
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同。 SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01');
interval可是以year,hour,minue等
select appid,count(*)ct from(
select distinct appid,date(create_time)d,hour(create_time)h,MINUTE(create_time)/30 m from tb
)t group by appid