mysql查询连续次数_Mysql如何查询连续的时间次数

最新推荐文章于 2024-08-29 04:09:00 发布
最新推荐文章于 2024-08-29 04:09:00 发布
阅读量1.2k 收藏
点赞数
文章标签： mysql查询连续次数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_35462903/article/details/113614420
版权

在网上看到一道有意思的题目，大意是如何在mysql查询连续的时间内登录的次数。原文链接：

首先建表，填充测试数据：

CREATE TABLE `tmysql_test_lianxu_3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`uid` int(11) DEFAULT NULL,

`sts` datetime DEFAULT NULL COMMENT '登录时间',

`ets` datetime DEFAULT NULL COMMENT '离线时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

测试数据为：

INSERT INTO `tmysql_test_lianxu_3` VALUES (1, 1, '2014-1-1 21:00:00', '2014-1-2 07:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (2, 1, '2014-1-2 15:37:57', '2014-1-2 21:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (3, 2, '2014-1-1 09:00:00', '2014-1-1 15:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (4, 2, '2014-1-2 09:00:00', '2014-2-1 16:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (5, 1, '2014-1-4 10:00:00', '2014-1-4 18:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (6, 1, '2014-1-5 12:00:00', '2014-1-5 13:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (7, 2, '2014-1-10 00:00:00', '2014-1-10 06:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (8, 2, '2014-1-11 13:00:00', '2014-1-11 18:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (10, 2, '2014-1-12 12:00:00', '2014-1-12 18:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (11, 1, '2014-1-8 06:00:00', '2014-1-8 16:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (12, 2, '2014-1-11 21:00:00', '2014-1-12 06:00:00');

在Oracle中可以使用row_number搞定，mysql中怎么做呢？

可以参考链接：

首先看原文中给出的答案：

SELECT uid, days, COUNT(*) AS num

FROM (SELECT uid,

@cont_day :=

(CASE

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt) = 1) THEN

(@cont_day + 1)

ELSE

1

END) AS days,

(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,

@last_uid := uid,

@last_dt := login_dt

FROM (SELECT uid, DATE(sts) AS login_dt

FROM tmysql_test_lianxu_3

ORDER BY uid, sts) AS t,

(SELECT @last_uid := '',

@last_dt := '',

@cont_ix := 0,

@cont_day := 0) AS t1) AS t2

GROUP BY uid, days;

也是使用了mysql模拟oracle的row_number函数。

运行结果是:

8c7066dbeeb21a7a06b2889552167953.png

我看了半天发现结果好像不是我想要的，我想要的是要有开始时间，结束时间之类的。

看下中间表再说：

SELECT uid,

@cont_day :=

(CASE

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN

(@cont_day + 1)

ELSE

1

END) AS days,

(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,

@last_uid := uid,

@last_dt := login_dt login_day

FROM (SELECT uid, DATE(sts) AS login_dt

FROM tmysql_test_lianxu_3

ORDER BY uid, sts) AS t,

(SELECT @last_uid := '',

@last_dt := '',

@cont_ix := 0,

@cont_day := 0) AS t1

结果为：

ba30d360bc5f9de2da55e0300298a168.png

看了下可以这么做，连续日期取最大的days,开始时间，结束时间去login_day，而是这样写了：

SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date

FROM (SELECT uid,

@cont_day :=

(CASE

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN

(@cont_day + 1)

ELSE

1

END) AS days,

(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,

@last_uid := uid,

@last_dt := login_dt login_day

FROM (SELECT uid, DATE(sts) AS login_dt

FROM tmysql_test_lianxu_3

ORDER BY uid, sts) AS t,

(SELECT @last_uid := '',

@last_dt := '',

@cont_ix := 0,

@cont_day := 0) AS t1) AS t2

GROUP BY uid, cont_ix;

结果是：

ba09f2f505d995c134ec7a28bb21b654.png

这里存在的问题是：表里面的的sts登录时间不能有2条uid相同时间在同一天内。

解决方法是:在case中添加一个<1 的判断条件

SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date

FROM (SELECT uid,

@cont_day :=

(CASE

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN

(@cont_day + 1)

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)<1) THEN

(@cont_day + 0)

ELSE

1

END) AS days,

(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,

@last_uid := uid,

@last_dt := login_dt login_day

FROM (SELECT uid, DATE(sts) AS login_dt

FROM tmysql_test_lianxu_3

ORDER BY uid, sts) AS t,

(SELECT @last_uid := '',

@last_dt := '',

@cont_ix := 0,

@cont_day := 0) AS t1) AS t2

GROUP BY uid, cont_ix;

存在的问题：

时间sts的时分秒不见了。

--------------------------------------------Oracle可以这样做-------------------------------------------------

create table TSQL_TEST_LIANXU_4

(

ID NUMBER(4) not null,

U_ID NUMBER(4),

STS TIMESTAMP(6),

ETS TIMESTAMP(6)

);

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (1, 1, to_timestamp('01-01-2014 21:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('02-01-2014 07:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (2, 1, to_timestamp('02-01-2014 15:37:57.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('02-01-2014 21:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (3, 2, to_timestamp('01-01-2014 09:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('01-01-2014 15:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (4, 2, to_timestamp('02-01-2014 09:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('01-02-2014 16:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (5, 1, to_timestamp('04-01-2014 10:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('04-01-2014 18:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (6, 1, to_timestamp('05-01-2014 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('05-01-2014 13:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (7, 2, to_timestamp('10-01-2014 00:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('10-01-2014 06:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (8, 2, to_timestamp('11-01-2014 13:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('11-01-2014 18:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (10, 2, to_timestamp('12-01-2014 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('12-01-2014 18:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (11, 1, to_timestamp('08-01-2014 06:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('08-01-2014 16:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

本来想使用row_number的，结果没搞定。

select t.u_id,

to_char(MIN(t.sts), 'yyyy-mm-dd') start_date,

to_char(MAX(t.sts), 'yyyy-mm-dd') end_date,

trunc(MAX(t.sts)) - trunc(MIN(t.sts)) + 1

from (select z.u_id, z.sts, trunc(z.sts) - trunc(z.min_days) - rownum rn

from (select (select min(sts) from tsql_test_lianxu_4) min_days,

t.*

FROM tsql_test_lianxu_4 t

order by u_id, sts) z) t

group by u_id, rn

order by 1, 2

Oracle中这样查询是有问题的，就是uid相同sts在同一天的记录不能有2条，

Oracle另一种方法:

SELECT u_id, MIN(sts) AS STARTDATE, MAX(sts), COUNT(u_id) AS ENDNUM

FROM (SELECT A.u_id,

to_date(to_char(A.sts, 'yyyy-mm-dd'), 'yyyy-mm-dd') sts,

to_date(to_char(A.sts, 'yyyy-mm-dd'), 'yyyy-mm-dd') - ROWNUM AS GNUM

FROM (SELECT *

FROM tsql_test_lianxu_4

ORDER BY u_id,sts) A)

GROUP BY u_id, GNUM

ORDER BY u_id, MIN(sts)

缺点：uid相同sts在同一天的记录不能有2条

欢迎各位留下更好的查询SQL,如本文中的SQL有问题也请指出，谢谢。

全文完。

徐可行
关注
mysql计算连续次数_mysql一个连续日期（天）次数的统计
weixin_42228796的博客
01-19 298
首先声明参考：http://www.oschina.net/question/573517_118821表定义CREATE TABLE `date_add` (`id` int(11) NOT NULL AUTO_INCREMENT,`uid` int(11) NOT NULL DEFAULT '0',`date` date NOT NULL,PRIMARY KEY (`id`),UNIQUE K...
mysql计算连续天数，mysql连续登录天数，连续天数统计
06-10
NULL 博文链接：https://fanshuyao.iteye.com/blog/2341455
mysql 判断连续数据
最新发布
weixin_36914800的博客
08-29 73
我整理的一些关于【MySQL】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/yOSbkRMySQL 判断连续数据的探讨 在数据库管理中，尤其是在MySQL中，我们常常需要处理分布不均或者不连续的数据。例如，在某些业务场景中，我们可能希望判断某些数值是否为连续数据。本文将通过...
mysql如何查询数据出现的次数
houhow的专栏
10-27 2775
在mysql中，可以利用select语句配合group by和count查询数据出现的次数，count能够返回检索数据的数目，语法为“select 列名,count(*) as count from 表名 group by 列名”。count函数是用来统计表中或数组中记录的一个函数，count(*) 它返回检索行的数目， 不论其是否包含 NULL值。SELECT 从一个表中检索，而不检索其它的列，并且没有 WHERE子句时， COUNT(*)被优化到最快的返回速度。
mysql查询连续次数_Mysql如何查询连续时间内的次数
weixin_39829236的博客
01-19 509
转自：http://www.tuicool.com/articles/uyEZJf原文中相关说明很少，看起来很费劲，故在这里，我自己添加了些说明，完全是自己理解的，不知道对否，若有不对，欢迎批评指正，谢谢。在网上看到一道有意思的题目，大意是如何在mysql查询连续在线的天数。所谓的连续在线是指相邻两天都登录过，不一定一直在线，但是只要有过登录即可。如，1月1日登录了一会下线了，一月2日登录了一会下...
关系型数据库+Mysql+查询用户连续登陆天数+数据统计
04-26
关系型数据库 MySQL 查询用户连续登录天数数据统计 关系型数据库是指使用关系模型来组织和存储数据的数据库管理系统。MySQL 是一种流行的关系型数据库管理系统。关系型数据库的主要特点是使用表来存储数据，每个表...
mysql限制登录次数_MySQL会话控制限制登录次数
weixin_39637545的博客
01-19 1277
前言一般我们开发环境的MySQL是没有配置登录保护的，但仅限于开发环境，正式环境是不允许无限制登录，存在很大的风险。MySQL 5.7 以后提供了Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟。该插件可有效的防止客户端暴力登录的风险(攻击)。该插件包含以下两个组件：connection_control：控制失败次数以及延迟时间connection_c...
mysql限制请求次数_MySQL会话控制限制登录次数
weixin_35952427的博客
01-21 554
前言一般我们开发环境的MySQL是没有配置登录保护的，但仅限于开发环境，正式环境是不允许无限制登录，存在很大的风险。MySQL 5.7 以后提供了Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟。该插件可有效的防止客户端暴力登录的风险(攻击)。该插件包含以下两个组件：connection_control：控制失败次数以及延迟时间connection_c...
mysql 连续打卡天数_Sql如何统计连续打卡天数?
weixin_28864485的博客
02-18 670
今天来解一道题面试中可能经常会被一些面试官拿来“刁难”的题，就是《如何统计连续打卡天数》，当然了这里面的打卡可以换成任意其他行为，比如连续登陆天数，连续学习天数，连续购买天数，这里的天数也是可以换成小时或者别的时间单位的。这个问题的逻辑还是有点复杂，如果要是之前没遇到过这种问题，当场被问到的时候，肯定会一脸懵。直接来看实战，现在有一张表t，这张表存储了每个员工每天的打卡情况，现在需要统计截止目前每...
mysql 5.7 查询最大最小连续签到时间
wurd的博客
10-21 433
mysql 5.7 获取连续签到时间 sql
mysql如何查询两个日期之间最大的连续登录天数
09-09
在现在的很多网站中都有这样一个功能。记录用户的连续登陆天数，所谓的连续在线是指相邻两天都登录过，不一定一直在线，但是只要有过登录即可。这篇文章主要介绍的是利用sql语句如何查询在两个日期之间最大的连续登录天数，有需要的朋友们下面来一起看看吧。
mysql 查询统计语句_单个select语句实现MySQL查询统计次数
weixin_33425215的博客
01-27 731
单个select语句实现MySQL查询统计次数单个select语句实现MySQL查询统计次数的方法用处在哪里呢？用处太多了，比如一个成绩单，你要查询及格得人数与不及格的人数，怎么一次查询出来？MySQL查询统计次数简单的语句肯定是这样了：复制代码 代码如下:select a.name,count_neg,count_plus from(select count(id) as count_plus,...
mysql连续查询_mysql持续查询
weixin_39926943的博客
01-28 325
持续读取mysql：#!/usr/bin/env python#coding=utf8import osimport sysimport timeimport Queueimport MySQLdbfrom threading import Threadfrom termcolor import cprintqueues = Queue.Queue(maxsize=100)dblen ...
MySql中关于某列中相同数值连续出现次数的统计
xiaotianlan的专栏
08-27 4809
MySql中关于某列中相同数值连续出现次数的统计   原表如下： www.2cto.com   100 101 102 100 100 103 104 102 102 105 106 101 101 输出如下：  www.2cto.com   100    1 101    2 102    3 100    4 100    4 103    5 1
MYSQL查询连续日期数据
fmy1023的博客
07-21 1568
查询连续多前天幼儿请假数据 SELECT child_id, val - ( @rownum := @rownum + 1 ) AS flag, group_concat( adate ) AS date_join, GROUP_CONCAT(id), COUNT( 1 ) num FROM ( SELECT t1.id, t1.adate, t1.child_id, t1.`status`, ( unix_timestamp( t1.adate ) - t2.min_u
mysql 日期连续性_一个计算日期连续性的脚本_MySQL
weixin_31108095的博客
01-19 206
1 USE [test]2 GO34 --一个计算日期连续性的脚本5 --需求：查找指定天数的连续交易的记录，比如连续2天发生，连续3天发生等等。6 /****** Object: Table [dbo].[liu_shui] Script Date: 11/11/2015 14:36:05 ******/7 SET ANSI_NULLS ON8 GO9 SET QUOTED_IDENTI...
mysql 判定连续日期
weixin_34186408的博客
07-03 135
实现“mysql 判定连续日期”流程 步骤表格 erDiagram |步骤|描述| |---|---| |1|查询连续日期的数据| |2|计算日期间隔| |3|判定连续日期|代码示例 步骤1：查询连续日期的数据 登录后复制 -- 查询连续日期的数据 SELECT id, date ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值