第5章Excel数据分析之数据透视表遇见SQL

第5章 数据透视表遇见SQL

5-1如何在查询中使用SQL语句？

1、制作数据透视表时编写SQL语句

外部数据源制作动态数据透视表

找到文件数据

统计工资>=8000各部门有多少人

select * from [数据源$] where 基础工资>=8000

2、3d快捷键导入外部数据时SQL语句编写

先按Alt 松开，再按d松开，再按d松开，再按d松开

Alt+d+d+d

5-2SQL查询语句（数据透视表的辅助列）

1.如何编写SQL查询语句

alt+d+d+d导入外部数据源放在新工作表，成超级表格

编写SQL语句

获取全部字段

select * from [销售表$]

获取某个字段

select 日期,数量 from [销售表$]

2.如何添加新增列（辅助列）

新增金额

select  *,数量*单价 as 金额  from [销售表$]

操作步骤：

1）导入数据

2）数据透视表新增一列

5-3SQL常用运算符（案例：添加分析维度）

1. 算术运算符：+、-、*、/、\

   select *,单价+1 from  [销售表$]

2. 比较运算符：>、>=、<、<=、<>、=

   select *,数量>=400 from [销售表$]

3. 连接运算符：&

   select *,名称&"-"&单价 from [销售表$]

4. 逻辑运算符：and、or、not

   select *,-1 and -1 and -1 from [销售表$]

select *,-1 or 0 or -1 from [销售表$]

select *,not 0 from [销售表$]

判断金额>=2000

5-4SQL筛选语句（数据透视表数据源的过滤）

1.普通筛选

select * from [销售表$] where 数量>=200

2.与关系筛选

select * from [销售表$] where 数量>=200 and 数量<=300

两个条件同时满足

3.或关系筛选

select * from [销售表$] where 单价>=5 or 数量<=400

只要一个条件满足

4.日期数据筛选

日期书写使用 ##

select * from [销售表$] where (单价>=5 or 数量<=400) and 日期<=#2022-1-5#

制作数据透视表

5-5SQL语句（逻辑运算符升级版）

1.Between（可以看作and的简化）

 select * from [销售表$] where 数量>=100 and  数量<=200       
 select * from [销售表$] where 数量 between 100  and 200      
 select * from [销售表$] where 日期 between  #2022-1-5# and #2022-1-10# 

2.In（可以看作or的简化）

select * from [销售表$] where  名称="小米" or 名称="大豆" or 名称="红豆" 
 select * from [销售表$] where 名称 in  ("小米", "大豆" ,"红豆") 
 select * from [销售表$$]  where 名称 in (select  名称 from [条件表$]) 

5-6SQL的like运算符-1（字符串模糊筛选）

1.任意多个字符%

2.任意单个字符_

3.%与_的常见应用方法

like 是 等于
select * from [销售表$] where 名称 like “大%”	以"大"字开头的名称
select * from [销售表$] where 名称 like “%豆”	以"豆"字结尾的名称
select * from [销售表$] where 名称 like “%水%”	包含"水"字的名称
select * from [销售表$] where 名称 like “___”	三根下划线就是三个字符名称
select * from [销售表$] where 名称 like “_稻”	以“稻”结尾的两个字符的名称

5-7SQL的like运算符-2（字符组的应用）

字符组表示方法：[……]，表示字符长度为1。[!……]表示排除字符组中的字符。
汉字：[一-龥]
字母：[a-z]
数字：[0-9]

要求	语句
以字母开头的品名	select * from [订单表$] where 品名 like “[a-z]%”
以abcd开头的品名	select * from [订单表$] where 品名 like “[abcd]%”
以数字开头的品名	select * from [订单表$] where 品名 like “[0-9]%”
以13579开头的品名	select * from [订单表$] where 品名 like “[13579]%”
包含%的品名	select * from [订单表$] where 品名 like “%[%]%”
包含_的品名	select * from [订单表$] where 品名 like “%[_]%”
不以字母开头的品名，加!	select * from [订单表$] where 品名 like “[!a-z]%”
以汉字开头的品名	select * from [订单表$] where 品名 like “[一-龥]%”

5-8SQL语句之前几项与唯一值

1.TOP前几项

2.Distinct获取唯一值

