SQL如何按Column a分组实现每组首行标1其余标0效果

需求描述 #

需要在查询结果中生成不存在的字段column b，取值规则为：按column a分组后，每组第一行取值为1，同组其余行取值为0。
预期返回效果如下：

注意：SQL没有默认的行顺序规则，必须指定分组内的排序依据（比如自增主键、创建时间等业务字段）才能准确定义"首行"，否则标记结果会随机不符合预期。下方代码中请将排序字段替换为实际用来判定行先后顺序的字段，表名替换为你查询的实际表名。

实现代码 #

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Hive、Spark SQL等） #

之前用row_number()未达预期，通常是漏写了分区逻辑或者分组内排序规则导致的，正确写法如下：

SELECT
  `column a`,
  CASE WHEN ROW_NUMBER() OVER (
    PARTITION BY `column a` 
    ORDER BY 排序字段
  ) = 1 THEN 1 ELSE 0 END AS `column b`
FROM 表名;

不要用RANK()、DENSE_RANK()实现该需求，这两个函数在排序字段值重复时会返回相同序号，会导致同组出现多个标记为1的行。

不支持窗口函数的旧版MySQL（5.x版本） #

可以通过用户变量实现分组序号统计，写法如下：

SELECT
  `column a`,
  CASE WHEN @current_grp = `column a` THEN 0
       WHEN @current_grp := `column a` THEN 1
  END AS `column b`
FROM 表名, (SELECT @current_grp := '') AS temp_var
ORDER BY `column a`, 排序字段;

该写法的ORDER BY必须放在查询最外层，否则分组计数顺序会错乱。

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