求解一条sql语句 本帖最后由 qq35610 于 2011-08-21 15:55:37 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 total 列为连续登录的天数,当然判断规则你要用准确的 24 小时来判断的话,用其它的函数即可:mysql> select * from tb4;+------+-------------------+| id | loginTime |+------+-------------------+| 1 | 2011-8-1 14:59:59 || 2 | 2011-8-2 14:58:59 || 3 | 2011-8-3 14:57:59 || 4 | 2011-8-4 14:56:59 || 5 | 2011-8-6 14:55:59 || 6 | 2011-8-7 14:54:59 |+------+-------------------+6 rows in set (0.00 sec)mysql> set @last := 0;Query OK, 0 rows affected (0.00 sec)mysql> set @days := 1;Query OK, 0 rows affected (0.00 sec)mysql> select ifnull(@last, @days := @days + 1), if(dayofyear(loginTime) - dayofyear(@last) = 1, @days := @days + 1, @days := 1) as 'total', @last := loginTime -> from tb4;+-----------------------------------+-------+--------------------+| ifnull(@last, @days := @days + 1) | total | @last := loginTime |+-----------------------------------+-------+--------------------+| 0 | 1 | 2011-8-1 14:59:59 || 2011 | 2 | 2011-8-2 14:58:59 || 2011 | 3 | 2011-8-3 14:57:59 || 2011 | 4 | 2011-8-4 14:56:59 || 2011 | 1 | 2011-8-6 14:55:59 || 2011 | 2 | 2011-8-7 14:54:59 |+-----------------------------------+-------+--------------------+6 rows in set, 1 warning (0.00 sec)mysql> 表记录如上,id为自动递增,希望统计出,同一个userid连续的数目的最大值,是否超过了7条,连续的规则是,同一个userid的情况下,上一条记录和下一条记录之间times字段的差值,不超过24小时数据库mysql,版本5.1 这个考虑到了针对相同userid的符合条件的数目的判断吗? 同一个userid连续的数目的最大值,是否超过了7条,连续的规则是,同一个userid的情况下,上一条记录和下一条记录之间times字段的差值,不超过24小时 http://www.iteye.com/problems/70397,我已经无语了,看这个吧 说明一下你希望这个SQL语句产生的结果是什么样?楼主与其花这么多时间浪费,不如耐心参考一下这个。 参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html 再说一遍规则,同一个userid连续的数目的最大值,是否超过了7条,连续的规则是,同一个userid的情况下,上一条记录和下一条记录之间times字段的差值,不超过24小时,返回同一个userid连续的数目的最大值就行了 id userid times1 1 2011-8-1 14:59:592 1 2011-8-2 14:58:593 1 2011-8-3 14:57:594 1 2011-8-4 14:56:595 1 2011-8-6 14:55:596 1 2011-8-7 14:54:597 2 2011-8-6 14:55:598 2 2011-8-7 14:54:59针对上面的数据,最大的连续记录数,应该是1 1 2011-8-1 14:59:592 1 2011-8-2 14:58:593 1 2011-8-3 14:57:594 1 2011-8-4 14:56:59这四条,所以返回的值应该是4 提供一下你的create table / insert into 语句,这样别人可以直接使用你的数据进行测试。 SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `t`-- ----------------------------DROP TABLE IF EXISTS `t`;CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` int(11) NOT NULL, `logintime` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;-- ------------------------------ Records of t-- ----------------------------INSERT INTO `t` VALUES ('1', '1', '2011-08-01 14:59:59');INSERT INTO `t` VALUES ('2', '1', '2011-08-02 14:58:59');INSERT INTO `t` VALUES ('3', '1', '2011-08-03 14:57:59');INSERT INTO `t` VALUES ('4', '1', '2011-08-04 14:56:59');INSERT INTO `t` VALUES ('5', '1', '2011-08-06 14:55:59');INSERT INTO `t` VALUES ('6', '1', '2011-08-07 14:54:59'); 看来让楼主提供点东西是难上加难。你16楼的提供的东西和你项楼的记录根本不一样。 还是得自己准备。提问的都提得这么牛!mysql> select * from qq35610;+------+--------+---------------------+| id | userid | times |+------+--------+---------------------+| 1 | 1 | 2011-08-01 14:59:59 || 2 | 1 | 2011-08-02 14:58:59 || 3 | 1 | 2011-08-03 14:57:59 || 4 | 1 | 2011-08-04 14:56:59 || 5 | 1 | 2011-08-06 14:55:59 || 6 | 1 | 2011-08-07 14:54:59 || 7 | 2 | 2011-08-06 14:55:59 || 8 | 2 | 2011-08-07 14:54:59 |+------+--------+---------------------+8 rows in set (0.00 sec)mysql>mysql> set @i=0;set @u=0;set @d='1911-08-01';Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> select max(n) from( -> select @i:=if( userid!=@u and times<=@d+interval 24 hour, @i+1,1) as n,@d:=times,@u=userid -> from qq35610 -> order by userid,times -> ) t;+--------+| max(n) |+--------+| 4 |+--------+1 row in set (0.00 sec)mysql> 如何优化联合查询这样两个表? 一个复杂SQL查询语句,冰天雪地跪求解答 Mysql在什么情况下会丢失数据库文件 Linux上的mysql4.0.18,用jdbc连接,总是报错: Unsupported character encoding 'UTF-8',怎么办啦 数据库连接不上,帮忙看看!谢谢 为什么mysql里面无法输入中文? 请问mysql是否一定要装载c盘 “delete from log;” ,不能执行 为啥? LOAD DATA FILE为什么每次只导入一半的记录? 数据库 mysql blob 字段下载下来后都是40K 求按月分组统计数据的sql语句。在线等
mysql> select * from tb4;
+------+-------------------+
| id | loginTime |
+------+-------------------+
| 1 | 2011-8-1 14:59:59 |
| 2 | 2011-8-2 14:58:59 |
| 3 | 2011-8-3 14:57:59 |
| 4 | 2011-8-4 14:56:59 |
| 5 | 2011-8-6 14:55:59 |
| 6 | 2011-8-7 14:54:59 |
+------+-------------------+
6 rows in set (0.00 sec)mysql> set @last := 0;
Query OK, 0 rows affected (0.00 sec)mysql> set @days := 1;
Query OK, 0 rows affected (0.00 sec)mysql> select ifnull(@last, @days := @days + 1), if(dayofyear(loginTime) - dayo
fyear(@last) = 1, @days := @days + 1, @days := 1) as 'total', @last := loginTime -> from tb4;
+-----------------------------------+-------+--------------------+
| ifnull(@last, @days := @days + 1) | total | @last := loginTime |
+-----------------------------------+-------+--------------------+
| 0 | 1 | 2011-8-1 14:59:59 |
| 2011 | 2 | 2011-8-2 14:58:59 |
| 2011 | 3 | 2011-8-3 14:57:59 |
| 2011 | 4 | 2011-8-4 14:56:59 |
| 2011 | 1 | 2011-8-6 14:55:59 |
| 2011 | 2 | 2011-8-7 14:54:59 |
+-----------------------------------+-------+--------------------+
6 rows in set, 1 warning (0.00 sec)mysql>
数据库mysql,版本5.1
这个考虑到了针对相同userid的符合条件的数目的判断吗?
同一个userid连续的数目的最大值,是否超过了7条,连续的规则是,同一个userid的情况下,上一条记录和下一条记录之间times字段的差值,不超过24小时
1 1 2011-8-1 14:59:59
2 1 2011-8-2 14:58:59
3 1 2011-8-3 14:57:59
4 1 2011-8-4 14:56:59
5 1 2011-8-6 14:55:59
6 1 2011-8-7 14:54:59
7 2 2011-8-6 14:55:59
8 2 2011-8-7 14:54:59针对上面的数据,最大的连续记录数,应该是
1 1 2011-8-1 14:59:59
2 1 2011-8-2 14:58:59
3 1 2011-8-3 14:57:59
4 1 2011-8-4 14:56:59
这四条,所以返回的值应该是4
-- Table structure for `t`
-- ----------------------------
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`logintime` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;-- ----------------------------
-- Records of t
-- ----------------------------
INSERT INTO `t` VALUES ('1', '1', '2011-08-01 14:59:59');
INSERT INTO `t` VALUES ('2', '1', '2011-08-02 14:58:59');
INSERT INTO `t` VALUES ('3', '1', '2011-08-03 14:57:59');
INSERT INTO `t` VALUES ('4', '1', '2011-08-04 14:56:59');
INSERT INTO `t` VALUES ('5', '1', '2011-08-06 14:55:59');
INSERT INTO `t` VALUES ('6', '1', '2011-08-07 14:54:59');
+------+--------+---------------------+
| id | userid | times |
+------+--------+---------------------+
| 1 | 1 | 2011-08-01 14:59:59 |
| 2 | 1 | 2011-08-02 14:58:59 |
| 3 | 1 | 2011-08-03 14:57:59 |
| 4 | 1 | 2011-08-04 14:56:59 |
| 5 | 1 | 2011-08-06 14:55:59 |
| 6 | 1 | 2011-08-07 14:54:59 |
| 7 | 2 | 2011-08-06 14:55:59 |
| 8 | 2 | 2011-08-07 14:54:59 |
+------+--------+---------------------+
8 rows in set (0.00 sec)mysql>
mysql> set @i=0;set @u=0;set @d='1911-08-01';
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> select max(n) from(
-> select @i:=if( userid!=@u and times<=@d+interval 24 hour, @i+1,1) as n,@d:=times,@u=userid
-> from qq35610
-> order by userid,times
-> ) t;
+--------+
| max(n) |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)mysql>