mysql - 如何计算列值出现的次数但没有GROUP BY(不是不同的值)mysql

Question

id      title           numbers1       chicken         12       meat            13       fish            14       fish            25       meat            26       fish            37       chicken         28       chicken         3

I want to construct the following output as above. Anyone can help me calculate number of occurrence of column values but without GROUP BY(not remove same values) , let create "numbers" field same as above , thank

Normal , if use COUNT(*) :

SELECT COUNT(*) FROM table GROUP BY title

但它删除了相同的值:

id      title           numbers1       chicken         32       meat            23       fish            3

我想保持相同的值，并像这样输出:

id      title           numbers1       chicken         1    2       meat            13       fish            14       fish            25       meat            26       fish            3    7       chicken         2     8       chicken         3     

Answer 1

我假设您的问题是如何在示例输出中生成 numbers 列。如果这不正确，请澄清问题。

select  id
,       title
,       (
        select  count(*)
        from    Table1 t2
        where   t1.title = t2.title
                and t2.id <= t1.id
        ) as numbers
from    Table1 t1

Live example at SQL Fiddle.