mysql 行转列 多行转一行
2022-7-7 11:53:44 星期四
场景, 因为某种特殊原因, 有张附表被设计成了"万能表", 如下:
主表: test
附表: test_detail
现在后台需要加筛选功能, 要跟其他表一起进行联结查询, 还要返回这个表中的其他字段(比如按照性别筛选, 但要返回姓名,年龄,身高等字段)
1. 如果是不需要返回此表的字段, 仅用于筛选那就可以用 where exists 语句解决
select a.* from test a where exists(select 1 from test_detail b where b.group_code = a.code and field_name ='age' and field_value < 21)
2. 如果是既要筛选又要返回字段, 那就得把这个表行转列了, 也就是把多行转为一行
2.1 利用 group by + group_concat + case_when
select a.*, aa.* from test a left join ( select group_code, group_concat(case when field_name = 'name' then field_value end) name, group_concat(case when field_name = 'age' then field_value end) age, group_concat(case when field_name = 'sex' then field_value end) sex, group_concat(case when field_name = 'height' then field_value end) height from test_detail group by group_code ) as aa on a.code = aa.group_code -- where aa.age = 20
结果:
2.2 利用 group by + group_concat + if
select a.*, aa.* from test a left join ( select group_code, group_concat(if(field_name = 'name',field_value,'') separator '') name, group_concat(if(field_name = 'age',field_value,'') separator '') age, group_concat(if(field_name = 'sex',field_value,'') separator '') sex, group_concat(if(field_name = 'height',field_value,'') separator '') height from test_detail group by group_code ) as aa on a.code = aa.group_code -- where aa.age = 20
结果跟上边2.1一样, 但需要注意, group_concat 默认连接符是逗号",", 所以这里指定了分隔符为空字符串.
另外, 在使用这种"万能表"过程中总结出以下缺点, 可供参考:
1. 字段类型只能是字符串, 如需区分真实类型, 需要单独起一列去记 2. 字段长度要使用所有可能值中最长的 3. 筛选时, 要用 where exists instr 等函数, 对索引不友好 4. 详情和列表, 每次都要重新组装后返回, 耗费cpu(否则就用上边的方法, 把压力转移到数据库服务器的cpu) 5. 像id, group_code, create_time, status, deleted 等一些公共字段, test_detail表中每一行都得存, 占用了更多的存储空间, 而且更新时也要同步更新 6. 数值形和字符串型存在一个字段, 理论上10和10.00 应该是等值的, 但都存成字符串, 这俩就不一样了, 要用instr()函数去比对, 就会使筛选SQL更复杂
=============2023-8-14 更新
还可以通过多次 join 明细表解决:
1 SELECT a.code, a.class_name, a1.field_value AS NAME, a2.field_value AS age, a3.field_value AS sex, a4.field_value AS height 2 FROM test a 3 LEFT JOIN test_detail a1 ON a.code = a1.group_code 4 LEFT JOIN test_detail a2 ON a.code = a2.group_code 5 LEFT JOIN test_detail a3 ON a.code = a3.group_code 6 LEFT JOIN test_detail a4 ON a.code = a4.group_code 7 WHERE a1.field_name = 'name' AND a1.field_value = '张三' 8 AND a2.field_name = 'age' AND a1.field_value < '18' 9 AND a3.field_name = 'sex' AND a1.field_value IN ('男', '女')
缺点也是类似的, 如果想要一次性把所有字段都返回, 要为每个字段写一个 `left join`;