2020-06-28

MySQL之小白的自述 终

子查询 （重点）

我们可以在一个 SQL 语句中放入另一个 SQL 语句。当我们在 WHERE 子句或 HAVING 子句中插入另一个 SQL 语句时，我们就有一个子查询 (subquery) 的架构。 子查询的作用
是什么呢？第一，它可以被用来连接表格。另外，有的时候子查询是唯一能够连接两个表格的方式。

子查询的语法如下：

SELECT “栏位 1”
FROM “表格”
WHERE “栏位 2” [比较运算素]
(SELECT “栏位 1”
FROM “表格”
WHERE [条件])

[比较运算素] 可以是相等的运算素，例如 =, >, <, >=, <=. 这也可以是一个对文字的运算素，
例如 “LIKE”。绿色的部分代表外查询，红色的部分代表内查询。

我们就用刚刚在阐述 SQL 连接时用过的例子：

Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-2018
San Diego $250 Jan-07-2018
Los Angeles $300 Jan-08-2018
Boston $700 Jan-08-2018
Geography 表格
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
我们要运用 subquery 来找出所有在西部的店的营业额。我们可以用下面的 SQL 来达到我
们的目的：

SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = ‘West’)
结果:

SUM(Sales)
2050

在这个例子中，我们并没有直接将两个表格连接起来，然后由此直接算出每一间西区店面的营业额。我们做的是先找出哪些店是在西区的，然后再算出这些店的营业额总共是多少。

在以上的例子，内部查询本身与外部查询没有关系。这一类的子查询称为『简单子查询』
(Simple Subquery)。如果内部查询是要利用到外部查询提到的表格中的栏位，那这个字查询就被称为『相关子查询』 (Correlated Subquery)。以下是一个相关子查询的例子：

SELECT SUM(a1.Sales) FROM Store_Information a1
WHERE a1.Store_name IN
(SELECT store_name FROM Geography a2
WHERE a2.store_name = a1.store_name)

红色部分即是外部查询提到的表格中的栏位。

EXISTS

在上一页中，我们用 IN 来连接内查询和外查询。另外有数个方式，例如 >， <， 及 =，都可以用来连接内查询和外查询。 EXISTS 也是其中一种方式。这一页我们将讨论 EXISTS
的用法。

基本上， EXISTS 是用来测试内查询有没有产生任何结果。如果有的话，系统就会执行外
查询中的 SQL。若是没有的话，那整个 SQL 语句就不会产生任何结果。

EXISTS 的语法是：

SELECT “栏位 1”
FROM “表格 1”
WHERE EXISTS
(SELECT *
FROM “表格 2” WHERE [条件])

在内查询中，我们并不一定要用 * 来选出所有的栏位。我们也可以选择表格 2 中的任何栏
位。这两种做法最后的结果是一样的。

来看一个例子。假设我们有以下的两个表格：

Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-2018
San Diego $250 Jan-07-2018
Los Angeles $300 Jan-08-2018
Boston $700 Jan-08-2018
Geography 表格
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
而我们打入的 SQL 是:

SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = ‘West’)

我们会得到以下的答案：

SUM(Sales)
2750

乍看之下，这个答案似乎不太正确，因为内查询有包含一个 [region_name = ‘West’] 的条件，
可是最后的答案并没有包含这个条件。实际上，这并没有问题。在这个例子中，内查询产生
了超过一笔的资料，所以 EXISTS 的条件成立，所以外查询被执行。而外查询本身并没有包含 [region_name = ‘West’] 这个条件。

CASE

CASE 是 SQL 用来做为 if-then-else 之类逻辑的关键字。 CASE 的语法如下：

SELECT CASE (“栏位名”)
WHEN “条件 1” THEN “结果 1” WHEN “条件 2” THEN “结果 2”
…
[ELSE “结果 N”] END
FROM “表格名”

“条件” 可以是一个数值或是公式。 ELSE 子句则并不是必须的。

在我们的 Store_Information 中

Store_Information 表格 store_name Sales Date Los Angeles $1500 Jan-05-2018
San Diego $250 Jan-07-2018
San Francisco $300 Jan-08-2018
Boston $700 Jan-08-2018

若我们要将 ‘Los Angeles’ 的 Sales 数值乘以 2，以及将 ‘San Diego’ 的 Sales 数值乘以 1.5，
我们就键入以下的 SQL：

SELECT store_name, CASE store_name
WHEN ‘Los Angeles’ THEN Sales * 2
WHEN ‘San Diego’ THEN Sales * 1.5
ELSE Sales
END
“New Sales”,
Date
FROM Store_Information

“New Sales” 是用到 CASE 那个栏位的栏位名。

