sql - 使用反向行值 SQL 创建视图

Question

我有类似的数据

 name| col1 | col2 | col3 | col4 | col4 | col5 | 
 rv  | rv1  | rv2  | rv3  | rv4  |      |      |
 sgh | sgh1 | sgh2 |      |      |      |      |
 vik | vik1 | vik2 | vik3 | vik4 |vik5  |vik6  |
 shv | shv1 | shv2 | shv3 | shv4 |shv5  |      |

表名：emp_data

创建视图以获取数据

 name| col1 | col2 | col3 | col4 | col4 | col5 | 
 rv  | rv4  | rv3  | rv2  | rv1  |      |      |
 sgh | sgh2 | sgh1 |      |      |      |      |
 vik | vik6 | vik5 | vik4 | vik3 |vik2  |vik1  |
 shv | shv5 | shv4 | shv3 | shv2 |shv1  |      |

标签： sqlsql-view

Answer 1

MySql 8 支持 LATERAL，这样您可以按位置对值进行排序并有条件地将它们聚合回来。

with tbl(name, col1, col2, col3 ,col4 ,col5 , col6) as
( 
select 'rv ','rv1 ','rv2 ','rv3 ','rv4 ',null,null union all
select 'sgh','sgh1','sgh2', null,null,null,null union all
select 'vik','vik1','vik2','vik3','vik4','vik5','vik6' union all
select 'shv','shv1','shv2','shv3','shv4','shv5', null
)
select tbl.name, t.* 
from tbl
, lateral (
   select
     max(case n when 1 then val end) col1,
     max(case n when 2 then val end) col2,
     max(case n when 3 then val end) col3,
     max(case n when 4 then val end) col4,
     max(case n when 5 then val end) col5,
     max(case n when 6 then val end) col6
   from (
     select row_number() over( order by n) n, val
     from (
       select case when col1 is null then 99 else 6 end n, col1 val union all
       select case when col2 is null then 99 else 5 end n, col2 val union all
       select case when col3 is null then 99 else 4 end n, col3 val union all
       select case when col4 is null then 99 else 3 end n, col4 val union all
       select case when col5 is null then 99 else 2 end n, col5 val union all
       select case when col6 is null then 99 else 1 end n, col6 val 
       ) t
    ) t
) t

数据库<>文件