前三项	select top 3 * from [销售表$]
去重	select distinct * from [销售表$]
对日期去重	select distinct 日期 from [销售表$]

3.实例：统计每天的采购品名数

select distinct 日期,名称 from [销售表$]

5-9SQL语句之排序（采购数量前100汇总）

1.排序（升序asc、降序desc）

以数量进行升序	select * from [销售表$] order by 数量 asc
以数量进行降序	select * from [销售表$] order by 数量 desc
以数量、单价进行降序	select * from [销售表$] order by 数量 asc,单价 asc
以日期进行排序	select * from [销售表$] order by 日期

2.实例：对采购数量前100汇总

select top 100 * from [销售表$] order by 数量 desc

5-10SQL语句之分组聚合-1（数据汇总分析）

1.group by分组基本写法

统计每一日的总金额

select 日期,sum(数量*单价) as 总金额 from [销售表$] group by 日期

2.实例1：统计出总金额大于等于10000的日期。
3.实例2：对采购金额用千为频段，统计记录数。

获取频段：

select  数量*单价\1000 as 频段 from [销售表$] group by 数量*单价\1000

统计记录数：

select  数量*单价\1000 as 频段,count(*) as 计数  from [销售表$] group by 数量*单价\1000

5-11SQL语句之分组聚合-2（条件分组）

1.多字段分组

select 日期,名称,count(*) as 计数 from [销售表$]  group by 日期,名称

2.分组前的条件语句

数量>=300，以名称进行分组计数	select 名称,count(*) as 计数 from [销售表$] where 数量>=300 group by 名称

3.分组后的条件语句（having）

More Actions数量>=300，以名称进行分组计数，筛选出计数>=5

select 名称,count(*) as 计数 from [销售表$]  where  数量>=300 group by 名称  having 计数 >=5    

执行出错 having 跟 group by同时执行的

正确：

select 名称,count(**) as 计数 from [销售表$]  where  数量>=300 group by 名称  having count(*)>=5

5-12SQL常用聚合函数

1. sum	select sum(数量) as 总数量 from [销售表$]
2. avg	select avg(数量) as 平均值 from [销售表$]
3. max	select max(数量) as 最大值 from [销售表$]
4. min	select min(数量) as 最小值 from [销售表$]
5. count	select count(*) as count计数 from [销售表$]
6. first	select first(日期) as 第一个日期 from [销售表$]
7. last	select last(日期) as 最后一个日期 from [销售表$]

select sum(数量) as 总数量,avg(数量) as 平均数量,max(数量) as 最大值, min(数量) as 最小值,count(*) as 计数,first(日期),last(日期) from [销售表$]

单价>=平均单价的数据

select * from  [销售表$] where 单价 >=(select  avg(单价) from [销售表])

5-13SQL逻辑分支函数-iif

1.iif函数的普通写法

iif(判断条件,正返回值,f负返回值)

select * ,iif(数量>=300,"优秀","普通") as 等级  from [销售表$]

2.iif函数的嵌套写法

数量>=400,优秀

数量>=200,良好

数量>=100,中等

数量>=0,较差

select * ,iif(数量>=400,"优秀",iif(数量>=200,"良好",iif(数量>=100,"中等","较差"))) as 等级  from [销售表$]

3.实例：根据等级做汇总（使用数据透视表）

select * ,iif(数量>=400,"优秀",iif(数量>=200,"良好",iif(数量>=100,"中等","较差"))) as 等级  from [销售表$]

5-14SQL结构比iif更简洁的switch

1.Switch基本语法

Switch(
条件1,值1,
条件2,值2,
条件3,值3,
……
)

例如：

select * ,switch(数量>=400,"优",数量>=300,"良",数量>=100,"中",数量>=0,"差") as 等级  from [销售表$]

select * ,switch(名称 like "%米","米类",名称 like "%豆","豆类",名称 like "%麦","麦类",-1,"其他") as 分类  from [销售表$]

5-15SQL的数字分支结构-choose

1.choose函数用法

0-59	差
60-79	中
80-89	良
90-100	优

select *,choose(分数\10+1,"差","差","差","差","差","差","中","中","良","优","优")  as 等级 from [成绩表$]

