选择查询 mysql_mysql – 高级SQL选择查询

此查询使用变量来跟踪相邻周,如果它们是连续的,则计算出来：

set @start_week = 2, @week := 0, @conseq := 0, @cookie:='';

select conseq_weeks, count(*)

from (

select

cookie,

if (cookie != @cookie or week != @week + 1, @conseq := 0, @conseq := @conseq + 1) + 1 as conseq_weeks,

(cookie != @cookie and week <= @start_week) or (cookie = @cookie and week = @week + 1) as conseq,

@cookie := cookie as lastcookie,

@week := week as lastweek

from (select week, cookie from webhist where week >= @start_week order by 2, 1) x

) y

where conseq

group by 1;

这是第2周.再过一周,更改顶部的start_week变量.

这是测试：

create table webhist(week int, cookie char);

insert into webhist values (1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'), (2, 'a'), (2, 'b'), (3, 'a'), (3, 'c'), (3, 'd');

输出上面的查询,其中周> = 1：

+--------------+----------+

| conseq_weeks | count(*) |

+--------------+----------+

| 1 | 4 |

| 2 | 2 |

| 3 | 1 |

+--------------+----------+

输出上面的查询,其中周> = 2：

+--------------+----------+

| conseq_weeks | count(*) |

+--------------+----------+

| 1 | 2 |

| 2 | 1 |

+--------------+----------+

附：好问题,但有点破球