avatar

mysql根据分隔符进行字段拆分
  1. 虽然已经有很多类似的写法,我这里还是自己写一写,有些分隔符分出的列数是不固定的,这时候怎么根据分隔符拆分出多列呢
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    insert into tpems.sp_questions_bank (id, title, option_a, option_b, option_c, option_d, option_e, answer1, answer2, parse, qtpye, diff, md5, subjectId, gradeId, knowledges, area, year, paperTpye, source, fromSite, isSub, isNormal, isKonw, tiid, Similarity, isunique, md52, s_type, s_qid, s_pid, s_user_id, s_user_name, s_option_number, s_status, s_create_time, s_last_update_time, s_is_exercise, s_select_number
    )
    SELECT
    null,
    t.title,
    IF(t.opi >= 1, t.option_a, '') a,
    IF(t.opi >= 2, t.option_b, '') b,
    IF(t.opi >= 3, t.option_c, '') c,
    IF(t.opi >= 4, t.option_d, '') d,
    IF(t.opi >= 5, t.option_e, '') e,
    t.answer,
    null,
    t.description,
    t.type,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    0,
    t.id,
    t.pid,
    t.user_id,
    t.user_name,
    t.option_number,
    t.status,
    t.create_time,
    t.last_update_time,
    t.is_exercise,
    t.select_number
    FROM
    (SELECT
    sp.id,
    sp.user_id,
    sp.type,
    sp.title,
    sp.user_name,
    sp.option_number,
    sp.answer,
    sp.description,
    sp.status,
    sp.create_time,
    sp.last_update_time,
    sp.is_exercise,
    sp.pid,
    sp.select_number,
    (LENGTH(sp.option) - LENGTH(REPLACE(sp.option, '^^', ''))) / 2 + 1 AS opi,
    if(sp.type in(4,5,6),sp.option,SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 1), '^^', - 1)) AS option_a,
    if(sp.type in(4,5,6),'',SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 2), '^^', - 1)) as option_b,
    if(sp.type in(4,5,6),'',SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 3), '^^', - 1)) as option_c,
    if(sp.type in(4,5,6),'',SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 4), '^^', - 1)) as option_d,
    if(sp.type in(4,5,6),'',SUBSTRING_INDEX(SUBSTRING_INDEX(sp.option, '^^', 5), '^^', - 1)) as option_e
    FROM
    tpems.sp_question sp) t;
    首先描述下我的sql的工作场景,有一列数据是用分隔符“^^”进行分隔的,一个字段里的值,然后需要把字段拆开,然后放到不同的option里面去,
    option有5个,然后字段里的分隔符的数目也是不固定的。
    这里我是确定了一下最大的上限值,最多只能写5个,然后这样就简单了。
    1
    (LENGTH(sp.option) - LENGTH(REPLACE(sp.option, '^^', ''))) / 2 + 1 AS opi
    确定了分隔符可以分出的列数。

然后判断选项的个数

1
2
3
4
5
IF(t.opi >= 1, t.option_a, '') a,
IF(t.opi >= 2, t.option_b, '') b,
IF(t.opi >= 3, t.option_c, '') c,
IF(t.opi >= 4, t.option_d, '') d,
IF(t.opi >= 5, t.option_e, '') e,

这里解释下if函数,if(表达式,a,b)
表达式如果为真则返回a,否则返回b
所以这里的判断就是,如果只能分出两个option则,3,4,5的值都是空
,就成功的将一列值扩充到多列

文章作者: 无知的小狼
文章链接: https://bytedance.press/2019/06/30/articles/2019/06/30/1561895167680.html/
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 无知的小狼

评论