5-16SQL常用文本提取函数-1（实例：性别判断）

1.left\right\mid提取函数使用

left（左侧提取）	select *,left(姓名,1) as 左 from [信息表$]
right（右侧提取）	select *,right(姓名,1) as 右 from [信息表$]
mid（中间侧提取）	select *,mid(姓名,2,1) as 中 from [信息表$]

select *,left(姓名,1) as 左,right(姓名,3) as 右,mid(姓名,2,1) as 中  from [信息表$]

2.实例：性别判断

350781196403072089 身份证号 倒数第二个数据 奇偶数决定性别，奇数为男，偶数为女

先得到性别数字：

select *,mid(身份证,17,1) as 性别数字   from [信息表$]

再除以2得余数用mod：

select *,mid(身份证,17,1) as 性别数字,mid(身份证,17,1) mod 2 as 余数   from [信息表$]

(mid(身份证,17,1) mod 2) = 0 为女

mid(身份证,17,1) mod 2 =1 为男

select *,mid(身份证,17,1) as 性别数字,switch((mid(身份证,17,1) mod 2)=0,"女",-1,"男" )as 性别   from [信息表$]

5-17SQL常用文本查找函数-2（实例：文本拆分）

1.instr查找函数

select instr(“abcd”,“b”) ，意思是b在“ancd”第几个位置，得到的值返回2，在第二个位置

select instr(3,“abcdefb”,“b”)，从第三个位置开始查找 b

2.实例：文本拆分（根据拆分结果做数据汇总）

成绩
战承志-A组-85
疏绿凝-A组-120
绳依云-B组-99
京访烟-C组-100
赫长莹-A组-9
柏玉宇-D组-36
韩子悦-A组-86
胡东-A组-78
黎若骞-A组-69
冒阳阳-A组-140
伯念-D组-110
旷同光-A组-98
王依白-B组-114
错香芹-A组-83

1)提取姓名：

先知道"-"位置数，减1就是姓名最后一个字的位置数，在用left提取名称

select *,instr(成绩,"-")  as 位置,left(成绩, 位置-1) as 姓名 from [成绩表$]

2)提取组别

先知道"-"位置数，加1就是组别第一个位置数，组别长度都是2，在用mid提取组别

select *,instr(成绩,"-")  as 位置,left(成绩, 位置-1) as 姓名,mid(成绩,位置+1,2) as 组别 from [成绩表$]

3）提取分数

获取第二个-的位置：instr(位置+1,成绩,“-”)

获取分数:mid(成绩,instr(位置+1,成绩,“-”)+1,2)

获取的分数是文本，进行+0转换成数值 mid(成绩,instr(位置+1,成绩,“-”)+1,2)+0

select *,instr(成绩,"-")  as 位置,left(成绩, 位置-1) as 姓名,mid(成绩,位置+1,2) as 组别 ,mid(成绩,instr(位置+1,成绩,"-")+1,2)+0  as 分数 from [成绩表$]

数据表统计

5-18SQL常用文本替换函数-3（实例：部门拆分整理）

1.Replace函数使用方法

replace(替换数据，查找被替换的数据，替换成的值，起始位置，替换个数)

在"asbffj-njdn-vfg"查找出"-"替换成“|”	select replace(“asbffj-njdn-vfg”,“-”,“|”)

2.实例应用

先把横线全部替换成逗号，再把第一个逗号替换成（，最后再连接 ）

select *,replace(replace(名单,"-",","),",","（",1,1) &"）" as 处理后名单 from [信息表$]

5-19、5-20、5-21、5-22SQL常用文本格式化函数-4

1.Format函数使用方法

format(数据 ,“代码”) as 新增列名

2.代码详解

*代码*	*注释*
aaa	星期几（简写）
aaaa	星期几（标准）
d	提取天数
dd	提取天数，以两位数表示
ddd	星期几英文简写
dddd	星期几英文完全拼写
ddddd	提取日期部分（x/x/x）
dddddd	提取日期部分（x年x月x日）
w	一周的第几天，默认周日为第1天，如果周一为第1天则格式为：format(日期,“w”,2)
ww	返回一年中的第几周
m	提取月数
mm	提取月数，以两位数表示
mmm	英文月份（简写）
mmmm	英文月份（完整写法）
y	一年中的第几天
yy	年份中的后两位
yyyy	年份
q	返回季度
h	时
hh	时
n	分
nn	分
s	秒
ss	秒
ttttt	返回完整的时间
am/pm	判断上午还是下午,a/m ,AM/PM同理
0	数字占位符
#	数字占位符
@	文字占位符
!	强制显示
\	强制显示
>	转为大写
<	转为小写

