oracle行轉列、列轉行、連續日期數字實現方式及mybatis下實現方式

funnyZpC發表於2018-11-18
OracleMyBatis
轉載請註明出處：https://www.cnblogs.com/funnyzpc/p/9977591.html
    九月份複習，十月份考試，十月底一直沒法收心，趕在十一初 由於不可抗拒的原因又不得不重新找工作就；欸~，
又是一番折騰，從入職到現在，可又沒法閒下來了...

這次就簡單介紹下oracle資料庫下如何實現行轉列、列轉行及此在mybatis中的實現方式，就具體用法我就不詳細說了，主要介紹下實戰中所碰到的坑~

行轉列大致的實現方式大致有三種

  • 使用條件判斷(case when...)+聚合函式方式
    這種方式sql難度低，但是容易給DB造成較大的開銷，畢竟每個最終的列的值都是一個聚合函式的值，同時非聚合列也要隨聚合列而定，大多數情況下可能需要將多個子查詢連表查；至於在mybatis中的時候就非常簡單了，這裡就不再綴訴哈~
  • 使用pivot函式方式
    此種方式有一個缺點是：一次查詢只能對一個列的資料進行拆分(成多列)，如需對多列拆分，則可行的方式是做多個查詢，一個查詢拆分一列(同時保留連線欄位)，
同時，這裡需要主要的是 在 pivot 下的in中不可以是一個子查詢，記得當時除錯的時候怎麼除錯就是報錯，後來翻了翻英文網站的說明，給的大致意思是這個pivot內拆分的目標欄位不可以是一個子查詢，只能是寫死的列 ，其實也不是不可以，只是官方給出的說法是：要實現動態列，只能使用xml的方式(說了等於沒說)。
    ok，待SQL除錯完畢，copy到應用中使用的時候還是會報錯，這個時候就需要注意到mybatis的一些基本約定，就是傳值方式，mybatis的傳值方式大致有#{value}和${value}兩種方式
，#符號定義的值其實是將整個值物件交給DBMS去處理，而$符號定義的值是將值直接放入到語句內，對DBMS來說，後者更相當於一個定值，所以將povit應用在mybatis中需要使用$的值定義方式(僅在in內這樣使用，pivot外視業務情況而定)。
    同時，因為使用$符號定義值的方式不時mybatis並不會將傳入的值作為一個String字元來處理，如果是日期及其他型別儘量使用string的方式將值傳入，拆分的值若是日期型別的需要
使用to_date()函式或者_to_char()_函式進轉換才是，如果目標值就是String型別且一定要將整個宣告值使用單引號引起來('#{value}'),另外，拆分的目標列也是可以定義別名的，不然DB
又會給出pivot內不能使用動態語句的錯誤，需要使用CDATA標籤做xml轉義，最終的樣子大致就是這樣 ： in (to_date('${value}','b') as <![CDATA[${key}]],to_date('${value}','b') as <![CDATA[${key}]]>)
  • 使用wm_concat視窗函式的方式
    這種方式其實很容易將sql寫的很複雜，主要有兩個方面：一個是wm_concat 函式一定要指定partition by和order by的欄位，就是組內分組和組內排序方式，不然最終的結果資料混亂不堪(除非這對你的業務不重要)，另一個原因是視窗函式本身是不會聚合行，所以，組內拼接也是順序拼接，所以需要做的就是將非結果行去掉才是，這個時候就需要使用另一個視窗函式rank(),這個函式是對組內做排序，由於目標是實現行轉列，所以此時就需要將rank()的排序方式改為desc，而後再做一個子查詢將rank的值為非1的全去掉。
    由於使用wm_concat的結果是單列，需要此時需要根據逗號做分隔，擷取為指定的列，wm_concat函式使用起來並不難，但是面對實際業務的時候，若是大資料量就得慎重咯，因為函式使用的越多就越容易造成DB的開銷，這個是不容忽視的。
    wm_concat在mybatis中的使用並沒有障礙，需要注意的細節是拼接欄位可能不是varchar型別，而是blob(大欄位)型別，需要用to_char()函式做轉換，同時wm_concat函式只能按照逗號進行內容拼接，如果字元包含逗號，建議將wm_concat內的源欄位拼接一個唯一字元。

實現列轉行的方式

  • 使用unpivot函式的方式
    此種方式同以上的pivot的方式相反，不過好處是他不會有行轉列的單列問題，至於在mybatis中的使用，建議參照以上pivot的方式
  • 使用_regexp_substr_正則函式的方式
    其實這個函式的說的意義並不大，因為regexp_substr函式在拆分十行資料的時候DB的開銷就顯現出來了，如果不得已要用的話首先推薦使用程式來處理，如果使用資料庫處理，建議將
目標資料拆分多個組來做，建議不要超過十個，而且拆分的列的資料複雜度不能太高(字元太長，正則太複雜)，在mybatis下的使用中只需要注意下目標列的型別，必要時使用to_char函式進行轉換才是~

連續日期的實現方式

  • 使用level+connect by+rownum實現
    相信大家已經猜出一部分了，這裡簡要說下這三個東東大概是幹什麼：level實現級的序號增加，connect by 實現遞迴，rownum則配合運算結果加減，以下就給出具體的SQL，可
直接執行。    
    SELECT TO_DATE('2018-10-28','YYYY-MM-DD')+ROWNUM - 1 DT
    FROM DUAL CONNECT BY LEVEL <=(TO_DATE('2018-11-20','YYYY-MM-DD')-TO_DATE('2018-10-28','YYYY-MM-DD')+1)
貌似不使用rownum也是可以的，各位可以嘗試下哈~

####    最後，原本在寫部落格前在個人電腦中跑一個oracle的，實際安裝的過程中發現oracle的安裝包實在是太大了，許久不安裝，安裝過程難免也會出現各種問題，遂~就放棄了，改天我會盡量將語句都放出來，以饗廣大讀者哈~，至於行轉列列轉行的實現方式就給個粗糙的sql大家嘗試著看哈~~

-- 行轉列
SELECT * from
(
SELECT tt1.SAP_ID,TT1.dt,TT1.EFF from (
SELECT t1.SAP_ID,T1.DT,nvl(T2.EFFECTIVE,0) eff from
(
SELECT A1.SAP_ID,mr.dt from
(SELECT DISTINCT SAP_ID from DATA_EMP_ATTENDANCE) a1,
(SELECT TO_DATE('2018-11-01','YYYY-MM-DD')+ROWNUM - 1 DT
FROM DUAL CONNECT BY LEVEL <=(TO_DATE('2018-11-15','YYYY-MM-DD')-TO_DATE('2018-11-01','YYYY-MM-DD')+1)) mr
) t1
LEFT JOIN
(
SELECT SAP_ID,BEGIN_DATE,1 effective from DATA_EMP_ATTENDANCE
) t2
ON T2.SAP_ID = T1.SAP_ID AND T2.BEGIN_DATE = T1.DT
ORDER BY t1.dt DESC
) tt1
) pivot
(max(eff) for dt in (to_date('2018-11-05','yyyy-mm-dd') d1,to_date('2018-11-12','yyyy-mm-dd') d2,to_date('2018-11-12','yyyy-mm-dd') d3));
```

現在是 2018-11-18 16:22:45 ,我得準備下一篇部落格了，各位下午好~smile

相關文章