MySQL不确定字段建表(横表纵表转换)
前两天难得有个朋友留言问了一个数据库建表的问题，也欢迎大家多留言多提问。大概是需要建表的字段不确定，可能后续会根据业务的需求增加一些字段，然后这位朋友其实也给了一个使用纵表的解决方案。其实我对建表也了解不多，但是不影响嘛，就像之前美团老大兴哥对"我不会，但是我可以学"这个高度赞扬一样，我遇到这种对我来说的新问题也是很兴奋的，于是，我也可以去学，查了一些资料，自己再思考了一下，于是出现了这一篇短文。
我先描述一下数据，大概如下，我们需要对每个人每个月的工资条进行存储，包括基础的信息（姓名、月份、部门、基本工资和各项常规税费）和每个人特殊的费用项目，比如差旅费、招待费等等。前面基础信息部分因为字段是固定的，所以固定字段建表就可以了；但是其他费用项目每个月都不一样，而且每个月可能还会新增费用项目，所以如果固定建表就需要每个月增加字段，而且很多字段只有个别人需要，对其他人来说是很浪费存储空间的。
所有这个时候就需要想办法来解决这个问题了。思路是使用横纵两个表就行存储，确定的字段就用常规的横表进行存储，其他费用项目就用纵表就行存储。还有一个思路就是其他费用项目单独用一个json字段进行存储，这样也很自由，但是如果数据量很大的时候，查询起来速度会有点影响。
思路就是这样，下面就演示一下横纵表之间的查询转换，和json格式字段的查询。
【横表转纵表】
横表就是我们日常最常见的表格，思路是把每个项目的金额单独查询出来然后连接到一起，下面是横表转纵表的SQL查询语句↓
SELECT 姓名,月份,部门,"基本工资" AS 项目费用,基本工资 AS 金额 FROM `工资表`
UNION ALL
SELECT 姓名,月份,部门,"绩效工资" AS 项目费用,绩效工资 AS 金额 FROM `工资表`
UNION ALL
SELECT 姓名,月份,部门,"社保" AS 项目费用,社保 AS 金额 FROM `工资表`
UNION ALL
SELECT 姓名,月份,部门,"公积金" AS 项目费用,公积金 AS 金额 FROM `工资表`
UNION ALL
SELECT 姓名,月份,部门,"个税" AS 项目费用,个税 AS 金额 FROM `工资表`【纵表转横表】
上面生成的结果就是纵表了，然后我们创建一张纵表，格式如下↓
下面是纵表转横表的SQL查询语句，思路是把每个费用项目查询出来，结果只有指定项的字段有数据，再使用MAX聚合函数结合GROUP BY就可以得到我们想要的形式了↓
SELECT 姓名,月份,部门,
MAX(CASE 项目费用 WHEN '基本工资' THEN 金额 ELSE NULL END) AS 基本工资,
MAX(CASE 项目费用 WHEN '绩效工资' THEN 金额 ELSE NULL END) AS 绩效工资,
MAX(CASE 项目费用 WHEN '社保' THEN 金额 ELSE NULL END) AS 社保,
MAX(CASE 项目费用 WHEN '公积金' THEN 金额 ELSE NULL END) AS 公积金,
MAX(CASE 项目费用 WHEN '个税' THEN 金额 ELSE NULL END) AS 个税
FROM
工资表纵表
GROUP BY
姓名,月份,部门这个时候如果我们需要增加几项特殊的费用，比如一项差旅费，一项高温补贴费，一项疫情补贴费，SQL查询和结果如下↓
SELECT 姓名,月份,部门,
MAX(CASE 项目费用 WHEN '基本工资' THEN 金额 ELSE NULL END) AS 基本工资,
MAX(CASE 项目费用 WHEN '绩效工资' THEN 金额 ELSE NULL END) AS 绩效工资,
MAX(CASE 项目费用 WHEN '社保' THEN 金额 ELSE NULL END) AS 社保,
MAX(CASE 项目费用 WHEN '公积金' THEN 金额 ELSE NULL END) AS 公积金,
MAX(CASE 项目费用 WHEN '个税' THEN 金额 ELSE NULL END) AS 个税,
MAX(CASE 项目费用 WHEN '差旅费' THEN 金额 ELSE NULL END) AS 差旅费,
MAX(CASE 项目费用 WHEN '高温补贴费' THEN 金额 ELSE NULL END) AS 高温补贴费,
MAX(CASE 项目费用 WHEN '疫情补贴费' THEN 金额 ELSE NULL END) AS 疫情补贴费
FROM
工资表纵表
GROUP BY
姓名,月份,部门【JSON格式读取】
上面使用的是横纵两个表进行存储的，现在可以在横标里面加一个JSON格式字段用于灵活存储其他费用，这样就可以使用一个表存储就行了，表格内容如下↓
然后使用获取JSON格式的SQL语句就可以查询出对应的字段了，关于JSON格式创建和查询，后面再单独拿一篇来讲，这里的SQL语句如下↓
SELECT
姓名,
月份,
部门,
基本工资,
绩效工资,
社保,
公积金,
个税,
json_extract(others,"$.chailv") AS 差旅费,
others->'$.gaowen' as 高温费,
others->'$.yiqing' as 疫情补助
FROM
工资表好了，今天就到这里，欢迎多留言多提问~
End