? ? ? ? 這是最近項目關(guān)于SQL語句的，本文簡單記錄并總結(jié)以下幾個知識點：
? ? ? ? 1.如何統(tǒng)計一張表中某個字段的總數(shù)，如不同"專業(yè)"的學(xué)生數(shù)及所占百分比；
? ? ? ? 2.如何聯(lián)系另一張表進行查詢某個字段的總數(shù)及百分比；
? ? ? ? 3.簡單介紹decode防止分母為0和trunc保留小數(shù)位數(shù)等函數(shù)；
? ? ? ? 4.通常復(fù)雜的SQL語句會涉及到查詢結(jié)果橫向連接和縱向連接，這里進行介紹。
? ? ? ? 最近買了本《Oracle查詢優(yōu)化改寫技巧與案例·有教無類 落落》，推薦大家也閱讀下。后面我也會補充一些相關(guān)數(shù)據(jù)的知識，希望對大家有所幫助吧！文章還是以基礎(chǔ)知識為主，同時主要是解決實際的問題。同時我采用自問自答的新敘述方法進行介紹~

0. 前言

? ? ? ? 假如現(xiàn)在存在如下圖所示的一張表，這種表在數(shù)據(jù)庫中是很常見，主要包括字段（序號，教師姓名，單位名稱，性別，學(xué)歷）。該表的信息為：TEST_TEACHER（id，name，dw_name，sex，degree）。

1.問題一： 如何在單表中統(tǒng)計總數(shù)及比例

? ? ? ? 現(xiàn)在需要統(tǒng)計各個單位的教師人數(shù)，及該單位的不同學(xué)歷的、性別的人數(shù)。這是非常常見的問題，當時項目中統(tǒng)計的內(nèi)容很多，包括：年齡、學(xué)緣、職稱、專業(yè)等等。
? ? ? ? 這很簡單并且方法很多，常見的主要使用子查詢或group by分組。
? ? ? ? group by：
select DW_NAME, COUNT(DW_NAME) as 人數(shù) from TEST_TEACHER group by DW_NAME order by DW_NAME; ? ? ? ? 輸出結(jié)果如下圖所示：其中order by是按漢語拼音排序輸出。

? ? ? ? 如果現(xiàn)在需要統(tǒng)計各個學(xué)院的男性、女性教師人數(shù)，博士、碩士、學(xué)士的教師人數(shù)，需要怎么辦呢？因為它都是同一張表的信息，一種方法是使用子查詢，這里介紹另外一種方法，通過CASE WHEN THEN實現(xiàn)。
? ? ? ??CASE WHEN THEN：
select DW_NAME, COUNT(DW_NAME) as SUM, COUNT(CASE WHEN SEX='男' THEN 1 END) as Man, COUNT(CASE WHEN SEX='女' THEN 1 END) as Women,COUNT(CASE WHEN DEGREE='博士' THEN 1 END) as BS, COUNT(CASE WHEN DEGREE='碩士' THEN 1 END) as SS from TEST_TEACHER group by DW_NAME order by DW_NAME; ? ? ? ? 輸出結(jié)果如下圖所示：表示如何SEX為"男"，統(tǒng)計加1。

?
? ? ? ? 現(xiàn)在如果需要統(tǒng)計每個學(xué)院所占所有人數(shù)的比例，怎么計算呢？
? ? ? ? 傳統(tǒng)方法該步驟是分別統(tǒng)計兩個數(shù)，再通過后臺Java或C++進行處理的，或者是使用除法: 學(xué)院總數(shù)/老師表總數(shù)，但是已經(jīng)使用了分組group by，如果再使用一遍統(tǒng)計TEST_TEACHER總數(shù)就會報錯。
? ? ? ? 這里使用的是Oracle的函數(shù)ratio_to_report() over()實現(xiàn)。
? ? ? ??ratio_to_report：
select DW_NAME, COUNT(DW_NAME) as SUM, ratio_to_report(COUNT(1)) OVER(),COUNT(CASE WHEN SEX='男' THEN 1 END) as Man, COUNT(CASE WHEN SEX='女' THEN 1 END) as Women from TEST_TEACHER group by DW_NAME order by DW_NAME; ? ? ? ? 輸出結(jié)果如下圖所示：其中計算機3個老師，總數(shù)10個老師，比例占0.3。

? ? ?

2.問題二： 聯(lián)系另一張表統(tǒng)計總數(shù)及比例

