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 明细表解决:

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
FROM test a
LEFT JOIN test_detail a1 ON a.code = a1.group_code
LEFT JOIN test_detail a2 ON a.code = a2.group_code
LEFT JOIN test_detail a3 ON a.code = a3.group_code
LEFT JOIN test_detail a4 ON a.code = a4.group_code
WHERE a1.field_name = 'name' AND a1.field_value = '张三'
AND a2.field_name = 'age' AND a1.field_value < '18'
AND a3.field_name = 'sex' AND a1.field_value IN ('男', '女')

缺点也是类似的, 如果想要一次性把所有字段都返回, 要为每个字段写一个 `left join`;