数据库知识点03

该文章所用数据库仅供参考，语法适用所有数据库。

第6节. 分组数据

A. SQL Server Group By语句

Group by 指定规则对数据进行分组。

示例：

select city,count(*) from sales.customers

group by city

select city,state,count(*) from sales.customers

group by city,state

order by state

select count(*) from sales.customers

group by customer_id,fist_name,last_name

B. GROUP BY子句和聚合函数

GROUP BY 子句通常与聚合函数一起用于统计数据。

聚合函数对组执行计算并返回每个组的唯一值。 例如， COUNT() 函数返回每个组中的行数。

其他常用的聚合函数是： SUM()总和 ， AVG()平均值 ， MIN()最小值 ， MAX()最大值 。

GROUP BY 子句将行排列成组，聚合函数返回每个组的统计量(总数量，最小值，最大值，平均值，总和等)。

示例：

--查询返回客户按年度下单的订单数

select customer_id,year(order_date) from sales.orders

select customer_id,year(order_date),count(*) 

from sales.orders

group by customer_id,year(order_date)

order by customer_id

--查询某个产品的最高价格

select category_id,max(list_price) 最大,min(list_price) 最小,avg(list_price) 平均,count(*)

from production.products

group by category_id

having avg(list_price)>1000

;

C. SQL Server Having子句

HAVING 子句通常与[GROUP BY]子句一起使用，以根据指定的条件列表过滤分组。

--查询某个产品的最高价格

select category_id,max(list_price) 最大,min(list_price) 最小,avg(list_price) 平均,count(*)

from production.products

group by category_id

having avg(list_price)>1000

第7节. 子查询

A. SQL Server子查询

子查询是嵌套在另一个语句(如：[SELECT]，[INSERT]，[UPDATE]或[DELETE])中的查询。

--使用子查询来查找位于纽约('New York')的客户的销售订单:

select * from sales.customers

where city = 'New York'

select * from sales.orders

where customer_id in (

select customer_id from sales.customers

where city = 'New York'

)

B. SQL Server嵌套子查询 

子查询可以嵌套在另一个子查询中。

SQL Server最多支持 32 个嵌套级别。

--查找价格高于'上海永久'和'凤凰'品牌的所有产品的平均定价的产品

select * from production.brands;

select * from production.products

where list_price > (

select avg(list_price)

from production.products

where brand_id in(

select brand_id from production.brands

where brand_name in('上海永久','凤凰')

  )

)

C. SQL Server相关子查询

相关子查询是使用外部查询的值的[子查询]。 换句话说，它取决于外部查询的值。 由于这种依赖性，相 关子查询不能作为简单子查询独立执行。 此外，对外部查询评估的每一行重复执行一次相关子查询。 相关子查询也称为重复子查询。

--查找价格等于其类别的最高价格的产品

select p1.* from production.products p1

inner join (

  select category_id,max(list_price) max_price

  from production.products

  group by category_id

) p2

on p1.category_id = p2.category_id

and p1.list_price = p2.max_price

;

select p1.* from production.products p1

where p1.list_price in (

---321次内层查询

   select max(list_price) max_price

   from production.products p2

   where p2.category_id = p1.category_id

   group by category_id

)

select p1.* from production.products p1

order by category_id

D.SQL Server Exists运算符

EXISTS 运算符是一个逻辑运算符，用于检查子查询是否返回任何行。 如果子查询返回一行或多行，则 EXISTS 运算符返回 TRUE 。

以下是SQL Server EXISTS 运算符的语法：

EXISTS ( subquery)

在此语法中，子查询仅是 SELECT 语句。子查询返回行后， EXISTS 运算符返回 TRUE 并立即停止处理。请注意，即使子查询返回 NULL 值， EXISTS 运算符也会计算为 TRUE

select null

select p1.* from production.products p1 where product_id = 1

select * from production.products p1

where exists (

   select * from production.products p2

   where p2.list_price > 8000

)

EXISTS 与 IN示例

--每个经理的下属员工

select * from sales.staffs

where manager_id in (

select manager_id from sales.staffs

)

--员工'Wiggins'的经理管理的所有员工

select * from sales.staffs

where manager_id = (

select manager_id from sales.staffs

where last_name = 'Wiggins'

)

--提问2:返回所有型号年份为'2018'的最低和最高价产品;

select category_id,model_year, 

max(list_price) 最高价,min(list_price) 最低价

from production.products

where model_year = 2018

group by category_id,model_year

--返回型号年份为'2018'年的所有产品的平均价格

select category_id,model_year, 

avg(list_price) 平均价格

from production.products

where model_year = 2018

group by category_id,model_year

--价格等于其类别的最高价格的产品

select * from production.products p1

inner join (

select category_id,max(list_price) 最高价格

from production.products

group by category_id) p2

on p1.category_id = p2.category_id

and p1.list_price = p2.最高价格;

select * from production.products p1

where p1.list_price = (

   select max(list_price) 最高价格

   from production.products p2

   where p1.category_id = p2.category_id

   group by category_id

)

E. SQL Server Any运算符

ANY 运算符是一个逻辑运算符，它将标量值与子查询返回的单列值集进行比较。

--查找销售订单中销售的数量超过'2'个的产品

select * from sales.order_items

select * from production.products

where product_id = any(

select product_id

from sales.order_items

group by product_id

having sum(quantity) > 2

)

select brand_id from production.brands

--查找价格大于'优米优'品牌产品的最高价格的产品

select * from production.products

where list_price > any (

select list_price from production.products

where brand_id = 9)

and brand_id != 9

F. SQL Server All运算符

SQL Server ALL 运算符是一个逻辑运算符，它将标量值与子查询返回的单列值列表进行比较。

--查找其他品牌价格大于'优米优'品牌任何产品的价格的产品

select * from production.products

where list_price > all (

select list_price from production.products

where brand_id = 8)

and brand_id != 8

order by list_price