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

---

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

根据您订购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屋！