1806628259554188_个人页

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