Mysql计算与多个ID匹配的最近连续数行

本文介绍了Mysql计算与多个ID匹配的最近连续数行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 问题： 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 | failCount1001 | 21002 | 01003 | 11004 | 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 pwhere 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 numfailsfrom payment pgroup by p.memid; 这里是一个SQL小提琴。Here is a SQL Fiddle. 这篇关于Mysql计算与多个ID匹配的最近连续数行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！