国产传媒-色哟哟,日韩无码垃圾

目錄：
一、sqlserver列轉(zhuǎn)行、行轉(zhuǎn)列詳解
二、Oracle列轉(zhuǎn)行、行轉(zhuǎn)列詳解
三、DB2列轉(zhuǎn)行、行轉(zhuǎn)列詳解
四、MYSQL列轉(zhuǎn)行、行專(zhuān)列詳解
五、練習(xí)數(shù)據(jù)

練習(xí)數(shù)據(jù)在文末，使用時(shí)需要修改表名，和建表語(yǔ)句

每次學(xué)習(xí)新語(yǔ)法時(shí)，我習(xí)慣性的在四種數(shù)據(jù)庫(kù)中逐一練習(xí)，這個(gè)是職業(yè)習(xí)慣哈哈。雖然去年寫(xiě)過(guò)oracle,db2列轉(zhuǎn)行文章，不過(guò)……感覺(jué)除了我沒(méi)人可以看懂，附上去年文章的截圖，不過(guò)說(shuō)實(shí)話(huà)DB2列轉(zhuǎn)行使用跟其他數(shù)據(jù)庫(kù)差距還是挺大。

靈機(jī)一動(dòng)，發(fā)現(xiàn)還可以附帶可以把with函數(shù)學(xué)習(xí)了。上面都是個(gè)人感悟，現(xiàn)在我們一起學(xué)習(xí)Mysql Oracle sqlserv DB2列轉(zhuǎn)行、行轉(zhuǎn)列的使用吧。
上班的時(shí)候公司開(kāi)發(fā)很驚訝說(shuō)“你居然會(huì)用列轉(zhuǎn)行”

一、sqlserver列轉(zhuǎn)行、行轉(zhuǎn)列詳解

我們看下這張表,總共是26列，1547行數(shù)據(jù)

select  *  from water_quality

1、列轉(zhuǎn)行unpivot

UNPIVOT函數(shù)的格式如下：

UNPIVOT([轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對(duì)應(yīng)的列名] for [轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對(duì)應(yīng)的列名] 
in ([轉(zhuǎn)換為行的列1],[轉(zhuǎn)換為行的列2],[轉(zhuǎn)換為行的列3],...[轉(zhuǎn)換為行的列N]))

函數(shù)講解：

[轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對(duì)應(yīng)的列名]這個(gè)是進(jìn)行列轉(zhuǎn)行的列其數(shù)據(jù)值在轉(zhuǎn)換為行后的列名稱(chēng)，就是要轉(zhuǎn)換的列對(duì)應(yīng)的數(shù)據(jù)值用什么字段表示
[轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對(duì)應(yīng)的列名]這個(gè)是進(jìn)行列轉(zhuǎn)行的列其列名在轉(zhuǎn)換為行后的列名稱(chēng)，就是要轉(zhuǎn)換的列用什么字段表示
[轉(zhuǎn)換為行的列]這個(gè)是聲明哪些列要進(jìn)行列轉(zhuǎn)行，就是要轉(zhuǎn)換為行的列名逐一列出來(lái)

問(wèn)題1：把PJJG后的字段轉(zhuǎn)換為行


select 
//NF, YF, HL, DMMC, PJJG ----這里不能單獨(dú)列幾個(gè)字段，一定要用select *,至于結(jié)果差異大家可以運(yùn)行后體會(huì)
* from water_quality
unpivot(  
//　　 POLLUTEVALUE for CODE_POLLUTE---上面字段無(wú)中括號(hào)就報(bào)錯(cuò)，按照下面有中括號(hào)的格式就不報(bào)錯(cuò)。不知道是不是數(shù)據(jù)庫(kù)不兼容
 [POLLUTEVALUE] for [CODE_POLLUTE]
 IN(
JCXM_PH ,   JCXM_RYL,JCXM_GMSYZS ,JCXM_SHXYL ,JCXM_AD ,JCXM_SYL,JCXM_HFF,JCXM_GONG  ,JCXM_QIAN  ,JCXM_GE ,JCXM_YLZBMHXJ  ,JCXM_LUO,JCXM_FHW,JCXM_ZL ,JCXM_QHW,JCXM_LHW,JCXM_SHEN  ,JCXM_HXXYL ,
JCXM_TONG  ,JCXM_XIN,JCXM_XI ))
as a--------#as一定要有，沒(méi)有就報(bào)錯(cuò)，我也不知道為什么。可能是特定的語(yǔ)法格式吧

