1806628259554188个人页面-阿里云开发者社区

个人头像照片 1806628259554188 TA的个人档案

个人介绍

暂无个人介绍

擅长的技术

获得更多能力
通用技术能力:

暂时未有相关通用技术能力~

云产品技术能力:

暂时未有相关云产品技术能力~

阿里云技能认证

详细说明
  • 高分内容
  • 最新动态
  • 文章
  • 问答
正在加载, 请稍后...
暂无更多信息

2020年04月

  • 04.21 16:28:18
    回答了问题 2020-04-21 16:28:18

    【每日一题】SQL 知识大测验 | 持续更新

    insert overwrite TABLE q11 values('1','monday','chinese','Y','Y'), ('2','tuesday','math',NULL,'Y'), ('3','wednesday','chinese','Y',NULL), ('4','wednesday','math',NULL,'Y'), ('5','wednesday','english',NULL,'Y'), ('6','friday','english',NULL,NULL);

    with q11_temp as ( SELECT t,str_to_map(concat_ws('&',collect_set(concat_ws('=',day,subj))),'&','=') as mp from ( SELECT day,t,concat_ws(',',collect_set(subj)) subj from ( SELECT day,'morning' t,if(morning='Y',subject,morning) subj FROM q11 union ALL SELECT day,'afternoon' t,if(afternoon='Y',subject,afternoon) subj FROM q11 ) a WHERE subj IS NOT NULL GROUP BY day,t

    ) b GROUP BY t
    

    )

    select t,if(array_contains(map_keys(mp),'monday'),mp['monday'],'noclass'), if(array_contains(map_keys(mp),'tuesday'),mp['tuesday'],'noclass'), if(array_contains(map_keys(mp),'wednesday'),mp['wednesday'],'noclass'), if(array_contains(map_keys(mp),'thursday'),mp['thursday'],'noclass'), if(array_contains(map_keys(mp),'friday'),mp['friday'],'noclass'), if(array_contains(map_keys(mp),'saturday'),mp['saturday'],'noclass'), if(array_contains(map_keys(mp),'sunday'),mp['sunday'],'noclass') from q11_temp

    踩0 评论0
正在加载, 请稍后...
滑动查看更多
正在加载, 请稍后...
暂无更多信息
  • 回答了问题 2020-04-21

    【每日一题】SQL 知识大测验 | 持续更新

    insert overwrite TABLE q11 values('1','monday','chinese','Y','Y'), ('2','tuesday','math',NULL,'Y'), ('3','wednesday','chinese','Y',NULL), ('4','wednesday','math',NULL,'Y'), ('5','wednesday','english',NULL,'Y'), ('6','friday','english',NULL,NULL);

    with q11_temp as ( SELECT t,str_to_map(concat_ws('&',collect_set(concat_ws('=',day,subj))),'&','=') as mp from ( SELECT day,t,concat_ws(',',collect_set(subj)) subj from ( SELECT day,'morning' t,if(morning='Y',subject,morning) subj FROM q11 union ALL SELECT day,'afternoon' t,if(afternoon='Y',subject,afternoon) subj FROM q11 ) a WHERE subj IS NOT NULL GROUP BY day,t

    ) b GROUP BY t
    

    )

    select t,if(array_contains(map_keys(mp),'monday'),mp['monday'],'noclass'), if(array_contains(map_keys(mp),'tuesday'),mp['tuesday'],'noclass'), if(array_contains(map_keys(mp),'wednesday'),mp['wednesday'],'noclass'), if(array_contains(map_keys(mp),'thursday'),mp['thursday'],'noclass'), if(array_contains(map_keys(mp),'friday'),mp['friday'],'noclass'), if(array_contains(map_keys(mp),'saturday'),mp['saturday'],'noclass'), if(array_contains(map_keys(mp),'sunday'),mp['sunday'],'noclass') from q11_temp

    踩0 评论0
正在加载, 请稍后...
滑动查看更多