@mysqlクエリ最大連続日数sql
2062 ワード
SELECT uid, min(date) AS mindate, max(date) AS maxdate, max(countday) countday, group_concat(date) dates FROM( SELECT uid,
SELECT DATEDIFF(‘2015-09-02’,‘2015-09-01’)
分解sql SELECT
@countday @last_dateこれは変数宣言です
:=割り当て
@countday:=(CASE WHEN(DATEDIFF(
@last_date:=
したがって、比較するたびに、現在の行dataと前の行dataが比較されます.
(select@countday:=0,@last_date:=’)as付与変数
試験データCREATE TABLE
INSERT INTO
(1, 1, ‘20111-011-01’),
(2, 1, ‘20111-011-02’),
(17, 1, ‘20111-011-03’),
(18, 1, ‘20111-011-04’),
(3, 2, ‘20111-011-01’),
(40, 2, ‘20111-011-02’),
(118, 2, ‘20111-011-03’),
(20, 2, ‘20111-011-04’),
(11, 3, ‘20111-011-01’),
(6, 3, ‘20111-011-02’),
(21, 3, ‘20111-011-03’),
(22, 3, ‘20111-011-04’),
(75, 4, ‘20111-011-01’),
(87, 4, ‘20111-011-02’),
(101, 11, ‘20111-011-01’),
(10, 11, ‘20111-011-02’),
(11, 6, ‘20111-011-01’),
(12, 6, ‘20111-011-02’),
(13, 7, ‘20111-011-01’),
(94, 7, ‘20111-011-02’),
(18, 8, ‘20111-011-01’),
(156, 8, ‘20111-011-02’);
date
,@countday := ( CASE WHEN ( DATEDIFF( date
,@last_date) = 1) THEN (@countday + 1) ELSE 1 END) AS countday, @last_date := date
FROM(SELECT uid
, date
FROM test_demo ORDER BY uid,date) AS t1, (select @countday:=0,@last_date:=’’) as t2 ) AS t3 GROUP BY uid SELECT DATEDIFF(‘2015-09-02’,‘2015-09-01’)
分解sql SELECT
uid
,date
FROM test_demo ORDER BY uid,dateソート検出id,日付@countday @last_dateこれは変数宣言です
:=割り当て
@countday:=(CASE WHEN(DATEDIFF(
date
,@last_date)=1)THEN(@countday+1)ELSE 1 END DATEDIFF(d 1,d 2)2つの時間の時間差を計算2つの時間差が1の場合、連続日数、@countday+1変数に1を加算します.@last_date:=
date
dataを変数@last_に割り当てるdate したがって、比較するたびに、現在の行dataと前の行dataが比較されます.
(select@countday:=0,@last_date:=’)as付与変数
試験データCREATE TABLE
test_demo
(id
int(11)NOT NULL AUTO_INCREMENT, uid
int(11) NOT NULL DEFAULT ‘0’, date
date NOT NULL, PRIMARY KEY ( id
), UNIQUE KEY idx_ud
( uid
, date
) ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 INSERT INTO
test_demo
( id
, uid
, date
) VALUES (1, 1, ‘20111-011-01’),
(2, 1, ‘20111-011-02’),
(17, 1, ‘20111-011-03’),
(18, 1, ‘20111-011-04’),
(3, 2, ‘20111-011-01’),
(40, 2, ‘20111-011-02’),
(118, 2, ‘20111-011-03’),
(20, 2, ‘20111-011-04’),
(11, 3, ‘20111-011-01’),
(6, 3, ‘20111-011-02’),
(21, 3, ‘20111-011-03’),
(22, 3, ‘20111-011-04’),
(75, 4, ‘20111-011-01’),
(87, 4, ‘20111-011-02’),
(101, 11, ‘20111-011-01’),
(10, 11, ‘20111-011-02’),
(11, 6, ‘20111-011-01’),
(12, 6, ‘20111-011-02’),
(13, 7, ‘20111-011-01’),
(94, 7, ‘20111-011-02’),
(18, 8, ‘20111-011-01’),
(156, 8, ‘20111-011-02’);