[轉(zhuǎn)換為行的**列值**在轉(zhuǎn)換后對(duì)應(yīng)的列名]對(duì)應(yīng)POLLUTEVALUE
[轉(zhuǎn)換為行的**列名**在轉(zhuǎn)換后對(duì)應(yīng)的列名]對(duì)應(yīng)CODE_POLLUTE
[轉(zhuǎn)換為行的列]對(duì)應(yīng)JCXM_PH , JCXM_RYL,JCXM_GMSYZS ,JCXM_SHXYL ,JCXM_AD ,JCXM_SYL,JCXM_HFF,JCXM_GONG  ,JCXM_QIAN  ,JCXM_GE ,JCXM_YLZBMHXJ  ,JCXM_LUO,JCXM_FHW,JCXM_ZL,JCXM_QHW,JCXM_LHW,JCXM_SHEN  ,JCXM_HXXYL ,JCXM_TONG  ,JCXM_XIN,JCXM_XI

我們看下轉(zhuǎn)換后的結(jié)果

2、行轉(zhuǎn)列PIVOT

PIVOT函數(shù)的格式如下

PIVOT(<聚合函數(shù)>([聚合列值]) FOR [行轉(zhuǎn)列前的列名]
 IN([行轉(zhuǎn)列后的列名1],[行轉(zhuǎn)列后的列名2],[行轉(zhuǎn)列后的列名3],.......[行轉(zhuǎn)列后的列名N]))

函數(shù)講解：

<聚合函數(shù)>就是我們使用的SUM,COUNT,AVG等Sql聚合函數(shù)，也就是行轉(zhuǎn)列后計(jì)算列的聚合方式。
[聚合列值]要進(jìn)行聚合的列名
[行轉(zhuǎn)列前的列名]這個(gè)就是需要將行轉(zhuǎn)換為列的列名。
[行轉(zhuǎn)列后的列名]這里需要聲明將行的值轉(zhuǎn)換為列后的列名，因?yàn)檗D(zhuǎn)換后的列名其實(shí)就是轉(zhuǎn)換前行的值，所以上面格式中的[行轉(zhuǎn)列后的列名1],[行轉(zhuǎn)列后的列名2],[行轉(zhuǎn)列后的列名3],......[行轉(zhuǎn)列后的列名N]其實(shí)就是[行轉(zhuǎn)列前的列名]每一行的值。

問(wèn)題2：把CODE_POLLUTE污染物編碼字段以列的方式顯示

解答1：
 ---water_quality_temp這個(gè)表是問(wèn)題1查詢(xún)后保存的結(jié)果表啦
select *
from water_quality_temp 
PIVOT(max(POLLUTEVALUE) FOR [CODE_POLLUTE] -----#max換成count會(huì)看見(jiàn)不同的結(jié)果
IN(JCXM_PH ,    JCXM_RYL,JCXM_GMSYZS ,JCXM_SHXYL ,JCXM_AD ,JCXM_SYL,JCXM_HFF,JCXM_GONG  ,JCXM_QIAN  ,JCXM_GE ,JCXM_YLZBMHXJ  ,JCXM_LUO,JCXM_FHW,JCXM_ZL ,JCXM_QHW,JCXM_LHW,JCXM_SHEN  ,JCXM_HXXYL ,
JCXM_TONG  ,JCXM_XIN,JCXM_XI)) AS T 

