sqlserver leftjoin出現重複資料_審計百萬行資料指南（七）：完結篇

本文主要梳理下，審計過程中運用到的分析性程式所需要的sql語句或技巧。若有錯誤或疏忽請海涵。

一、删除重複資料

我這裡有一張表"sap表頭”，他是從一個電商平台抓取的資料，如果多次抓取，就會有重複的記錄。那麼我們就需要把重複的去掉。

這裡我們用group語句就能解決問題。

create TABLE sap表頭去重
SELECT *
from sap表頭
group by  原始訂單
           

（注：SQL大小寫不敏感，是以大寫、小寫都一樣）

group by 意思是分組，比如這裡就是按照“原始訂單”号來分組，相同的原始訂單号就歸集在一起，是以僅剩下了一個。這樣就把重複的給去除了。去除後的結果放在建立的表“sap表頭去重”那裡。

二、資料透視

1.group by

對于簡單的一個字段的透視，我們可以直接用group by語句完成。上面講了group by的意思就是分組，對于每一組我們有sum,avg,count等聚合函數對其求和、平均、計數等。

比如上面的“sap表頭去重”表裡有收貨人地區這個字段，我想統計出每個省的訂單銷售金額出來。

substring_index(收貨人地區，'-',1) as 地區 ，這句話相當于是分列，把取到的省份 命名成“地區”。group by就是按照地區分組。

查詢出來的結果如下：

2.case when

我們可以用case when 條件 then 值1 else 值2 end ，來完成我們所想要的透視。

比如我想統計銷售訂單金額落在一下圖示區間的次數：

那麼我可以寫語句：

select語句後面跟着的都是你需要查詢的列，比如我們拿第三行代碼舉例，這個相當于第二列的資料，我給他列名命名為'20-40'，那麼sql在讀取“sap表頭去重”的每一行資料的時候，這一列都會判斷訂單銷售金額是不是在20-40之間，如果是就計數1，如果不是就計數0，當資料讀取完後，外面的sum函數會把所有的計數求和，這樣就查詢出了'20-40'訂單金額次數。

如果我不是要計算次數，而是要計算分布的訂單金額。其實我就把“1”變成“訂單銷售金額”就行了。

執行結果如下：

當然，前面我們統計分地區訂單銷售金額也可以用這個語句來寫。僅僅是把when 後面的條件變成 “地區=北京”這樣就行，不過可能要寫34個，有點麻煩。

三、vlookup及完整性核對

vlookup就是從另一張表裡找數，兩張表要有一個字段相同才行。

在mysql中就是需要用到join語句。

• join**

  假如，我們用銷售單号來比對。那麼用“join"的結果隻會顯示，兩張表都有單号的資料，要是其中有一張表沒有，他就不會顯示。

也就是說join連接配接兩張表的結果隻是他們的交集。

• left join,right join

如果是 A left join B 那麼查詢結果就是左邊整個圓，也就是說隻要A有的銷售單号都會顯示出來。

如果A right join B 那麼查詢結果就是右邊整個圓，也就是說隻要B有的銷售單号都會顯示出來。

我們看看實際怎麼用的。

比如我有"商派資料"表和"sap表頭去重"兩張表。商派資料是我直接從第三方電商ERP系統導出來的資料，sap表 頭去重是我sap系統自動抓取的資料，那麼我們想要看sap究竟有沒有把資料抓全或者抓多了。

我就通過唯一的銷售号作為兩張表的連結：

整個語句其實是分兩部分，上面是left join ，下面是right join,用一個union把兩個語句查詢結果合并在一起。

上面部分找的是A有但是B沒有的單号，下面找的是B有A沒有的單号。用union合在一起就相當于找出所有兩張表不一緻的資料。

執行後，就找出來一些不一緻的單号。我去查這些單号，其實是我導入資料的時候有部分導錯了，尴尬。

上面是完整性檢查，如果是單純vlookup就用一個left join 兩張 表連結，把你需要的字段寫在select後面就可以了。

四、分組前N名

如果我們要求前5大客戶，這個很好實作：

按照訂單銷售金額降序排列取前5條記錄就能實作。

要是我們想篩選出每個省前5名呢？這種求分組前N名在Mysql稍微麻煩點。

我們看一個例子：

比如我們這裡有一個fruits表,我想求出每種水果最便宜的前兩個。

代碼如下：

這種例子在網上很容易搜到，但是了解卻很難了解，在網上搜半天都沒有找到詳細解釋的。

這裡我們看看怎麼去了解：

首先，這裡有兩個對fruits的查詢語句，外面的表把fruits命名為a,裡面的命名為b。a,b實際上都是fruits表。

我們知道select最先運作的是where裡面的子查詢。

因為兩個表有type相同的條件，為便于了解，我們拿apple一種來舉例：

• 1、當a表取第一行時，b表周遊所有行：

  2.79<=2.79 √

  0.24<=2.79 √

  0.30<=2.79 √

  count(*)=3 不滿足<=2 是以a表第一行放棄

• 2、當a表取第二行時，b表周遊所有行：

  2.79<=0.24 ×

  0.24<=0.24 ×

  0.30<=0.24 √

  count(*)=1 滿足<=2 是以a表第二行選中。

• 3、當a表取第三行時，b表周遊所有行：

  2.79<=0.30 ×

  0.24<=0.30 √

  0.30<=0.30 √

  count(*)=2 滿足<=2 是以a表第三行選中。

  就是通過這樣的方式選出了第二、三行兩個最便宜的蘋果。

  相信通過上面的周遊過程，能夠很容易了解了。

結語

關于利用Mysql處理資料的分享暫時告一段落。對于有這方面需求的人也許會有幫助。