如何在不影响is_first_kind字段的情况下结合会话变量实现倒序排序？

问题原因 #

你修改了子查询中的ORDER BY id desc, date desc，这会让变量@rn在倒序的行序列中计算，导致每个日期的最后一条（原正序的第一条）被误判为分组第一条，从而标记值反向。正确逻辑应该是先按原规则计算出正确的is_first_kind，再对最终结果进行倒序排序。

解决方案1：基于原有变量查询修改 #

在你原本正确的查询外层添加倒序排序，不改动内部的rn计算逻辑：

select id, 
       msg ,
       `date` ,
       case when rn =1 then 1 else 0 end as is_first_kind
from ( SELECT  id, msg ,`date`,rn
       FROM (  SELECT *, IF(@prev <> `date`, @rn:=0,@rn), @prev:=`date`, @rn:=@rn+1 AS rn
               FROM chat, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
               ORDER BY id asc, `date` asc 
            ) t 
     ) x
ORDER BY id desc; -- 仅在最终结果层添加倒序排序

解决方案2：使用窗口函数（MySQL 8.0+） #

更简洁的写法，利用ROW_NUMBER()窗口函数直接按日期分组、id正序标记第一条，最后整体倒序输出：

SELECT 
    id,
    msg,
    `date`,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY `date` ORDER BY id ASC) = 1 THEN 1 ELSE 0 END AS is_first_kind
FROM chat
ORDER BY id DESC;

最终结果 #

两种方法都会得到你预期的结果：

id  msg         date        is_first_kind
5   Oi!         2022-10-08  0
4   Ciao!       2022-10-08  1
3   Bonjour!    2022-10-07  1
2   Hola!       2022-10-06  0
1   Hello!      2022-10-06  1

内容的提问来源于stack exchange，提问作者WebDiva