mysql连续打卡次数_mysql-带计数的SQL连续记录

您可以使用变量来执行此操作.

select phone_number from (

select c.*,

@prev_outcome:=@cur_outcome,

@cur_outcome:=system_outcome,

@prev_pnum:=@cur_pnum,

@cur_pnum:=phone_number,

case when @cur_pnum = @prev_pnum and @prev_outcome <> @cur_outcome then @rn:=@rn+1

when @cur_pnum = @prev_pnum and @prev_outcome = @cur_outcome then @rn:=@rn

else @rn:=1 end as rank

from calls c,

(select @rn:=0,@prev_pnum:='',@cur_pnum:='',@prev_outcome:='',@cur_outcome:='') r

order by phone_number,dt

) x

where system_outcome='No Answer'

group by phone_number,rank

having count(*) > 6

该查询使用4个变量

1)@cur_outcome,最初设置为空字符串.此后,选择将分配当前行的system_outcome.

2)@prev_outcome,最初设置为空字符串.此后,选择将其设置为@cur_outcome(这是第一次为空字符串,依此类推).

3)@cur_pnum最初设置为空字符串.此后,选择将分配当前行的phone_number.

4)@prev_pnum,最初设置为空字符串.此后,选择将其设置为@cur_pnum值(最初为空字符串).

order by子句在这里很重要,以便根据phone_number和date指定当前行和上一行.

最初运行内部查询以查看变量的设置方式,这将为您弄清楚.

该演示包含的一些示例数据比问题中显示的要多.