複雜查詢—子查詢

路飛小朋友發表於2020-11-16

基本概念

子查詢定義

子查詢指的是在嵌入在其他 sql 語句中的 select 語句，又稱為內部查詢、巢狀查詢
子查詢，又叫內部查詢，相對於內部查詢，包含內部查詢的就稱為外部查詢
內部查詢只能是 select 語句且可以包括任何子句
外部查詢可以是 select，insert，update，delete，set 或 do

子查詢的位置

select 中，from 後，where 中
group by 和order by 中也可以，但無實用意義

子查詢分類

單行單列：返回的是一個具體列的內容，可以理解為一個單值資料
多行單列：返回多行記錄之中同一列的內容，相當於給出了一個操作範圍
單行多列：返回一行資料中多個列的內容
多行多列：查詢返回的結果是一張臨時表

單行單列（where中）

顯示與smith同一部門的所有員工資訊，不包括smith

mysql> select * from emp where deptno=(select deptno from emp where ename='smith') and ename <> 'smith';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | ford  | analyst | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

多行單列（where中）

in 關鍵字

//查詢和部門10的工作相同的僱員的資訊，但是不含10自己的  
mysql> select * from emp where job in (select distinct job from emp where deptno=10) and deptno <> 10;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7900 | james | clerk   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
+-------+-------+---------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)

all 關鍵字

<>all：等價於not in（但是=all並不等價於in）
>all：比子查詢中最大的值還要大（還包含了>=all）
<all：比子查詢中最小的值還要小（還包含了<=all）

//顯示工資比部門30的所有員工的工資高的員工資訊
mysql> select * from emp where sal > all(select sal from emp where deptno=30);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.45 sec)

mysql> select * from emp where sal > (select max(sal) from emp where deptno=30);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)

any關鍵字

=any：表示與子查詢中的每個元素進行比較，功能與in類似（然而<>any不等價於not in）
>any：比子查詢中返回結果的最小的要大（還包含了>=any）
<any：比子查詢中返回結果的最大的要小（還包含了<=any）

//顯示工資比部門30的任一員工的工資高的員工資訊
mysql> select * from emp where sal > any(select sal from emp where deptno=30);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)

mysql> select * from emp where sal > (select min(sal) from emp where deptno=30);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)

單行多列（where中）

查詢和 allen 部門崗位完全相同的僱員資訊

//標準寫法
mysql> select * from emp where (deptno,job) = (select deptno,job from emp where ename='allen');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)

//自己寫的 比較low
mysql> select * from emp where deptno = (select deptno from emp where ename='allen') and job = (select job from emp where ename='allen');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.01 sec)

在from中使用子查詢

核心思想就是把子查詢的結果當作一個臨時表來使用

顯示高於自己部門平均工資的員工的資訊

//先獲取各個部門的平均工資
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2443.750000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

//將上一步的結果當作臨時表 進行多表查詢
mysql> select * from emp,(select deptno,avg(sal) from emp group by deptno) temp where emp.deptno = temp.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | deptno | avg(sal)    |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | 2443.750000 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | 1566.666667 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | 1566.666667 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | 2443.750000 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | 1566.666667 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | 1566.666667 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | 2916.666667 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | 2443.750000 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | 2916.666667 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |     30 | 1566.666667 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | 1566.666667 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | 2443.750000 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | 2916.666667 |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
13 rows in set (0.00 sec)

//篩選結果

Laravel Query Builder 複雜查詢案例：子查詢實現分割槽查詢 partition by
2018-11-27
LaravelUI
Solr複雜查詢一：函式查詢
2020-04-17
Solr函式
SQL 複雜查詢
2022-03-14
SQL
oracle表複雜查詢
2020-04-04
Oracle
JPA的多表複雜查詢
2019-08-03
SQL查詢的：子查詢和多表查詢
2020-11-18
SQL
Laravel使用MongoDB複雜的查詢
2021-01-14
LaravelMongoDB
SQL學習(三）複雜查詢
2020-12-20
SQL
Elasticsearch複合查詢——boosting查詢
2021-11-17
Elasticsearch
mysql-分組查詢-子查詢-連線查詢-組合查詢
2020-12-22
MySql
Hibernate中不支援複雜子查詢from (select ……)解決方案
2018-09-18
MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]
2024-03-10
MySql筆記
複雜查詢還是直接寫sql吧
2024-08-08
SQL
linux中查詢find命令的複雜用法
2021-09-19
Linux
微服務複雜查詢之快取策略
2021-03-15
微服務快取
MySQL之連線查詢和子查詢
2021-09-04
MySql
es的複雜查詢測試，使用jest的dsl工具寫查詢語句
2020-12-19
MYsql 子查詢
2018-05-15
MySql
MySQL子查詢
2019-11-29
MySql
子串查詢
2020-11-04
寫一個“特殊”的查詢構造器 – (四、條件查詢：複雜條件)
2019-02-16
Spring JPA聯表情況下的複雜查詢
2019-02-23
Spring
探討一個比較複雜的查詢
2020-10-19
基於 MongoTemplate 實現MongoDB的複雜查詢
2024-12-03
MongoDB
如何完成複雜查詢的動態構建？
2021-11-17
區分關聯子查詢和非關聯子查詢
2019-05-21
Elasticsearch 複合查詢——多字串多欄位查詢
2021-03-14
Elasticsearch字串
Javaweb-子查詢
2024-09-08
JavaWeb
巢狀子查詢
2021-09-09
巢狀
GORM subquery 子查詢
2021-06-25
GoORM
字串查詢（字串雜湊）
2020-11-10
字串
MYSQL學習筆記26: 多表查詢|子查詢
2024-03-14
MySql筆記
MySQL資料庫基礎——多表查詢：子查詢
2020-10-30
MySql資料庫
MySQL全面瓦解11：子查詢和組合查詢
2020-11-17
MySql
34. 過濾條件、多表查詢、子查詢
2024-10-28
LinQ查詢基礎（三）LINQ to ADO.net（1）LINQ to DataSet實現複雜資料查詢
2018-05-05
Mysql 日期格式化複雜日期區間查詢
2021-08-31
MySql
Excel查詢重複項
2019-03-26
Excel

複雜查詢—子查詢

基本概念

單行單列（where中）

多行單列（where中）

單行多列（where中）

在from中使用子查詢

相關文章