解答2：
-----如果沒(méi)有將問(wèn)題1結(jié)果報(bào)錯(cuò)，可以這樣寫(xiě)
with water_quality_temp as(
select 
//NF, YF, HL, DMMC, PJJG ----這里不能單獨(dú)列幾個(gè)字段，一定要用select *,至于結(jié)果差異大家可以運(yùn)行后體會(huì)
* from water_quality
unpivot(  
//　　 POLLUTEVALUE for CODE_POLLUTE---上面字段無(wú)中括號(hào)就包括，按照下面有中括號(hào)的格式就不報(bào)錯(cuò)。不知道是不是不兼容
 [POLLUTEVALUE] for [CODE_POLLUTE]
 IN(JCXM_PH ,   JCXM_RYL,JCXM_GMSYZS ,JCXM_SHXYL ,JCXM_AD ,JCXM_SYL,JCXM_HFF,JCXM_GONG  ,JCXM_QIAN  ,JCXM_GE ,JCXM_YLZBMHXJ  ,JCXM_LUO,JCXM_FHW,JCXM_ZL ,JCXM_QHW,JCXM_LHW,JCXM_SHEN  ,JCXM_HXXYL ,
JCXM_TONG  ,JCXM_XIN,JCXM_XI ))
as a)----#as一定要有，沒(méi)有就報(bào)錯(cuò)
select *
from water_quality_temp 
PIVOT(max(POLLUTEVALUE) FOR [CODE_POLLUTE] IN(JCXM_PH , JCXM_RYL,JCXM_GMSYZS ,JCXM_SHXYL ,JCXM_AD ,JCXM_SYL,JCXM_HFF,JCXM_GONG  ,JCXM_QIAN  ,JCXM_GE ,JCXM_YLZBMHXJ  ,JCXM_LUO,JCXM_FHW,JCXM_ZL ,JCXM_QHW,JCXM_LHW,JCXM_SHEN  ,JCXM_HXXYL ,
JCXM_TONG  ,JCXM_XIN,JCXM_XI)) AS T

- <聚合函數(shù)>本例中的max
- [聚合列值]本例中的POLLUTEVALUE
- [行轉(zhuǎn)**列前**的列名]本例中的CODE_POLLUTE
- [行轉(zhuǎn)**列后**的列名]本例中的CXM_PH , JCXM_RYL,JCXM_GMSYZS ,JCXM_SHXYL ,JCXM_AD ,JCXM_SYL,JCXM_HFF,JCXM_GONG  ,JCXM_QIAN  ,JCXM_GE ,JCXM_YLZBMHXJ  ,JCXM_LUO,JCXM_FHW,JCXM_ZL ,JCXM_QHW,JCXM_LHW,JCXM_SHEN  ,JCXM_HXXYL ,JCXM_TONG  ,JCXM_XIN,JCXM_XI

我們看下轉(zhuǎn)換后的結(jié)果

二、Oracle列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、Oracle列轉(zhuǎn)行unpivot

UNPIVOT函數(shù)的格式如下：

UNPIVOT([轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對(duì)應(yīng)的列名] for [轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對(duì)應(yīng)的列名] 
in ([轉(zhuǎn)換為行的列1],[轉(zhuǎn)換為行的列2],[轉(zhuǎn)換為行的列3],...[轉(zhuǎn)換為行的列N]))

函數(shù)的講解
Oracle列轉(zhuǎn)行函數(shù)的講解跟sqlserver一樣，這里就不詳細(xì)介紹了,我們直接使用
我們先看下源表

select  *  from TJSXM.water_quality

問(wèn)題1：把PJJG后的字段轉(zhuǎn)換為行

select 
*  from TJSXM.water_quality
unpivot(  POLLUTEVALUE for CODE_POLLUTE
 IN(JCXM_PH ,   JCXM_RYL,JCXM_GMSYZS ,JCXM_SHXYL ,JCXM_AD ,JCXM_SYL,JCXM_HFF,JCXM_GONG  ,JCXM_QIAN  ,JCXM_GE ,JCXM_YLZBMHXJ  ,JCXM_LUO,JCXM_FHW,JCXM_ZL ,JCXM_QHW,JCXM_LHW,JCXM_SHEN  ,JCXM_HXXYL ,
JCXM_TONG  ,JCXM_XIN,JCXM_XI )) 
----和SQLSERVER區(qū)別，這里沒(méi)有as

[轉(zhuǎn)換為行的**列值**在轉(zhuǎn)換后對(duì)應(yīng)的列名]對(duì)應(yīng)POLLUTEVALUE
[轉(zhuǎn)換為行的**列名**在轉(zhuǎn)換后對(duì)應(yīng)的列名]對(duì)應(yīng)CODE_POLLUTE
[轉(zhuǎn)換為行的列]對(duì)應(yīng)JCXM_PH , JCXM_RYL,JCXM_GMSYZS ,JCXM_SHXYL ,JCXM_AD ,JCXM_SYL,JCXM_HFF,JCXM_GONG  ,JCXM_QIAN  ,JCXM_GE ,JCXM_YLZBMHXJ  ,JCXM_LUO,JCXM_FHW,JCXM_ZL,JCXM_QHW,JCXM_LHW,JCXM_SHEN  ,JCXM_HXXYL ,JCXM_TONG  ,JCXM_XIN,JCXM_XI

