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处理数据的分享暂时告一段落。对于有这方面需求的人也许会有帮助。