select * ,format(mid(身份证,7,8),"0000-00-00") as 出生年月日,format(出生年月日 ,"aaa") as 周几,format(出生年月日 ,"aaaa") as 星期几,format(出生年月日 ,"d") as 天, format(出生年月日 ,"dd") as 天2,format(出生年月日 ,"ddd") as 周几e,format(出生年月日 ,"dddd") as 周几e2,now from [员工信息$]

select *,cdate(format(mid(身份证,7,8),"0000-00-00")) as 日期,format(日期,"w",2) as 周w,cint(format(日期,"ww",2)) as 年周ww,format(日期,"m") as 月m,format(日期,"mm") as 月mm,format(日期,"mmm") as 月mmm,format(日期,"mmmm") as 月mmmm,format(日期,"y") as 天y,format(日期,"yy") as 年yy,format(日期,"yyyy") as 年yyyy,format(日期,"q") as 季q from [员工信息$]

select *,接单日期 as 日期,format(日期,"h") as 小时h,format(日期,"hh") as 小时hh ,format(日期,"n") as 分钟n,format(日期,"nn") as 分钟nn,format(日期,"s") as 秒s,format(日期,"ss") as 秒ss,format(日期,"hh:nn:ss") as 时间hns,format(日期,"ttttt") as 时间ttttt,format(日期,"AM/PM") as 上下午   from [订单表$]

select *,format(盈亏,"盈;亏;平") as 状态 from [基金盈亏表$]

5-23SQL常用日期函数-1（实例：根据身份证做年龄段统计）

1.date()、time()、now()

select date() as 当前日期,time() as 当前时间,now()  as 当前日期以及时间

2.实例：根据身份证做年龄段统计

select *,mid(身份证,7,4) as 出生年份,format(date(),"yyyy") as 现在年份,现在年份-出生年份 as 年龄 from [信息表$]

5-24SQL常用日期函数-2（实例：分析结算日期）

1.Datepart提取日期指定单位的数据

Datepart(单位，时间)

select now() as 当前日期,datepart("yyyy",now()) as 当前年份

2.Dateadd对日期指定单位数据的增减

Dateadd(单位，值，时间或日期)

代码	注释
yyyy	年
q	季度
m	月
y	某年的第几天
d	某月的第几天
w	某周的第几天
ww	周
h	时
n	分
s	秒

select now() as 当前日期,dateadd("yyyy",1,now()) as 下一年日期

实例：

公司	结算日期	付款方式
A公司	2022/1/26	微信
B公司	2022/1/22	支付宝
C公司	2022/1/17	现金
D公司	2022/1/25	转账
E公司	2022/1/4	支付宝
F公司	2022/1/29	支付宝
G公司	2022/1/23	微信
H公司	2022/1/6	现金

付款方式	天数
微信	5
支付宝	7
现金	3
转账	6

算出付款日期

算出付款天数：,switch(付款方式=“微信”,5,付款方式=“支付宝”,7,付款方式=“现金”,3,付款方式=“转账”,5) as 付款天数

算出付款日期：dateadd(“d”, 付款天数,结算日期) as 付款日期

select * ,switch(付款方式="微信",5,付款方式="支付宝",7,付款方式="现金",3,付款方式="转账",5) as 付款天数,dateadd("d", 付款天数,结算日期) as 付款日期 from [结算表$]

5-25SQL常用日期函数-3（实例：工龄计算）

1.datediff计算两个日期指定单位的差值。

datediff(单位，起始日期，终止日期)

代码	注释
yyyy	年数
q	季度数
m	月数
y	天数
d	天数
w	周数
ww	周数（不包括date1的周日）
h	小时数
n	分钟数
s	秒数

些时间要用 “#”