我們看下結(jié)果

2、Oracle行轉(zhuǎn)列pivot

PIVOT函數(shù)的格式如下

PIVOT(<聚合函數(shù)>([聚合列值]) FOR [行轉(zhuǎn)列前的列名]
 IN('行轉(zhuǎn)列前的列名值1' [行轉(zhuǎn)列后的列名1],' 行轉(zhuǎn)列前的列名值2'  [行轉(zhuǎn)列后的列名2],'行轉(zhuǎn)列前的列名值3' [行轉(zhuǎn)列后的列名3],.......' 行轉(zhuǎn)列前的列名值n'  [行轉(zhuǎn)列后的列名N]))

#行轉(zhuǎn)列前的列名值2=
#select  distinct  行轉(zhuǎn)列前的列名 from 表名

問(wèn)題2：把POLLUTEVALUE污染物編碼字段以列的方式顯示

select *
from TJSXM.water_quality_temp 
PIVOT(max(CODE_POLLUTE) FOR POLLUTEVALUE IN(---- --#POLLUTEVALUE 即要轉(zhuǎn)成列的字段
'JCXM_PH'  JCXM_PH,------#max(CODE_POLLUTE) 此處必須為聚合函數(shù)
'JCXM_RYL'  JCXM_RYL,-----------#in () 對(duì)要轉(zhuǎn)成列的每一個(gè)值指定一個(gè)列名
'JCXM_GMSYZS'  JCXM_GMSYZS,
'JCXM_SHXYL'  JCXM_SHXYL,
'JCXM_AD'  JCXM_AD,
'JCXM_SYL'  JCXM_SYL,
'JCXM_HFF'  JCXM_HFF,
'JCXM_GONG'  JCXM_GONG,
'JCXM_QIAN'  JCXM_QIAN,
'JCXM_GE'  JCXM_GE,
'JCXM_YLZBMHXJ'  JCXM_YLZBMHXJ,
'JCXM_LUO'  JCXM_LUO,
'JCXM_FHW'  JCXM_FHW,
'JCXM_ZL'  JCXM_ZL,
'JCXM_QHW'  JCXM_QHW,
'JCXM_LHW'  JCXM_LHW,
'JCXM_SHEN'  JCXM_SHEN,
'JCXM_HXXYL'  JCXM_HXXYL,
'JCXM_TONG'  JCXM_TONG,
'JCXM_XIN'  JCXM_XIN,
'JCXM_XI'  JCXM_XI))

我們看下結(jié)果

image.png

三、DB2列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、DB2列轉(zhuǎn)行

先看下源表

select * from TJSXM.water_quality_temp

函數(shù)格式：
DB2列轉(zhuǎn)行不能使用函數(shù)unpivot，也沒(méi)找到相應(yīng)的函數(shù)格式，帶大家從案例里面體會(huì)

問(wèn)題1：把PJJG后的字段轉(zhuǎn)換為行

