Mysql计算与多个ID匹配的最近连续数行 [英] Mysql Counting most recent consecutive number rows that match for multiple id's

问题描述

问题： Mysql计数匹配的连续数行<

the Q: Mysql Counting the consecutive number rows that match has touched on this but would like some help expanding it.

我在付款表中有多个成员，需要最近一次失败付款的数量，其中：

I have multiple Members in a payments table and need a count of most recent Failed Payments where:

1 =成功，2 =失败

1 = Success, 2 = Fail

它必须基于最近的付款，而不是总计！
所以一个人可能有失败，但如果最近的付款是成功的，则计数为零。

It must be based on most recent payments, not overall count! So a person could have had failures but count would be zero if most recent payment was Successful.

CREATE TABLE Payment
(`pID` int, `memID` int, `pStatus` int, )
;

INSERT INTO Payment
(`pID`, `memID`, `pStatus)
VALUES
(1, 1, 1001),
(2, 1, 1001),
(3, 1, 1001),
(4, 2, 1001),
(5, 2, 1001),
(6, 1, 1002),
(7, 2, 1002),
(8, 2, 1002),
(9, 1, 1002),
(10, 1, 1002),
(11, 2, 1003),
(12, 1, 1003),
(13, 2, 1003),
(14, 1, 1003),
(15, 2, 1003),
(16, 2, 1004),
(17, 2, 1004),
(18, 2, 1004),
(19, 2, 1004),
(20, 2, 1004);

Retun应为：

memId | failCount
1001  |  2
1002  |  0
1003  |  1
1004  |  5

推荐答案

我们可以获取上次未失败的付款的最大ID，然后使用较大的ID对失败的付款进行汇总。这基本上是你想要的：

Hmmm. We can get the maximum id of the last not failed payment and then sum the failed payments with bigger ids. This basically does what you want:

select p.memid, count(*)
from payment p
where id > coalesce((select max(id) from payment p2 where p2.memid = p.memid and p2.pstatus = 1), 0)
group by p.memid;

但是，它不返回0，所以让我们把它转换成条件聚合： p>

But, it doesn't return the 0s, so let's turn this into a conditional aggregation:

select p.memid,
       sum(id > coalesce((select max(id) from payment p2 where p2.memid = p.memid and p2.pstatus = 1), 0)
          ) as numfails
from payment p
group by p.memid;

这里是一个SQL小提琴。

Here is a SQL Fiddle.

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