mysql分组(group by)后检查是否包含某些值的学习

1 背景

开发业务使用mysql数据库时，为了扩展性通常会使用列表（纵表)。如下图，在这个表中抽象出类型和值，当类型增加后，不需要改表结构，直接插入即可。

表名：config

type	value
1	出行
1	饮食
1	娱乐
2	饮食
2	娱乐

有时候通过这类表建立配置表。初始会通过人工导入一些数据，同时要求数据必须满足某些条件，比如表中约定任意一个type都必须要有“出行”这个值。

在人工操作时，有时会发生忘记导入某些数据。从上面的例子看，就是2这个类型下缺少了出行这个值。因此需要通过SQL查询快速找到这种问题。

2 查询分组中是否包含某些值

为了解决背景中提到的问题，我们会利用group by及聚合函数来解决。还是使用背景中的例子，我们对数据按类型分组，随后查看分组中是否包含某个值。

select type,sum(value = '出行') from config 
group by type

• 首先通过group by分组
• 接着通过聚合函数sum[1]求出分组下value为出行的行数，sum函数入参可以是一个表达式，我们得出满足表达式的行的数量
• 根据分析，求和后行数为0的为不满足条件的列。我们删选出后统一处理

由于条件中明确出行为必填值，那么在这个列表中我们还可以通过必填值，来找到不满足的type。我们限定好必填候选值，先看找出不包含这些候选值的列即可。 假设有多行候选数据，我们就要构造出多行数据。

• 通过union all[2] 构造当列多行候选数据

select '出行' union all
select '饮食'

• 查询包含候选数据的行，并去重类型type

select distinct type from config where value in
(
	select '出行' union all
	select '饮食'
)

• 最后从所有type中取出满足条件的type得出结果

两种方式都涉及到表中列名获取，为了获取列表我们可以通过
INFORMATION_SCHEMA.COLUMNS[3]来获取列明

select * from INFORMATION_SCHEMA.COLUMNS

这种方式读取数据表的描述信息[4]来查询表结构定义。类型的还可以查询一个表的其它描述性信息。

3 总结

列表(纵表)快速检查是否包含某些数据，结合分组和聚合函数来处理。有时候也可以通过union来构造单列多行数据。为了进一步简化工作可以继读取数据表的元信息。

参考资料

[1].聚合函数sum,https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_sum
[2].mysql union,https://dev.mysql.com/doc/refman/8.0/en/union.html
[3].https://dev.mysql.com/doc/refman/8.0/en/information-schema-columns-table.html
[4]https://dev.mysql.com/doc/refman/8.0/en/information-schema.html