如何统计mysql中连续重复的行数 [英] How to count the consecutive duplicate rows in mysql

问题描述

我们在表 customer 中有两列，其中有客户 ID 和服务 ID.因此，每个客户使用多项服务，我们必须找到客户使用的连续服务的最大计数.

We have two columns in a table customer in which we have customer id and the service id in those. So, every customer using multiple services, we have to find the maximum count of the consecutive services used by the customers.

create table customer(id int not null auto_increment,customerid varchar(20), serviceid varchar(20),primary key(id));

insert into customer(customerid,serviceid) values('Nitesh','Mobile');
insert into customer(customerid,serviceid) values('Nitesh','Mobile');
insert into customer(customerid,serviceid) values('Nitesh','Landline');
insert into customer(customerid,serviceid) values('Nitesh','Broadband');
insert into customer(customerid,serviceid) values('Nitesh','Mobile');

insert into customer(customerid,serviceid) values('Nishant','Mobile');
insert into customer(customerid,serviceid) values('Nishant','Landline');
insert into customer(customerid,serviceid) values('Nishant','Landline');
insert into customer(customerid,serviceid) values('Nishant','Landline');
insert into customer(customerid,serviceid) values('Nishant','Broadband');

insert into customer(customerid,serviceid) values('Soe','Mobile');
insert into customer(customerid,serviceid) values('Soe','Mobile');
insert into customer(customerid,serviceid) values('Soe','Landline');
insert into customer(customerid,serviceid) values('Soe','Broadband');
insert into customer(customerid,serviceid) values('Soe','Mobile');

我必须连续计算客户使用的最大服务.

I have to count the maximum service used by the customer consecutively.

输出:

Customerid|Serviceid|ServiceidCount
----------------------------------

Nitesh|Mobile|2
Nishant|Landline|3
Soe|Mobile|2

推荐答案

更新版本

您可以尝试以下查询.它使用 mysql 变量来检查 customerid 和 serviceid 列中的连续更改.

You can try following query. It uses mysql variables to check for consecutive changes in columns customerid and serviceid.

    SELECT customerid, serviceid, MAX(R)+1 AS ServiceIdCount
FROM (SELECT c.customerid, c.serviceid
    , CASE WHEN @s=CONCAT(customerid,'|',serviceid) THEN @r:=@r+1 ELSE @r:=0 END AS R
    , @s:=CONCAT(customerid,'|',serviceid) AS S
    FROM customer c
    CROSS JOIN (SELECT @r:=0, @s:='') d
     ORDER BY id
    ) e 
WHERE R>0
GROUP BY customerid, serviceid;

添加了示例数据(您的除外):

Added sample data (other than yours):

insert into customer(customerid,serviceid) values('Mark','Mobile');
insert into customer(customerid,serviceid) values('Mark','Mobile');
insert into customer(customerid,serviceid) values('Mark','Mobile');
insert into customer(customerid,serviceid) values('Mark','Landline');
insert into customer(customerid,serviceid) values('Mark','Mobile');
insert into customer(customerid,serviceid) values('Mark','Mobile');

输出:

+------------+-----------+----------------+
| customerid | serviceid | ServiceIdCount |
+------------+-----------+----------------+
| Mark       | Mobile    |              3 |
| Nishant    | Landline  |              3 |
| Nitesh     | Mobile    |              2 |
| Soe        | Mobile    |              2 |
+------------+-----------+----------------+

这篇关于如何统计mysql中连续重复的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！