结果:
store_name New Sales Date
Los Angeles $3000 Jan-05-2018
San Diego $375 Jan-07-2018
San Francisco $300 Jan-08-2018
Boston $700 Jan-08-2018

算排名

列出每一行的排名是一个常见的需求，可惜 SQL 并没有一个很直接的方式达到这个需求。要以 SQL 列出排名，基本的概念是要做一个表格自我连结 (self join)，将结果依序列出，然后算出每一行之前 (包含那一行本身) 有多少行数。这样讲读者听得可能有点困惑，所以最好的方式是用一个实例来介绍。假设我们有以下的表格：

Total_Sales 表格
Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20
要找出每一行的排名，我们就打入以下的 SQL 语句：

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

结果:
Name Sales Sales_Rank
Greg 50 1
Sophia 40 2
Stella 20 3
Jeff 20 3
Jennifer 15 5
John 10 6

我们先来看 WHERE 子句。在字句的第一部分 (a1.Sales <= a2.Sales)，我们算出有多少笔资
料 Sales 栏位的值是比自己本身的值小或是相等。如果在 Sales 栏位中没有同样大小的资料，那这部分的 WHERE 子句本身就可以产生出正确的排名。

子句的第二部分，(a1.Sales=a2.Sales and a1.Name = a2.Name)，则是让我们在 Sales 栏位中有同样大小的资料时 (像 Stella 及 Jeff 这两笔资料)，仍然能够产生正确的排名。

算中位数

要算出中位数，我们必须要能够达成以下几个目标：

将资料依序排出，并找出每一行资料的排名。 找出『中间』的排名为何。举例来说，如果总共有 9 笔资料，那中间排名就是 5 (有 4 笔资料比第 5 笔资料大，有 4 笔资料比第 5 笔资料小)。
找出中间排名资料的值。 来看看以下的例子。假设我们有以下的表格：

Total_Sales 表格
Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20
要找出中位数，我们就键入：

SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name) group by a1.Name, a1.Sales order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);

结果:

Median
20

读者将会发现，第 2 行到第 6 行是跟产生 排名 的语句完全一样。第 7 行则是算出中间的排名。DIV 是在 MySQL 中算出商的方式。在不同的数据库中会有不同的方式求商。第 1
行则是列出排名中间的资料值。

算累积总计算出累积总计是一个常见的需求，可惜以 SQL 并没有一个很直接的方式达到这个需求。要以 SQL 算出累积总计，基本上的概念与列出排名类似：第一是先做个表格自我连结 (self join)，然后将结果依序列出。在做列出排名时，我们算出每一行之前 (包含那一行本身) 有多少行数；而在做累积总计时，我们则是算出每一行之前 (包含那一行本身) 的总合。

来看看以下的例子。假设我们有以下的表格：

Total_Sales 表格
Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20
要算出累积总计，我们就键入：

SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

结果:

Name Sales Running_Total
Greg 50 50
Sophia 40 90
Stella 20 110
Jeff 20 130
Jennifer 15 145
John 10 155

在以上的 SQL 语句中， WHERE 子句和 ORDER BY 子句让我们能够在有重复值时能够
算出正确的累积总计。

算总合百分比

要用 SQL 算出总合百分比，我们需要用到算排名和累积总计的概念，以及运用子查询的做
法。在这里，我们把子查询放在外部查询的 SELECT 子句中。让我们来看以下的例子：

Total_Sales 表格
Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20
要算出总合百分比，我们键入：

SELECT a1.Name, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

结果:

Name Sales Pct_To_Total
Greg 50 0.3226
Sophia 40 0.2581
Stella 20 0.1290
Jeff 20 0.1290
Jennifer 15 0.0968
John 10 0.0645

“SELECT SUM(Sales) FROM Total_Sales” 这一段子查询是用来算出总合。总合算出后，我们就能够将每一行一一除以总合来求出每一行的总合百分比。

算累积总合百分比

要用 SQL 累积总合百分比算出，我们运用类似总合百分比的概念。两者的不同处在于在这
个情况下，我们要算出到目前为止的累积总合是所有总合的百分之几，而不是光看每一笔资料是所有总合的百分之几。让我们来看看以下的例子：

Total_Sales 表格
Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20
要算出累积总合百分比，我们键入：

SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

结果:

Name Sales Pct_To_Total
Greg 50 0.3226
Sophia 40 0.5806
Stella 20 0.7097
Jeff 20 0.8387
Jennifer 15 0.9355
John 10 1.0000

“SELECT SUM(Sales) FROM Total_Sales” 这一段子查询是用来算出总合。我们接下来用累
积总计 “SUM(a2.Sales)” 除以总合来求出每一行的累积总合百分比。