每日一題：SQL之行轉(zhuǎn)列和列轉(zhuǎn)行
+---+----------+|id |login_date|+---+----------+|01 |2021-02-28||01 |2021-03-01||01 |2021-03-02||01 |2021-03-04||01 |2021-03-05||01 |2021-03-06||01 |2021-03-08||02 |2021-03-01||02 |2021-03-02||02 |2021-03-03||02 |2021-03-06||03 |2021-03-06|+---+----------+
select id,concat_ws(',',collect_list(login_date)) cwfrom datagroup by id;
+---+----------------------------------------------------------------------------+|id |cw |+---+----------------------------------------------------------------------------+|01 |2018-02-28,2018-03-01,2018-03-02,2018-03-04,2018-03-05,2018-03-06,2018-03-08||02 |2018-03-01,2018-03-02,2018-03-03,2018-03-06 ||03 |2018-03-06 |+---+----------------------------------------------------------------------------+
以上面SQL生成的數(shù)據(jù)為基準，執(zhí)行下列SQL：
select id, login_datefrom tlateral view explode(split(cw,',')) b AS login_date;
結(jié)果
+---+----------+|id |login_date|+---+----------+|01 |2018-02-28||01 |2018-03-01||01 |2018-03-02||01 |2018-03-04||01 |2018-03-05||01 |2018-03-06||01 |2018-03-08||02 |2018-03-01||02 |2018-03-02||02 |2018-03-03||02 |2018-03-06||03 |2018-03-06|+---+----------+
(長按三秒，即可進入)
后臺已放置一份精心整理的技術(shù)干貨，查看即可獲?。?/span> 后臺回復關(guān)鍵字：面試，帶你進入高手如云的學習交流群！ 推薦閱讀
評論
圖片
表情