mysql - 根据分组依据和ID向行添加(半)序号，然后标记最新的两个项目

Question

我想做(某种程度上)与 this old post 相反的事情是关于；

这是我的示例表:

ID   NAME    
---- ----
1    Apple
2    Apple
3    Apple
4    Bravo
5    Bravo
6    Cat
7    Cat
8    Cat
9    Cat
10   Apple
11   Apple

我想添加一个新行，为每组项目添加序号。问题是有些项目出现多次，但不属于同一组。仅当它们紧接着出现时(顺序 ID)，它们才属于在一起。

更新后的表格应如下所示:

ID   GROUP NAME    
---- ----- ----
1    1     Apple
2    1     Apple
3    1     Apple
4    2     Bravo
5    2     Bravo
6    3     Cat
7    3     Cat
8    3     Cat
9    3     Cat
10   4     Apple
11   4     Apple

然后作为最后一步，我想分别标记每组中最新的两项(ID 值最高的项)，如下所示:

ID   GROUP NAME   MARK    
---- ----- ----   ----
1    1     Apple  false
2    1     Apple  true
3    1     Apple  true
4    2     Bravo  true
5    2     Bravo  true
6    3     Cat    false
7    3     Cat    false
8    3     Cat    true
9    3     Cat    true
10   4     Apple  true
11   4     Apple  true

如何在 MySQL 中执行此操作？

Answer 1

使用变量，首先按 id 和名称创建组，然后根据 id 对这些 grp 进行排序，标记前两个。

<强> SQL DEMO

SELECT `ID`, `NAME`, rn,
        mark <= 2 as mark       
FROM (
        SELECT `ID`, `NAME`, rn, 
               @mark := IF(@grp = rn,
                           @mark + 1,
                           IF( @grp := rn, 1, 1)                                      
                           ) as  mark
        FROM (       
                SELECT `ID`, `NAME`, 
                       @rn := IF(@name = `NAME`,
                                 @rn,
                                 IF(@name := `NAME`, @rn + 1, @rn + 1)
                                ) as rn
                FROM Table1
                CROSS JOIN (SELECT @rn := 0, @name :='') var
                ORDER BY `ID`
             ) T
        CROSS JOIN (SELECT @mark := 0, @grp := 0 ) var
        ORDER BY rn, `ID` DESC
    ) Y
ORDER BY ID    

输出