源数据

+-----------+-------+-------+-------+
| grade_id  |  yw   |  sx   |   zz  |
+-----------+-------+-------+-------+
| 1         | 98.0  | 0.0   | 0.0   |
| 2         | 0.0   | 80.0  | 78.0  |
| 5         | 88.0  | 66.0  | 99.0  |
+-----------+-------+-------+-------+

1,98.0,0.0,0.0
2,0.0,80.0,78.0
5,88.0,66.0,99.0

想得到的结果:

 +--------------+------------------+---------------+
|    grade_id  |  subject_name    |  max_score    |
+--------------+------------------+---------------+
| 1            | 语文               | 98.0        |
| 2            | 数学               | 80.0        |
| 2            | 政治               | 78.0        |
| 5            | 语文               | 88.0        |
| 5            | 数学               | 66.0        |
| 5            | 政治               | 99.0        |
+--------------+------------------+---------------+

建表

create table if not exists test.ms2 (grade_id int, yw double, ss double, zz double)
row format delimited fields terminated by ",";

导入数据

load data local inpath "/doit16/ms2.txt" into table ms2;

解法1:

SELECT grade_id
    ,split(cs, ':') [0] AS subject_name
    ,split(cs, ':') [1] AS score
FROM (
    SELECT grade_id
        ,concat_ws(',', cast(yw AS string), cast(ss AS string), cast(zz AS string)) AS sub_score
    FROM (
        SELECT grade_id
            ,concat_ws(':', 'yw', cast(yw AS string)) AS yw
            ,concat_ws(':', 'ss', cast(ss AS string)) AS ss
            ,concat_ws(':', 'zz', cast(zz AS string)) AS zz
        FROM ms2
        ) t1
    ) t2 LATERAL VIEW explode(split(sub_score, ',')) susc AS cs;