|
该文章所用数据库仅供参考，语法适用所有数据库。
第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
|