Mysql计算匹配的连续行 [英] Mysql Counting the consecutive rows that match

问题描述

我正在尝试运行 MySQL 查询，但不太确定如何执行.我想计算匹配的连续行数.例如

I am trying to run a MySQL query but not quite sure how to do it. I want to count the number of consecutive rows that match. For example

A A A B B B B A A

我希望结果是 3

计算 A 的总数很容易，但我不确定是否只能找出最近的 3 个.

It is easy to count the total number of A but im not sure out to out the 3 most recent only.

这是我如何列出所有内容的示例

Here is an example of how im listing all

SELECT email,subject FROM tablename where email='[e-mail]' and subject='FAIL';

<小时>

编辑:以下是一些可能有帮助的示例数据.为简单起见，我们将只有 ID 和主题，并按 ID

---

Edit: Here is some sample data that might help. For simplicity We will just have ID and Subject and order by ID

ID Subject
1 FAIL
2 FAIL
3 FAIL
4 PASS
5 PASS
6 FAIL
7 PASS
8 FAIL
9 FAIL

结果应该是 3 或 2，具体取决于您如何订购 ID

The result should be either 3 or 2 depending on how you order ID

推荐答案

我在这里加载了一个 SQLfiddle:http://sqlfiddle.com/#!2/5349a/1 但是，在您的示例数据中，您有两个 ID=5.我让它独一无二.此外，我的 SQLFiddle 数据不再与您的数据匹配，因为我更改了一些值以确保它有效.玩得开心:)(这可以查看序列的最大 ID 值)

I loaded a SQLfiddle here: http://sqlfiddle.com/#!2/5349a/1 However, in your sample data, you had two ID=5. I made it unique. Also my SQLFiddle data doesn't match yours anymore since I changed some values to make sure it worked. Have fun with it :) (This works looking at the largest ID value for the sequence)

试试这个:

SELECT COUNT(*)
FROM (
  SELECT Subject, MAX(ID) AS idlimit
  FROM t
  GROUP BY Subject
  ORDER BY MAX(ID) DESC
  LIMIT 1,1) as Temp
JOIN t
  ON Temp.idlimit < t.id

这篇关于Mysql计算匹配的连续行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！