sql server 求百分比_数据分析-SQL第四节复杂查询

一、 视图

1． 什么是视图（存放SQL语句）

2． 如何创建视图（create view视图名称（<视图列名1>,<视图列名2>,…）as <select查询语句>--------视图内列名需与select查询列名内容一一对应，名称允许不同）

创建视图

查看视图

查询视图

3． 视图作用（如某些查询语句需经常使用，可保存在视图中，方便后续调用）

4． 注意事项（避免在视图基础上再创建视图—多层视图降低效率；视图中不能插入数据，否则报错）

二、 子查询

1． 什么是子查询（一次性的视图，直接在sql查询语句中嵌入写，即select语句中再嵌套select）

2．如何使用子查询

找出每个课程里成绩最低的学号

按照上节课方法查找，发现无法识别学号字段

使用嵌套查询

第一步：查找成绩最低的课程号及分数

第二步：查找最低课程号及分数对应的学号

我们发现上面的答案是错误的，其实这是一个分组取最小值对应的行问题

使用关联子查询（关联子查询可省略group by）

Select 学号,课程号,成绩 from score as s1 where 成绩 = (select min(成绩) from score as s2 where s1.课程号=s2.课程号)

逻辑运算符含义：

Not 否定某一条件

And 并且

Between 范围查找

Or 或者

In 等同于or

Any 任何=some

All 所有

练习：哪些学生的成绩比课程0002大的全部成绩里的任意一个高呢？

第一步：课程0002的所有成绩

第二步：查询学生成绩条件大于0002的任意一个

练习：哪些学生的成绩比课程0002大的全部成绩里都高呢？

第一步：课程0002的所有成绩

第二步：查询学生成绩条件大于0002的所有成绩

3．子查询作用：偶尔使用（子查询）；经常使用（视图）

4注意事项：

1) All后面得出的结果为数组，不能使用数字做算式例：a>3*all(b)错误 a/3>all(b)正确

2) Select…from(子查询（子查询）)可层层嵌套，但不可嵌套层数过多造成计算压力

3) Select…from…子查询 as 子查询名称（from后的子查询最好定义别名，使其更易理解）

Sql运行顺序：先运行子查询，其余句子按上节课方式运行

三、 标量子查询

Where不能使用汇总函数

1． 什么是标量子查询（只可返回一行一列单一的值）

大于平均成绩学生的学号和成绩

成绩介于差生平均成绩与优等生平均成绩之间的学生学号和成绩（差生<=60,优等生>=80）

2.如何使用标量子查询

查询所有学生学号、成绩、平均成绩

3.标量子查询有什么用

偶尔使用（子查询（多行---子查询；单一值---标量子查询））

4.注意事项

标量子查询仅可返回单一值，否则为普通子查询，普通子查询不可用于比较运算

下例中子查询报错因为其中返回了多行，而在一行select语句中不可使用多行数据

四、 关联子查询

1． 什么是关联子查询

2． 如何使用关联子查询

练习：查找出每个课程中大于对应课程平均成绩的学生

--首先查找出每门课程的平均成绩

Select avg(成绩) from score group by 课程号

--最终SQL

Select 学号,课程号,成绩 from score as s1

Where 成绩>

( Select avg(成绩) from score as s2

Where s1.课程号 = s2.课程号

/*按课程号对成绩分组，同一组的成绩跟其对应的平均成绩比较, 关联条件仅能写在子查询中*/

group by 课程号)

3． 关联子查询作用（在每个组里进行比较）

五、 如何使用SQL解决业务问题

1． 翻译成大白话

2． 写出分析思路

3． 写出对应的SQL语句

4． 如何看懂sql报错信息（先运行子查询看看，子查询没问题再检查别的，做到逐一排查）

六、 常用函数

1． 汇总函数（countsumavgmaxmin）

2． 算术函数

四舍五入：Round(数值、保留小数位数)—round（14.56,1）结果14.6-----round(14.56,-1)结果10

绝对值：abs(数值)------abs(-100)结果100

求余数：mod(被除数,除数)-----mod(5,2)结果：1

3． 字符串函数

字符串长度：length(字符串)-----length(‘abcde’)结果5

大写转换为小写：lower(字符串)----lower(‘A’)结果a

小写转换为大写：upper(字符串)-----upper(‘a’)结果A

字符串拼接：concat(字符串1,字符串2)-----concat(‘调音师’,’真好看’)结果：调音师真好看

