select
row_number()over()as category,
name,
activity
from test
where activity !=''
1 张三 吃饭
2 王五 打豆豆
3 赵四 唱歌
3.3 将上两步结果进行关联
select
tab_test_origin.name,
tab_category.activity
from
(
select
sum(flag)over(order by rownum) as category,
name,
activity
from
(
select
row_number()over() as rownum,
case when activity =='' then 0 else 1 end as flag,
name,
activity
from test
) tab_test
) tab_test_origin
inner join
(
select
row_number()over() as category,
name,
activity
from test where activity !=''
) as tab_category
on tab_test_origin.category=tab_category.category