带计数的SQL连续记录

本文介绍了带计数的SQL连续记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

表名调用

Field 1 - Phone_number Field 2 - System_outcome Phone_number System_outcome DateTime -------------------------------------------------- 07777778999 Answered 18-12-2016 17:15 07777778123 No Answer 18-12-2016 18:10 07777778999 No Answer 19-12-2016 19:30 07777778999 No Answer 19-12-2016 12:15 07777778999 No Answer 19-12-2016 13:15 07777778999 No Answer 20-12-2016 11:15 07777778124 No Answer 20-12-2016 9:15 07777778128 Answered 20-12-2016 17:15 07777778074 Answered 20-12-2016 17:15

上面是一个示例，我需要找出的是如何找到连续无应答计数大于6的数字?

Above is an example, what i need to find out is how do i find numbers that have a consecutive no answer count of more than 6?

此刻我已经获得了以下内容，但并不是连续的.

at the moment i have been able to get the following but it's not consecutive.

SELECT phone_number,system_outcome,Datetime FROM calls WHERE DATE (datetime) BETWEEN '2016-12-23' AND '2016-12-31' AND system_outcome = 'NO_ANSWER' GROUP BY phone_number HAVING count(Phone_number) > 6

推荐答案

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

You can use variables to do this.

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.

1) @cur_outcome which is initially set to an empty string. Thereafter the select assigns the current row's system_outcome.

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

2) @prev_outcome which is initially set to an empty string. Thereafter the select sets it to the @cur_outcome (which is an empty string for the first time and so on).

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

3) @cur_pnum which is initially set to an empty string. Thereafter the select assigns the current row's phone_number.

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

4) @prev_pnum which is initially set to an empty string. Thereafter the select sets it to @cur_pnum value (which is an empty string initially).

order by子句在这里很重要，它可以根据phone_number和date指定当前行和先前行.

order by clause is important here to designate the current and previous rows based on phone_number and date.

首先运行内部查询，以了解变量的设置方式，这将为您澄清事物.

Run the inner query initially to see how the variables are set, which would clarify things for you.

Sample Demo

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

The demo contains some sample data more than what was shown in the question.