mysql行转列(列不固定)
使用场景:由于题目的选项按行存储在数据库中,并且选项数量不是固定的
使用sql语句如下
SELECT
questionitem.questionId,
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 1), ',', -1), '') AS '1',
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 2), ',', -1), '') AS '2',
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 3,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 3), ',', -1), '') AS '3',
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 4,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 4), ',', -1), '') AS '4',
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 5,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 5), ',', -1), '') AS '5',
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 6,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 6), ',', -1), '') AS '6',
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 7,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 7), ',', -1), '') AS '7',
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 8,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 8), ',', -1), '') AS '8',
IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 9,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 9), ',', -1), '') AS '9'
FROM
questionitem
GROUP BY
questionitem.questionId;
查询结果如下
sql分析:
SELECT
questionitem.questionId,
GROUP_CONCAT(questionitem.content) as OPTION_LIST,
length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 AS OPTION_LEN,
SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 2) as '获取到第二个逗号的记录',
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 2), ',', -1) as '获取到第二个逗号的记录的最后一个'
FROM
questionitem
GROUP BY
questionitem.questionId;
-- 求GROUP_CONCAT的元素个数 把,替换为空然后做减法
length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 AS OPTION_LEN
-- 获取指定的第几个元素,先获取第三个逗号左侧全部字符串,在获取右侧第一个逗号右侧全部字符串
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 3), ',', -1) as '获取到第三个逗号的记录的最后一个'
参考:
https://blog.csdn.net/weixin_45716993/article/details/103234724
如果这篇文章对你有用,可以关注本人微信公众号获取更多ヽ(^ω^)ノ ~
![微信公众号二维码](https://images.cnblogs.com/cnblogs_com/aeolian/1679458/o_wechat_gzh_qrcode.jpg)