MySQL查询满足条件的连续时间段数据

文章转自《MySQL查询满足条件的连续时间段》及评论，主要涉及mysql变量的使用

建表：

CREATE TABLE `stadium` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `visit_date` DATE NOT NULL,
  `people` INT(32) NOT NULL,
  PRIMARY KEY (`id`)
)

数据：

方法一：

-- 查询连续至少3天游客数目大于700的数据；

SET @startdate = CAST(NULL AS DATE);
SET @granularity = 1;   /* DAY */
SET @minduration = 3;  /* DAY */
SET @minvalue = 699;
 
SELECT t.visit_date,t.people FROM (
  SELECT StartDate,MAX(visit_date) AS EndDate FROM (
    SELECT visit_date,people,
      CASE WHEN people > @minvalue OR @startdate IS NOT NULL 
      THEN IFNULL(@startdate, visit_date) 
      END AS StartDate,
      @startdate := 
      CASE WHEN people > @minvalue 
      THEN IFNULL(@startdate, visit_date) 
      END AS s
    FROM (
      SELECT visit_date, people FROM stadium
      UNION ALL
      SELECT MAX(visit_date) + INTERVAL @granularity DAY, @minvalue FROM stadium
    ) w
    ORDER BY visit_date
  ) w
  WHERE StartDate IS NOT NULL
  GROUP BY StartDate
) w
  INNER JOIN stadium t ON t.visit_date >= w.StartDate AND t.visit_date < w.EndDate
WHERE w.EndDate >= w.StartDate + INTERVAL @minduration DAY;

 查询结果： 

方法二：通过变量b每次加1来使得相邻的日期减去b是一个相同值，如果中间有不符合t条件的，日期减去b的值将会改变

SELECT a.*
FROM (
	SELECT stadium.*
		, DATE_FORMAT(visit_date, '%d') - (@b := @b + 1) AS c
	FROM `stadium`, (
			SELECT @b := 0
		) b
	WHERE people > 699
) a
	INNER JOIN (
		SELECT c
		FROM (
			SELECT stadium.*
				, DATE_FORMAT(visit_date, '%d') - (@e := @e + 1) AS c
			FROM `stadium`, (
					SELECT @e := 0
				) e
			WHERE people > 699
		) a
		GROUP BY c
		HAVING COUNT(c) > 2
	) b
	ON a.c = b.c

 查询结果：

相关文章：

SQL如何求解用户连续N天登录？