? ? ? ?在設(shè)計數(shù)據(jù)庫過程中，通常會將教師的信息設(shè)置為一張表，學(xué)院信息會存在另一張表中，同理授課信息、發(fā)表論文信息都會存在單獨的表，再通過教師姓名（或教師編號）連接查詢。
? ? ? ?所以這里講述第二種統(tǒng)計總數(shù)及比例的方法，假設(shè)還存在一張學(xué)院表，如下圖所示：該表TEST_DEP結(jié)構(gòu)為（DW_NAME，DW_CODE，YEAR，ADDR）。

? ? ? ?通常是遍歷該單位表，然后子查詢連接教師表，統(tǒng)計不同單位的人數(shù)信息。同樣統(tǒng)計專業(yè)教師信息、教師發(fā)表論文、學(xué)院發(fā)表論文都是這種方法。


? ? ? ? 兩表連接子查詢 ：統(tǒng)計總數(shù)
select t2.DW_NAME, (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) as 總數(shù),(select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男') as 男,(select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='女') as 女 from TEST_DEP t2; ? ? ? ? 輸出結(jié)果如下圖所示：這種方法是非常常見的一種統(tǒng)計方法，而不是僅僅通過單表，因為數(shù)據(jù)庫往往都會通過外鍵聯(lián)系其他表。

? ? ? ? 這種兩表聯(lián)系的另一個優(yōu)勢是更方便計算百分比、比例等，因為同一張表使用兩次時很麻煩的，如 "select (select * from t1) from t1;"這里使用子查詢計算比例代碼如下。
? ? ? ? 兩表連接子查詢：統(tǒng)計比例
select t2.DW_NAME, (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) as 總數(shù),((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) / (select COUNT(*) from TEST_TEACHER)) as 總數(shù)比例,(select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男') as 男,(select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='女') as 女,((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男')/ (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME)) as 男教師比例 from TEST_DEP t2; ? ? ? ? 輸出結(jié)果如下圖所示：

? ? ? ? 其中總共10個教師，計算機學(xué)院3人，所占比例=3/10=0.4，同時男教師比例為計算機學(xué)院男教師2人，女教師1人，故比例=2/3=0.666，這里使用的方法是子查詢的除法。其實更常見的方法是獲取總數(shù)，然后后臺業(yè)務(wù)邏輯進行計算。

?
? ? ? ??

3.問題三： 除法防止分母為0及保留有效位數(shù)

? ? ? ? 如上圖輸出為0.6666667，此時需要保留有效位數(shù)，這里的方法可以是使用trunc()函數(shù)，整數(shù)可用floor()函數(shù)。
? ? ? ??TRUNC(x[,y])功能: 計算截尾到y(tǒng)位小數(shù)的x值，y缺省為0，結(jié)果變?yōu)橐粋€整數(shù)值。
? ? ? ??trunc()是截斷操作，floor(x)是小于或等于x的最大整數(shù)。
? ? ? ? TRUNC() FLOOR()：
select t2.DW_NAME, (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) as 總數(shù),(select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男') as 男,(select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='女') as 女,trunc(((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男')/ (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME))*100,2) as 百分比 from TEST_DEP t2; ? ? ? ? 輸出結(jié)果如下圖所示：

? ? ? ? 其中輸出為66.66，表示計算機學(xué)院男教師所占百分比，其中保留2位有效數(shù)字的方法為：trunc( 統(tǒng)計男教師子查詢 / 統(tǒng)計教師子查詢*100，2)。

? ? ? ? Oracle中通常需要統(tǒng)計如男生占全班總?cè)藬?shù)比例等用法，此時如果分母為0，它會報錯"[Err] ORA-01476: divisor is equal to zero"。那怎么辦呢？
? ? ? ? 解決方法：使用函數(shù)decode，當分母為0時直接返回0，否則進行除法運算。
? ? ? ?select a/b from c; 修改成如下即可：select decode(b, 0, 0, a/b) from c;
? ? ? ? 例如：decode(XF_ALL, 0, 0,trunc(XF_MATH / XF_ALL * 100, 1)) as BL
? ? ? ? 上面子查詢除法使用decode的并結(jié)合trunc保留2位有效數(shù)字的SQL如下。
? ? ? ? decode()：
select t2.DW_NAME, (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) as 總數(shù),(select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男') as 男,(select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='女') as 女,trunc(decode((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME),0, 0, ((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男')/ (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME)))*100,2) as 百分比 from TEST_DEP t2; ? ? ? ? 輸出結(jié)果如下圖所示，核心方法：trunc(decode(b,0,0,a/b)*100, 2)。

