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更复杂