select datediff("yyyy",#1999-03-03 9:15:00#,#2024-03-16 14:00:00#) as  年差,datediff("m",#1999-03-03 9:15:00#,#2024-03-16 14:00:00#) as  月差,datediff("h",#1999-03-03 9:15:00#,#2024-03-16 14:00:00#) as  时差

（实例：工龄计算）

select *,datediff("yyyy",入职日期,date()) as 工龄数 from [data$]

5-26SQL的合并语句（实例：多表合并统计）

1.union all语句的使用方法

select * from [表1$] union all select *   from [表2$] union all select *  from [表3$]

2.实例：多表合并统计

select *,"喜剧片" as 类型 from [喜剧片$] union all select * ,"恐怖片" as 类型  from [恐怖片$] union all select * ,"爱情片" as 类型 from [爱情片$]

5-27SQL的合并语句（实例：多表合并统计进阶）

1.union去重合并

两个表出现重复的去重保留一个

select * from [表1] union  select * from [表2]

2.实例（多表合并统计进阶）

华林商城

月份	电脑	手机	家电	合计
1月	127	103	77	307
2月	89	91	54	234
3月	73	81	60	214
4月	92	96	60	248
5月	149	72	135	356
6月	103	102	61	266
7月	137	75	83	295
8月	115	137	97	349
9月	53	88	142	283
10月	77	150	149	376
11月	150	97	71	318
12月	115	133	119	367

好又美

月份	电脑	家电
1月	142	87
2月	70	66
3月	127	81
4月	115	132
5月	57	125
6月	130	107
7月	134	108
8月	105	143
9月	110	144
10月	103	121
11月	139	74
12月	89	126

绿联商城

月份	手机	电脑
1月	112	63
2月	71	82
3月	136	90
4月	146	148
5月	70	73
6月	80	141
7月	107	144
8月	130	58
9月	61	76
10月	142	53
11月	60	139
12月	78	142

三表合一

select  月份,电脑,手机,家电,"华林商城" as 商城 from [华林商城$] union all select 月份,电脑,0,家电,"好又美" from [好又美$] union all select 月份,电脑,手机,0,"绿联商城" from [绿联商城$]

5-28SQL的笛卡尔运算（实例：跨表查询数据）

1.演示笛卡尔运算原理

select * from [表1$],[表2$]

2.实例：跨表查询数据

销售表

日期	产品	重量
2022/8/1	白菜	10
2022/8/2	茄子	20
2022/8/3	冬瓜	5
2022/8/4	四季豆	100
2022/8/5	四季豆	101
2022/8/5	茄子	200

价格表

品名	单价
白菜	2.5
莴笋	5.5
茄子	1.9
洋葱	4.6
冬瓜	0.5
四季豆	6

select 产品,sum(重量*单价) as 金额 from [销售表$],[价格表$] where 产品=品名 group by 产品

5-29SQL的笛卡尔运算进阶（实例：多表数据横向合并）

2020年

姓名	部门	分数
张三	销售部	70
李四	财务部	79
张三	开发部	60
王五	IT部	94
朱能	销售部	56
王炸	销售部	83

2021年

姓名	部门	分数
张三	销售部	80
李四	财务部	71
张三	开发部	69
王五	IT部	78
朱能	销售部	89
王炸	销售部	74

2022年

姓名	部门	分数
张三	销售部	82
李四	财务部	55
张三	开发部	57
王五	IT部	100
朱能	销售部	57
王炸	销售部	74

select * from ['2020年$'],['2021年$'],['2022年$']

select t1.姓名,t1.部门,t1.分数 as 2020年,t2.分数 as 2021年,t3.分数 as 2022年 from ['2020年$'] t1,['2021年$'] t2,['2022年$'] t3 where t1.姓名=t2.姓名 and t2.姓名=t3.姓名 and t1.部门=t2.部门 and t2.部门=t3.部门

5-30SQL内连接（案例：查询并做统计汇总）

1.inner join内连接用法

select * from [表1$]  inner join [表2$] on [表1$]. 字段 =[表2$]. 字段

select * from [销售表$] inner join [价格表$] on [销售表$].产品 =  [价格表$].品名

2.案例：查询并做统计汇总

select 产品,sum(重量*单价) as 金额 from [销售表$] t1 inner join [价格表$] t2 on t1.产品=t2.品名 group by 产品

5-31SQL左右连接（案例：查询两表所有人业绩）

1.left join与right join用法

select * from [表1$]  left join [表2$] on [表1$]. 字段 =[表2$]. 字段

select * from [表1$]  right join [表2$] on [表1$]. 字段 =[表2$]. 字段

2.案例：查询两表所有人业绩

2020年

姓名	业绩
张三	580000
李四	660000
王麻子	470000

2021年

姓名	业绩
张三	770000
陈宫	368000
王麻子	570000
黄杰	690000

左连接

select t1.姓名,t1.业绩,t2.业绩 from ['2020年$'] t1 left join  ['2021年$'] t2 on t1.姓名 = t2.姓名

右连接

select t2.姓名,t1.业绩,t2.业绩 from ['2020年$'] t1 right join  ['2021年$'] t2 on t1.姓名 = t2.姓名

左右连接

select t1.姓名,t1.业绩 as 2020年,t2.业绩 as 2021年 from ['2020年$'] t1  left join  ['2021年$'] t2 on t1.姓名=t2.姓名  union   select t2.姓名,t1.业绩,t2.业绩 from ['2020年$'] t1  right join  ['2021年$'] t2 on t1.姓名=t2.姓名

5-32SQL左右连接进阶（案例：三表关联查询汇总）

实例：统计各部门总人数和奖金

三表合一

select * from ([成绩表$] t1 left join [名单表$] t2 on t1.姓名 = t2.姓名) left join [部门表$] t3 on t2.部门ID = t3.序号

统计各部门总人数和奖金

select 部门,count(*) as 记录条数,sum(奖金) as 总奖金 from ([成绩表$] t1 left join [名单表$] t2 on t1.姓名 = t2.姓名) left join [部门表$] t3 on t2.部门ID = t3.序号 group by 部门

5-33SQL嵌套子查询应用-1

1.实例1：筛选高于平均单价的记录。

select *  from [销售表$] where 单价>=(select avg(单价)  from [销售表$])

2.实例2：将筛选条件放在单元格。

select *  from [销售表$] where 名称 in (select 名称 from [Sheet1$n:n])

3.实例3：将多表合并结果做分组汇总。

三表合一：

select *,"喜剧片" as 类型 from [喜剧片$] union all select * ,"恐怖片" as 类型  from [恐怖片$] union all select * ,"爱情片" as 类型 from [爱情片$]

分组汇总：

select 类型,count(*) as 片,sum(数量) as 总数量  from (select *,"喜剧片" as 类型 from [喜剧片$] union all select * ,"恐怖片" as 类型  from [恐怖片$] union all select * ,"爱情片" as 类型 from [爱情片$]) group by 类型

5-34SQL嵌套子查询应用-2

1.实例：统计各类图书的种类数

去重：

select distinct 类别,书名 from [销售表$]

select 类别,count(*) as 总类数 from (select distinct 类别,书名 from [销售表$]) group by 类别

2.实例：筛选大于等于各类图书均价的书籍。

select a.* from [销售表$] a inner join (select 类别,avg(单价) as 均价 from [销售表$] group by 类别) b on a.类别=b.类别 and a.单价>=b.均价

5-35SQL相关子查询应用-1

1.相关子查询工作原理

2.实例：筛选出大于等于所在班平均分的记录。

select * from [分数表$] a where 分数>=(select avg(分数) from [分数表$] b where a.班别=b.班别)

5-36SQL相关子查询应用-2（美式排名）

1.美式排名

select *,(select count(*) from [成绩表$]  b where a.分数 < b.分数)+1 as 名次  from [成绩表$] a

2.美式分组排名

班别里面排名

select *,(select count(*) from [成绩表$]  b where a.班别=b.班别 and a.分数 < b.分数)+1 as 名次  from [成绩表$] a

5-37SQL相关子查询应用-3（中式排名）

3.中式排名

select *, 1+(select count(*) from (select distinct 分数 from [成绩表$])b where a.分数<b.分数) as 名次 from [成绩表$] a

4.中式分组排名

select *, 1+(select count(*) from (select distinct 班别,分数 from [成绩表$]) b where a.班别=b.班别 and a.分数<b.分数) as 名次 from [成绩表$] a