MySQL列轉行巧錄資料

壹頁書發表於2016-11-17
開發同學讓我幫忙看看一個資料怎麼錄合適。
原始的資料如下,要錄入到資料庫裡。


先取消Excel的單元格合併.


刪除第一行和第二行的資料,這些都是標題.
然後匯出CSV檔案,
使用文字檔案編輯器開啟CSV,替換 - 為 ,


然後 德塔貝斯 建個表。
再將CSV資料匯入.

  1. create table t(
  2.     id int primary key auto_increment,
  3.     starttime time,
  4.     endtime time,
  5.     d1 varchar(20),
  6.     d2 varchar(20),
  7.     d3 varchar(20),
  8.     d4 varchar(20),
  9.     d5 varchar(20),
  10.     d6 varchar(20),
  11.     d7 varchar(20)
  12. );


類似於紅框的部分,都是連續的節目,需要合併時間段。
下表是模擬開發同事最終錄入資料的表。

  1. create table target(
  2.         id int primary key auto_increment,
  3.         channel varchar(32) comment '電臺名稱',
  4.         d int comment '1-7 表示星期一或者星期二...',
  5.         starttime time comment '節目開始時間',
  6.         endtime time comment '節目結束時間',
  7.         program varchar(32) comment '節目名稱'
  8. );

錄入的SQL

  1. insert into target(channel,d,program,starttime,endtime)   
  2. select '中國之聲',1,d1,min(starttime),max(endtime) from (  
  3.     SELECT @gid := @cgid, @cgid := t.d1, if(@gid = @cgid, @rank,@rank := @rank + 1) AS rank, t.* from   
  4.     (select id,starttime,endtime,d1 from t  order by id) t ,(SELECT @gid := 1, @cgid := 1, @rank := 0) t2  
  5. ) t4 group by d1,rank  
  6. union all  
  7. select '中國之聲',2,d2,min(starttime),max(endtime) from (  
  8.     SELECT @gid := @cgid, @cgid := t.d2, if(@gid = @cgid, @rank,@rank := @rank + 1) AS rank, t.* from   
  9.     (select id,starttime,endtime,d2 from t  order by id) t ,(SELECT @gid := 1, @cgid := 1, @rank := 0) t2  
  10. ) t4 group by d2,rank  
  11. union all  
  12. select '中國之聲',3,d3,min(starttime),max(endtime) from (  
  13.     SELECT @gid := @cgid, @cgid := t.d3, if(@gid = @cgid, @rank,@rank := @rank + 1) AS rank, t.* from   
  14.     (select id,starttime,endtime,d3 from t  order by id) t ,(SELECT @gid := 1, @cgid := 1, @rank := 0) t2  
  15. ) t4 group by d3,rank  
  16. union all  
  17. select '中國之聲',4,d4,min(starttime),max(endtime) from (  
  18.     SELECT @gid := @cgid, @cgid := t.d4, if(@gid = @cgid, @rank,@rank := @rank + 1) AS rank, t.* from   
  19.     (select id,starttime,endtime,d4 from t  order by id) t ,(SELECT @gid := 1, @cgid := 1, @rank := 0) t2  
  20. ) t4 group by d4,rank  
  21. union all  
  22. select '中國之聲',5,d5,min(starttime),max(endtime) from (  
  23.     SELECT @gid := @cgid, @cgid := t.d5, if(@gid = @cgid, @rank,@rank := @rank + 1) AS rank, t.* from   
  24.     (select id,starttime,endtime,d5 from t  order by id) t ,(SELECT @gid := 1, @cgid := 1, @rank := 0) t2  
  25. ) t4 group by d5,rank  
  26. union all  
  27. select '中國之聲',6,d6,min(starttime),max(endtime) from (  
  28.     SELECT @gid := @cgid, @cgid := t.d6, if(@gid = @cgid, @rank,@rank := @rank + 1) AS rank, t.* from   
  29.     (select id,starttime,endtime,d6 from t  order by id) t ,(SELECT @gid := 1, @cgid := 1, @rank := 0) t2  
  30. ) t4 group by d6,rank  
  31. union all  
  32. select '中國之聲',7,d7,min(starttime),max(endtime) from (  
  33.     SELECT @gid := @cgid, @cgid := t.d7, if(@gid = @cgid, @rank,@rank := @rank + 1) AS rank, t.* from   
  34.     (select id,starttime,endtime,d7 from t  order by id) t ,(SELECT @gid := 1, @cgid := 1, @rank := 0) t2  
  35. ) t4 group by d7,rank; 

檢視結果



這個方法看著步驟比較多,其實還是很省事兒的.
主要的工作就是把Excel匯入資料庫,匯入之後的合併,使用同一個SQL改改就好。沒啥工作量。
否則使用JAVA解析,還需要自己合併時間段.也不是一個輕鬆的事情.

該同學需要錄入 這種Excel 大致20多個..我覺得我這個方法核心步驟可以複用,應該還是很有效率的。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29254281/viewspace-2128626/,如需轉載,請註明出處,否則將追究法律責任。

相關文章