4.問題四： 查詢橫向連接和縱向連接

? ? ? ? 復(fù)雜的SQL語句通常包括橫向連接和縱向連接。
? ? ? ? 橫向連接：使用自定義子查詢
? ? ? ? 這里使用的方法是子查詢自定義命名的方法，該方法只返回一行數(shù)據(jù)，通常通過Json格式給后臺，后臺只需要顯示即可。它的優(yōu)勢是不論表多復(fù)雜或表之間沒有關(guān)系，而是只需要把值統(tǒng)計在一行，都能聯(lián)系返回結(jié)果。
? ? ? ? 如下圖所示，這就是橫向連接返回的結(jié)果，但是需要知道具體的學(xué)院名稱。

? ? ? ? 在統(tǒng)計不同表的總數(shù)信息時，使用該方法比較好。
select t1.DW_NAME, t2.ZS, t3.BS, t4.DW_NAME, t5.ZS, t6.BS from (select DW_NAME as DW_NAME from TEST_TEACHER where DW_NAME='軟件學(xué)院' group by DW_NAME) t1, (select COUNT(*) as ZS from TEST_TEACHER where DW_NAME='軟件學(xué)院') t2, (select COUNT(*) as BS from TEST_TEACHER where DW_NAME='軟件學(xué)院' and DEGREE='博士') t3, (select DW_NAME as DW_NAME from TEST_TEACHER where DW_NAME='計算機學(xué)院' group by DW_NAME) t4, (select COUNT(*) as ZS from TEST_TEACHER where DW_NAME='計算機學(xué)院') t5, (select COUNT(*) as BS from TEST_TEACHER where DW_NAME='計算機學(xué)院' and DEGREE='博士') t6; ? ? ? ?縱向連接：使用UNION ALL連接
? ? ? ? 假設(shè)現(xiàn)在只需要統(tǒng)計"軟件學(xué)院"、"計算機學(xué)院"的信息，這里需要縱向連接，則使用UNION ALL自然連接。
? ? ? ??Union：對兩個結(jié)果集進行并集操作，不包括重復(fù)行，同時進行默認規(guī)則的排序；
? ? ? ? Union All：對兩個結(jié)果集進行并集操作，包括重復(fù)行，不進行排序。
? ? ? ? 注意：COUNT需要使用GROUP BY，這種方法的優(yōu)勢是如果表中存在很多NULL的單位信息或只需要統(tǒng)計幾個固定的學(xué)院信息，此時使用該方法就比較適合。
? ? ? ? 總之，特定的場合需要特定的處理方法。
select DW_NAME, COUNT(DW_NAME) as SUM, ratio_to_report(COUNT(1)) OVER() as BL,COUNT(CASE WHEN SEX='男' THEN 1 END) as Man, COUNT(CASE WHEN SEX='女' THEN 1 END) as Women from TEST_TEACHER WHERE DW_NAME='軟件學(xué)院' GROUP BY DW_NAMEUNION ALL select DW_NAME, COUNT(DW_NAME) as SUM, ratio_to_report(COUNT(1)) OVER() as BL,COUNT(CASE WHEN SEX='男' THEN 1 END) as Man, COUNT(CASE WHEN SEX='女' THEN 1 END) as Women from TEST_TEACHER WHERE DW_NAME='計算機學(xué)院' GROUP BY DW_NAME; ? ? ? ? 輸出結(jié)果如下圖所示：

? ? ? ? 相關(guān)資料：
? ? ? ? [數(shù)據(jù)庫] SQL語句select簡單記錄總結(jié)
? ? ? ??[數(shù)據(jù)庫] SQL查詢語句表行列轉(zhuǎn)換及一行數(shù)據(jù)轉(zhuǎn)換成兩列
? ? ? ??[數(shù)據(jù)庫] Navicat for Oracle基本用法圖文介紹
? ? ? ??[數(shù)據(jù)庫] Navicat for Oracle設(shè)置唯一性和遞增序列實驗

? ? ? ? 最后希望文章對你有所幫助，這是一篇我的在線筆記，同時后面結(jié)合自己實際項目和SQL性能優(yōu)化，將分享一些更為專業(yè)的文章~還有20多天就要畢業(yè)當老師了，哈哈！
? ? ? ?(By:Eastmount 2016-06-14 深夜3點 ??http://blog.csdn.net//eastmount/?)

總結(jié)

以上是生活随笔為你收集整理的[数据库] Oracle单表查询总数及百分比和数据横向纵向连接的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。