统计按不同字段排序的连续行中相同值的数量。

Question

统计按不同字段排序的连续行中相同值的数量。

3

我有一个具有以下结构的表 finals(ID, name, result)

CREATE TABLE `finals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `result` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

地点：

ID 是一个自增键，
name 是人的名字，
result 可以是通过或失败

示例数据

mysql> select * from finals;
+----+------+--------+
| id | name | result |
+----+------+--------+
|  1 | John | Pass   |
|  2 | John | Fail   |
|  3 | John | Pass   |
|  4 | Kyle | Pass   |
|  5 | John | Pass   |
|  6 | Kyle | Pass   |
|  7 | Kyle | Pass   |
|  8 | Kyle | Fail   |
+----+------+--------+
8 rows in set (0.00 sec)

解释

我正在尝试获取至少有连续三个"Pass"的人员姓名。所以，期望的结果是： "Kyle" 因为"Kyle"有连续三个"Pass"，即ID 4、6、7。

请注意，我不关心这个序列是否被其他人的记录中断，但如果同一个人的记录中断后出现了"Fail"的值，则我会在结果中排除该人员。

这就是为什么"John"不在期望的结果中。也就是说，虽然"John"总共有三个"pass"，但它们被一个"Fail"打断，所以我想将其从结果中排除。

我尝试了以下查询：

mysql> SELECT name, GROUP_CONCAT(result ORDER BY id) as result_str FROM finals GROUP BY name having result_str LIKE '%Pass,Pass,Pass%';
+------+---------------------+
| name | result_str          |
+------+---------------------+
| Kyle | Pass,Pass,Pass,Fail |
+------+---------------------+
1 row in set (0.00 sec)

它有效，这是期望的结果。然而，我正在寻找其他方法来：

更加通用
避免对GROUP_CONCAT结果长度的限制。

提前感谢您。

- Jehad Keriaki

1个回答

相关问题
10 选择具有相同值的“连续”行

- RolandoMySQLDBA · Accepted Answer

我有一个解决方案，不需要使用GROUP_CONCAT。

建议的解决方案

SET @x = 0;
SET @name = '';
SET @result = '';
SELECT name,consecutive FROM
(SELECT
    name,
    (@nametag:=MD5(CONCAT(name,':',result))),
    (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    (@name:=@nametag) inc
FROM finals ORDER BY name,id) A
WHERE consecutive >= 3;

提议的解决方案已执行

mysql> SELECT name,consecutive FROM
    -> (SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id) A
    -> WHERE consecutive >= 3;
+------+-------------+
| name | consecutive |
+------+-------------+
| Kyle |           3 |
+------+-------------+
1 row in set (0.02 sec)

mysql>

子查询的输出

mysql> SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id;
+------+------------------------------------------+-------------+----------------------------------+
| name | (@nametag:=MD5(CONCAT(name,':',result))) | consecutive | inc                              |
+------+------------------------------------------+-------------+----------------------------------+
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 534b3d163a04b74a72c6dbe68db1c01e         |           1 | 534b3d163a04b74a72c6dbe68db1c01e |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           2 | 84cc30b986fe149dfb765dd09fad8a60 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           1 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           2 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           3 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 4a4e0aaa102c37f098bd6afd13ccfea0         |           1 | 4a4e0aaa102c37f098bd6afd13ccfea0 |
+------+------------------------------------------+-------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>

试一试吧！！！

我只是两天前回答了一个类似的问题（赋值表达式的逻辑值）