字符串替换：replace(字符串，被替换的字符串，用什么字符串替换)----replace(‘调音师真好看’,’真好看’,’真棒’)结果：调音师真棒

字符串截取：substring(字符串,截取的起始位置,截取长度)-----substring(‘abcde’,2,3)结果：bcd

4． 日期函数

当前日期：current_date

当前时间：current_time

日期和时间：current_timestamp

获取日期的年份月份：year(日期)month(日期)day(日期)----year(‘2020/7/26’)结果：2020

日期对应星期几：dayname(日期)---dayname(‘2020/7/26 10:41:23’)结果：星期六

例：--查询1990年出生的学生名单

七、 sqlzoo

1.列出符合条件的国家名称，条件：国家人口大于俄罗斯(Russia)的人口

【知识点】标量子查询

思路：第一步先查找到俄罗斯的人口，第二步查找出国家人口大于俄罗斯人口的国家名称

Select name,population from world where population>(Select population from world where name=’ Russia’)

2. 列出欧洲每个国家的人均GDP，其中人均GDP要高于英国(United Kingdom)

【知识点】比较运算符（人均GDP=人口/gdp），逻辑运算符（and），标量子查询

思路：第一步查找出英国的人均GDP，第二步查找出欧洲每个国家的人均GDP

Select name, population/gdp as 人均GDP from world where continent=’Europe’ and gdp/population >(Select gdp/population from world where name=’ United Kingdom’)

3. 在阿根廷(Argentina)和澳大利亞(Australia)所在的洲份中的国家有哪些？查找出国家名称和洲名称，并按国家名称排序

【知识点】在运算符in里使用子查询

思路：第一步查找出阿根廷和澳大利亚所在的洲份，第二部查找出其对应的国家名称和州名称，第三步对结果排序

Select continent ,name from world where continent in(Select continent from world where name in (‘Argentina’,’ Australia’)) order by name

4. 查找符合下面条件的国家名称和人口：国家的人口比加拿大(Canada)的多，但比波兰(Poland)的少

【知识点】在运算符between里使用标量子查询，这里用between查找出的范围边界值包括了边界值，所以要+1，和-1去掉边界值

比如范围是 1=<x<=10，（其中1是加拿大人口，10是波兰人口），为了不包括边界值，需要去掉两个边界值，变成 1+1=<x<= (10-1)

思路：第一步分别查找出加拿大和波兰的人口，第二步查找人口在加拿大和波兰之间的国家名称和人口（包含边界值）

Select name,population from world where population between (Select population from world where name=’ Canada’)+1 and (Select population from world where name=’ Poland’)+1

5. 德国(Germany)在欧洲(Europe)國家的人口最多。奧地利(Austria)拥有德国总人口的11％。

查找欧洲的国家名称和每个国家的人口，其中人口以德国人口的百分比来显示人口数

【知识点】标量子查询，字符串连接函数concat,浮点数保留多少位round函数

round函数用于把数值字段舍入为指定的小数位数，用法：

round(数值,返回的小数位数)

思路：第一步查找出德国人口，第二部查找欧洲的国家名称和人口

Select name,concat(round(population /(Select population from world where name=’ Germany’) *100,0),’ %’) as 人口占德国比重 from world where continent=’ Europe’

6．哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

思路：第一步查找出欧洲全部国家的GDP，第二步查找出GDP高于所有欧洲国家的国家名称,用gdp>0去除空值

Select name from world where gdp>all(Select gdp from world where continent=’ Europe’ and gdp>0)

7. 在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

思路：属于分组找最大问题，套用模板

Select continent,name,area from world as x where area =(select max(area) from world as y where x.continent=y.continent)

8. 列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

思路：分组比较取最小值

Select continent,name from world as x where name <=all (select name from world as y where x.continent=y.continent)

9. 找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

思路：分组比较取符合条件值

Select name,continent,population from world as x where 25000000 > continent in (Select population from world where x.continent = y.continent)

10. 有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

思路：分组判断问题

Select name,continent from world as x where population/3 > all (select population from world as y where x.continent=y.continent and x.name<>y.name)

八、心得

本节课复杂查询有一定难度，跟着老师练习基本可以完成随堂练习

SQLZOO上的学习需要加强，之前sqlzoo网站有问题，只是看懂后面的答案

但今天独立完成还是一定难度，重点在于对题目的理解及拆解，在看懂答案后独立完成还是有8题和9题做错，对题目理解不到位，做题太少，以后需要自己先独立完成sqlzoo题目，再去对答案，对于错题需要反复加强记忆