select 
a.NF,a.YF,a.HL,a.DMMC,a.DMLX ,a.PJJG,q.polutcode,q.polut_value
from TJSXM.water_quality a,
TABLE (VALUES
('Ph(pH無(wú)量綱）',a.JCXM_PH),
('溶解氧',a.JCXM_RYL),
('高錳酸鹽指數(shù)',a.JCXM_GMSYZS),
('生化需氧量（BOD5）',a.JCXM_SHXYL),
('氨氮',a.JCXM_AD),
('石油類(lèi)（石油醚萃?。?,a.JCXM_SYL),
('揮發(fā)酚',a.JCXM_HFF),
('汞',a.JCXM_GONG),
('鉛',a.JCXM_QIAN),
('鎘',a.JCXM_GE),
('陰離子表面活性劑',a.JCXM_YLZBMHXJ),
('鉻（六價(jià)）',a.JCXM_LUO),
('氟化物（以F-計(jì)）',a.JCXM_FHW),
('總磷（以P計(jì)）',a.JCXM_ZL),
('氰化物',a.JCXM_QHW),
('硫化物',a.JCXM_LHW),
('砷',a.JCXM_SHEN),
('化學(xué)需氧量（CODcr）',a.JCXM_HXXYL),
('銅',a.JCXM_TONG),
('鋅',a.JCXM_XIN),
('硒（四價(jià)）',a.JCXM_XI)
)
AS q(polutcode, polut_value)
#每個(gè)values中對(duì)應(yīng)列的數(shù)據(jù)類(lèi)型必須相同，值可以任意，如1，2，3，4都是整形

下面解釋一下執(zhí)行的過(guò)程：
核心是用table函數(shù)創(chuàng)建了一個(gè)表，這個(gè)表是用value實(shí)現(xiàn)的多行表，value實(shí)現(xiàn)虛表的例子：

所不同的是這里跟from子句中的一個(gè)表產(chǎn)生了關(guān)系，取出了表中的一列作為數(shù)據(jù)。參考文章：db2中的列轉(zhuǎn)行和行轉(zhuǎn)列

我們看下列轉(zhuǎn)行結(jié)果:

2、DB2行轉(zhuǎn)列

函數(shù)格式
很遺憾DB2行轉(zhuǎn)列不能使用unpivot函數(shù)，只能用max函數(shù)。我們一起寫(xiě)下吧

問(wèn)題2：把CODE_POLLUTE 污染物編碼字段以列的方式顯示

select
  NF,YF,HL,DMMC,PJJG,
 max(case when  CODE_POLLUTE = 'JCXM_PH'then POLLUTEVALUE end) JCXM_PHJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_RYL'then POLLUTEVALUE end) JCXM_RYLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_GMSYZS'then POLLUTEVALUE end) JCXM_GMSYZSJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_SHXYL'then POLLUTEVALUE end) JCXM_SHXYLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_AD'then POLLUTEVALUE end) JCXM_ADJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_SYL'then POLLUTEVALUE end) JCXM_SYLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_HFF'then POLLUTEVALUE end) JCXM_HFFJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_GONG'then POLLUTEVALUE end) JCXM_GONGJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_QIAN'then POLLUTEVALUE end) JCXM_QIANJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_GE'then POLLUTEVALUE end) JCXM_GEJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_YLZBMHXJ'then POLLUTEVALUE end) JCXM_YLZBMHXJJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_LUO'then POLLUTEVALUE end) JCXM_LUOJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_FHW'then POLLUTEVALUE end) JCXM_FHWJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_ZL'then POLLUTEVALUE end) JCXM_ZLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_QHW'then POLLUTEVALUE end) JCXM_QHWJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_LHW'then POLLUTEVALUE end) JCXM_LHWJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_SHEN'then POLLUTEVALUE end) JCXM_SHENJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_HXXYL'then POLLUTEVALUE end) JCXM_HXXYLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_TONG'then POLLUTEVALUE end) JCXM_TONGJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_XIN'then POLLUTEVALUE end) JCXM_XINJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_XI'then POLLUTEVALUE end) JCXM_XIJCXM_PH 
 from  water_quality_temp ----已存在數(shù)據(jù)庫(kù)中
 group by NF,YF,HL,DMMC,PJJG

我們看下運(yùn)行結(jié)果：

四、MYSQL列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、MYSQL列轉(zhuǎn)行

函數(shù)格式
MYSQL列轉(zhuǎn)行是不能使用unpivot函數(shù)的，我在網(wǎng)上百度了3個(gè)小時(shí)都沒(méi)找到。所以先用union all函數(shù)吧【內(nèi)心很崩潰】

問(wèn)題1：把PJJG后的字段轉(zhuǎn)換為一行

