计算最后一行，直到列值等于myql中的给定值

Suppose My table has only two column; among them one is AUTO_INCREMENT and another contains under line data...

假设我的表只有两列;其中一个是AUTO_INCREMENT,另一个是包含在线数据......

A
B
C
A
A

I want to count last rows until desire given value found.

我想计算最后一行,直到找到给定的值。

Suppose For,

A : 0 (Count From last to reach A)
C : 2 (Count From last to reach C)
B : 3 (Count From last to reach B)

1 个解决方案

#1

Get the highest ID for each item, and then count the number of rows that have higher IDs.

获取每个项目的最高ID,然后计算具有更高ID的行数。

SELECT x.item, IFNULL(COUNT(y.id), 0) as count
FROM (SELECT item, MAX(id) AS lastid
      FROM yourTable
      GROUP BY item) AS x
LEFT JOIN yourTable AS y ON y.id > x.lastid
GROUP BY x.item

DEMO