牛客网刷题之SQL篇二：SQL必知必会 50T

一、SQL 知识点

1、SQL编写顺序

select、from、where、group by、having、order by、limit

2、SQL执行顺序

Mysql里面select、from、where、group by、having、order by的执行顺序

1、执行from和where得到第一个结果集

2、对第一个结果集执行group by分组操作，得到第二个结果集

3、对第二个结果集进行select操作，得到第三个结果集

4、对第三个结果集进行having操作，得到第四个结果集

5、对第四个结果集进行order by操作，得到最终结果

3、having和where的区别

1、having必须在执行了group by操作之后才能运行

2、where的执行顺序在group by之前，也就必然在having之前。

3、having后面可以加上聚合函数，而where后面不能加聚和函数如count(),sum(),avg(),max(),min()等函数。

举例：按由高到低的顺序显示个人平均分在70分以上的学生姓名和平均分，为了尽可能地提高平均分，在计算平均分前不包括分数在60分以下的成绩

select 
name,ave(score) 
from scoretable 
where score >= 60 
group by name 
having ave(score) > 70 
order by ave(score) desc;

4、group by 多字段分组

group by 学习

5、union all 和 union

6、

知识点：SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一
部分的情况，因此不允许使用多条 ORDER BY 子句。

错误的SQL
只需去掉 第一行的 ORDER BY cust_name; 即可

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
ORDER BY cust_name; 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'ORDER BY cust_name;

或者(我写的有点麻烦)

select
*
from (
SELECT  
cust_name
, cust_contact
, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 


UNION ALL

SELECT 
cust_name
, cust_contact
, cust_email 
FROM Customers 
WHERE cust_state = 'IL'
) t
ORDER BY cust_name;

二、牛客网题

1、SQL22 顾客登录名

SQL22 顾客登录名：

select
cust_id
,cust_name
,upper(concat(substring(cust_contact,1,2), substring(cust_city, 1, 3))) as user_login
from Customers

upper()：变成大写字母
concat()：连接字符串函数
substring：字符串截取函数

2、SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期

SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期

//方法一
select
order_num
,order_date
from Orders
where order_date like '%2020-01%'
order by order_date

//方法二
select
order_num
,order_date
from Orders
where year(order_date) = '2020'
and month(order_date) = '1'
order by order_date

3、SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格

SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格

// 方式一
select 
max(prod_price) as max_price
from Products
where prod_price <= 10
 
// 方式二
select 
prod_price as max_price
from Products
where prod_price <= 10
order by prod_price desc
limit 1 

4、SQL28 每个供应商成本最低的产品

SQL28 每个供应商成本最低的产品
分组使用聚合函数，可以记住幼:min()+group by

select 
vend_id
,min(prod_price) as cheapest_item
from Products
group by vend_id
order by cheapest_item

5、SQL29 返回订单数量总和不小于100的所有订单的订单号

SQL29 返回订单数量总和不小于100的所有订单的订单号
group by + having + 聚合函数sum()

select
order_num
from OrderItems
group by order_num
having sum(quantity) > 100
order by order_num

6、SQL30 计算总和

SQL30 计算总和
聚合函数sum() + 列乘 + group by + having + order by

select  
order_num
,sum(item_price * quantity) as total_price
from OrderItems
group by order_num
having sum(item_price * quantity) >= 1000
order by order_num

7、SQL38 返回顾客名称和相关订单号以及每个订单的总价

SQL38 返回顾客名称和相关订单号以及每个订单的总价

三表连接、聚合函数、列乘、group by 、order by 、选择多列、select后的字段 应在group by之后（除聚合函数）

select 
cust_name
,o.order_num
,sum(quantity * item_price) as OrderTotal
from Customers c
left join Orders o on c.cust_id = o. cust_id
left join OrderItems oo on o.order_num = oo.order_num
group by cust_name, order_num
order by cust_name, o.order_num, OrderTotal

8、SQL39 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

SQL39 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

//方法一
select 
cust_id
,order_date
from OrderItems o1
left join Orders o2 on o1.order_num = o2.order_num
where prod_id = 'BR01'
order by order_date asc 

#方法二
select 
cust_id, order_date
from Orders,
(select order_num
from OrderItems
where prod_id="BR01") t
where t.order_num = Orders.order_num
order by order_date;

#方法三
select 
cust_id,order_date
from Orders 
where order_num in 
(select order_num
from OrderItems
where prod_id="BR01")
order by order_date;

#方法四
select 
cust_id,order_date
from Orders o join OrderItems oi on o.order_num=oi.order_num
where prod_id="BR01"
order by order_date;

//方法四 
select cust_id,order_date from Orders a,OrderItems b where a.order_num =b.order_num and b.prod_id ='BR01' order by order_date

9、SQL41 确定最佳顾客的另一种方式（二

SQL41 确定最佳顾客的另一种方式（二）
聚合函数sum、列成、inner join、group by、having、order by

select
cust_name
,sum(item_price * quantity) as total_price
from OrderItems o1
inner join Orders o2 on o1.order_num = o2.order_num
inner join Customers c on c.cust_id = o2.cust_id
group by cust_name
having total_price > 1000
order by total_price

三、SQL进阶挑战

1、