select NF,YF,HL,DMMC,PJJG, 'JCXM_PH' CODE_POLLUTE , JCXM_PH as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_PH' CODE_POLLUTE, JCXM_PH as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_RYL' CODE_POLLUTE, JCXM_RYL as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_GMSYZS' CODE_POLLUTE, JCXM_GMSYZS as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_SHXYL' CODE_POLLUTE, JCXM_SHXYL as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_AD' CODE_POLLUTE, JCXM_AD as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_SYL' CODE_POLLUTE, JCXM_SYL as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_HFF' CODE_POLLUTE, JCXM_HFF as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_GONG' CODE_POLLUTE, JCXM_GONG as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_QIAN' CODE_POLLUTE, JCXM_QIAN as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_GE' CODE_POLLUTE, JCXM_GE as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_YLZBMHXJ' CODE_POLLUTE, JCXM_YLZBMHXJ as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_LUO' CODE_POLLUTE, JCXM_LUO as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_FHW' CODE_POLLUTE, JCXM_FHW as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_ZL' CODE_POLLUTE, JCXM_ZL as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_QHW' CODE_POLLUTE, JCXM_QHW as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_LHW' CODE_POLLUTE, JCXM_LHW as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_SHEN' CODE_POLLUTE, JCXM_SHEN as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_HXXYL' CODE_POLLUTE, JCXM_HXXYL as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_TONG' CODE_POLLUTE, JCXM_TONG as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_XIN' CODE_POLLUTE, JCXM_XIN as POLLUTEVALUE from water_quality
union select NF,YF,HL,DMMC,PJJG, 'JCXM_XI' CODE_POLLUTE, JCXM_XI as POLLUTEVALUE from water_quality
order by NF,YF,HL,DMMC,PJJG

我們看下運(yùn)行結(jié)果

2、MYSQL行轉(zhuǎn)列

函數(shù)格式
很遺憾MYSQL行轉(zhuǎn)列不能使用unpivot函數(shù)，只能跟DB2數(shù)據(jù)庫(kù)一樣，只能用max函數(shù)。

問(wèn)題2：把CODE_POLLUTE 污染物編碼字段以列的方式顯示

select
  NF,YF,HL,DMMC,PJJG,
 max(case when  CODE_POLLUTE = 'JCXM_PH'then POLLUTEVALUE end) JCXM_PHJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_RYL'then POLLUTEVALUE end) JCXM_RYLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_GMSYZS'then POLLUTEVALUE end) JCXM_GMSYZSJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_SHXYL'then POLLUTEVALUE end) JCXM_SHXYLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_AD'then POLLUTEVALUE end) JCXM_ADJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_SYL'then POLLUTEVALUE end) JCXM_SYLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_HFF'then POLLUTEVALUE end) JCXM_HFFJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_GONG'then POLLUTEVALUE end) JCXM_GONGJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_QIAN'then POLLUTEVALUE end) JCXM_QIANJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_GE'then POLLUTEVALUE end) JCXM_GEJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_YLZBMHXJ'then POLLUTEVALUE end) JCXM_YLZBMHXJJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_LUO'then POLLUTEVALUE end) JCXM_LUOJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_FHW'then POLLUTEVALUE end) JCXM_FHWJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_ZL'then POLLUTEVALUE end) JCXM_ZLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_QHW'then POLLUTEVALUE end) JCXM_QHWJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_LHW'then POLLUTEVALUE end) JCXM_LHWJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_SHEN'then POLLUTEVALUE end) JCXM_SHENJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_HXXYL'then POLLUTEVALUE end) JCXM_HXXYLJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_TONG'then POLLUTEVALUE end) JCXM_TONGJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_XIN'then POLLUTEVALUE end) JCXM_XINJCXM_PH ,
max(case when  CODE_POLLUTE = 'JCXM_XI'then POLLUTEVALUE end) JCXM_XIJCXM_PH 
 from  water_quality_temp ----已存在數(shù)據(jù)庫(kù)中
 group by NF,YF,HL,DMMC,PJJG

我們看下運(yùn)行結(jié)果：

五、練習(xí)數(shù)據(jù)

使用時(shí)需要數(shù)據(jù)庫(kù)修改表名，和建表語(yǔ)句
建表語(yǔ)句

