Mysql计算匹配的连续行

2021-12-30 00:00:00 count mysql

我正在尝试运行 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='test@example.com' 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

相关文章