hive笔试题（sase when else end）列转行

 

面试原题

建表语句：

CREATE TABLE `teach_class` (
  `t_num` varchar(12) DEFAULT NULL,
  `week_num` varchar(12) NOT NULL,
  `flag` varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

答案：

SELECT t_num,
SUM(CASE week_num WHEN 1 THEN 1 ELSE NULL END) AS '星期一',
SUM(CASE week_num WHEN 2 THEN 1 ELSE NULL END) AS '星期二',
SUM(CASE week_num WHEN 3 THEN 1 ELSE NULL END) AS '星期三'
FROM teach_class
GROUP BY t_num;