CREATE TABLE water_quality  ( 
    NF              varchar(20) NULL,
    YF              varchar(20) NULL,
    HL              varchar(50) NULL,
    DMMC            varchar(50) NULL,
    PJJG            varchar(50) NULL,
    JCXM_PH         varchar(50) NULL,
    JCXM_RYL        varchar(50) NULL,
    JCXM_GMSYZS     varchar(50) NULL,
    JCXM_SHXYL      varchar(50) NULL,
    JCXM_AD         varchar(50) NULL,
    JCXM_SYL        varchar(50) NULL,
    JCXM_HFF        varchar(50) NULL,
    JCXM_GONG       varchar(50) NULL,
    JCXM_QIAN       varchar(50) NULL,
    JCXM_GE         varchar(50) NULL,
    JCXM_YLZBMHXJ   varchar(50) NULL,
    JCXM_LUO        varchar(50) NULL,
    JCXM_FHW        varchar(50) NULL,
    JCXM_ZL         varchar(50) NULL,
    JCXM_QHW        varchar(50) NULL,
    JCXM_LHW        varchar(50) NULL,
    JCXM_SHEN       varchar(50) NULL,
    JCXM_HXXYL      varchar(50) NULL,
    JCXM_TONG       varchar(50) NULL,
    JCXM_XIN        varchar(50) NULL,
    JCXM_XI         varchar(50) NULL )

插入數(shù)據(jù)
數(shù)據(jù)量太大這里給大家提供網(wǎng)盤(pán)。
網(wǎng)盤(pán)鏈接：https://pan.baidu.com/s/1nTMvJopi9KJFnWOEEHXmQw
提取碼：msoh

六、感受

最大的感受就是MYSQL列轉(zhuǎn)行、行轉(zhuǎn)列太不友好了，都沒(méi)函數(shù)提供。

本來(lái)可以拆分成4篇文章的，合并在一個(gè)文章啦，以后有需要在拆分吧~~~
其實(shí)不同數(shù)據(jù)庫(kù)列轉(zhuǎn)行、行轉(zhuǎn)列區(qū)別挺明顯的，當(dāng)然羅如果不用函數(shù)，用方法就沒(méi)什么區(qū)別啦

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

sql中列轉(zhuǎn)行unpivot、行轉(zhuǎn)列pivot詳細(xì)講解||Mysql Oracle sqlserv DB2數(shù)據(jù)庫(kù)

sql中列轉(zhuǎn)行unpivot、行轉(zhuǎn)列pivot詳細(xì)講解||Mysql Oracle sqlserv DB2數(shù)據(jù)庫(kù)

一、sqlserver列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、列轉(zhuǎn)行unpivot

2、行轉(zhuǎn)列PIVOT

二、Oracle列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、Oracle列轉(zhuǎn)行unpivot

2、Oracle行轉(zhuǎn)列pivot

三、DB2列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、DB2列轉(zhuǎn)行

2、DB2行轉(zhuǎn)列

四、MYSQL列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、MYSQL列轉(zhuǎn)行

2、MYSQL行轉(zhuǎn)列

五、練習(xí)數(shù)據(jù)

六、感受

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

sql中列轉(zhuǎn)行unpivot、行轉(zhuǎn)列pivot詳細(xì)講解||Mysql Oracle sqlserv DB2數(shù)據(jù)庫(kù)

一、sqlserver列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、列轉(zhuǎn)行unpivot

2、行轉(zhuǎn)列PIVOT

二、Oracle列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、Oracle列轉(zhuǎn)行unpivot

2、Oracle行轉(zhuǎn)列pivot

三、DB2列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、DB2列轉(zhuǎn)行

2、DB2行轉(zhuǎn)列

四、MYSQL列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、MYSQL列轉(zhuǎn)行

2、MYSQL行轉(zhuǎn)列

五、練習(xí)數(shù)據(jù)

六、感受

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

sql中列轉(zhuǎn)行unpivot、行轉(zhuǎn)列pivot詳細(xì)講解||Mysql Oracle sqlserv DB2數(shù)據(jù)庫(kù)

一、sqlserver列轉(zhuǎn)行、行轉(zhuǎn)列詳解

2、行轉(zhuǎn)列PIVOT

二、Oracle列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、Oracle列轉(zhuǎn)行unpivot

三、DB2列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、DB2列轉(zhuǎn)行

2、DB2行轉(zhuǎn)列

四、MYSQL列轉(zhuǎn)行、行轉(zhuǎn)列詳解

1、MYSQL列轉(zhuǎn)行

五、練習(xí)數(shù)據(jù)

六、感受