计算机三级数据库技术 第6章「高级数据查询」

前言：“No bibi, show the code”

如发现内容错误，评论区轰炸

---

6.1

1. TOP

SELECT TOP 3 WITH TIES cust_id, cust_name

FROM Customer

ORDER BY cust_id

 

2. CASE

SELECT cust_id, cust_name, type =

CASE

WHEN cust_id <= 1000000003 THEN 'Group1'

WHEN cust_id > 1000000003 THEN 'Group2'

FROM Customer

 

3. INTO

SELECT cust_id, cust_name

INTO Customer_new

FROM Customer

 

6.2

1. UNION

SELECT cust_id, cust_name FROM Customer_1

UNION

SELECT cust_id, cust_name FROM Customer_2

 

2. INTERSECT

SELECT cust_id, cust_name FROM Customer_1

INTERSECT

SELECT cust_id, cust_name FROM Customer_2

 

3. EXCEPT

SELECT cust_id, cust_name FROM Customer_1

EXCEPT

SELECT cust_id, cust_name FROM Customer_2

 

6.3

1. 子查询集合

SELECT cust_id, cust_name

FROM Customer

WHERE cust_name IN (SELECT cust_name FROM Customer WHERE cust_id <= 1000000003 )

 

2. 子查询比较

SELECT cust_id, cust_name

FROM Customer

WHERE cust_id IN (SELECT MAX(cust_id) FROM Customer)

 

3. 子查询存在性

SELECT cust_id, cust_name

FROM Customer

WHERE EXISTS (SELECT * FROM Customer WHERE cust_id <= 1000000003 )

 

6.4

1. 替代表达式的子查询

任何能使用表达式的地方都可以使用子查询来替换

 

2. 派生表

SELECT cust_id, cust_name

FROM (SELECT cust_id, cust_name

FROM Customer) AS T1

 

6.5

1. 开窗函数

开窗函数可以分别应用于每个分区，把每个分区看成是一个窗口，并为每个分区进行计算。开窗函数必须放在OVER子句前边

 

2. 公用表表达式

将查询语句产生的结果指定一个临时命名的名字，这些命名的结果集就称为公用表表达式