MySQL借助列名实现分组标识

描述及实现

需求描述及实现思路

在要分页展示的数据表中，不借助额外字段，如果需要对查询结果集中标识字段（某列字段）分组，并对分组的数据集做开始(┏)、结束(┗)和中间(┃)的一组标识，那么要知道信息有：

1. 当前标识字段分组的总个数，标记为 groupCount；
2. 当前数据所在占当前标识字段分组总个数的位数，标记为 currentRowNumber。

知道这两个信息后，我们的计算方式如下：

f(groupCount, currentRowNumber)=\begin{cases} 开始标识(┏) & currentRowNumber=1 \\ 中间标识(┃) & groupCount > currentRowNumber > 1 \\ 结束标识(┗) & groupCount = currentRowNumber \\ \end{cases}

SQL代码：

注意：当前的MySQL版本需要在8.0以上，使用窗口函数。

SELECT   
    'other_columns',
    COUNT(*) OVER(PARTITION BY 'group_column') AS groupCount,
    ROW_NUMBER() OVER(PARTITION BY 'group_column' ORDER BY 'order_column') AS currentRowNumber
FROM   
    'select_table_name';

• COUNT(*) OVER (PARTITION BY 'group_column')：计算每个 group_column 分组内的总行数。
• ROW_NUMBER() OVER(PARTITION BY 'group_column' ORDER BY 'order_column')：每个 group_column 分组内的行分配一个序号,序号是基于 order_column 字段排序。

MySQL中 ROW_NUMBER() 和 OVER() 的含义：

• ROW_NUMBER()：函数返回查询结果集中每行的序号。序号从1开始，对每一行递增。
• OVER()：定义了如何计算的方法。
  1. PARTITION BY 'group_column'：这个子句依据 group_column 的值，将查询结果分成多个分区。
  2. ORDER BY 'order_column'：这个子句依据 order_column 的值，定义了每个分区内行的排序方式。

实践及效果

示例代码：

# 新建一张表 tb_tree_lev
CREATE TABLE demo.tb_tree_lev (
	id varchar(32) NULL,
	code varchar(100) NULL,
	name varchar(100) NULL,
	parent_id varchar(100) NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# 插入表数据
INSERT INTO demo.tb_tree_lev (id, code, name, parent_id) VALUES('1', 'lev_1', '层级1', '1');
INSERT INTO demo.tb_tree_lev (id, code, name, parent_id) VALUES('2', 'lev_2', '层级2', '1');
INSERT INTO demo.tb_tree_lev (id, code, name, parent_id) VALUES('3', 'lev_3', '层级3', '1');
INSERT INTO demo.tb_tree_lev (id, code, name, parent_id) VALUES('4', 'lev_4', '层级4', '4');
INSERT INTO demo.tb_tree_lev (id, code, name, parent_id) VALUES('5', 'lev_5', '层级5', '4');

# 使用窗口函数查询并标记
select 
	tmp.id,
	case
		when tmp.currentRowNumber = 1 then '┏'
		when (tmp.groupCount > tmp.currentRowNumber and tmp.currentRowNumber > 1) then '┃'
		else '┗'
	end as groupFlag,
	tmp.code,
	tmp.name,
	tmp.parent_id,
	tmp.groupCount,
	tmp.currentRowNumber
from (
	SELECT   
	    *,
	    COUNT(*) OVER(PARTITION BY parent_id) AS groupCount,
	    ROW_NUMBER() OVER(PARTITION BY parent_id ORDER by parent_id, id) AS currentRowNumber
	FROM   
	    tb_tree_lev ttl
)tmp

示例效果：

使用总结

在不扩充字段的前提下，既为了减少代码的编写量，又能够实现动态数据的一组标识，可以采用该MySQL的窗口函数来计算。在使用的时一定要注意MySQL的版本，窗口函数需要MySQL的版本至少为8.0以上。