牛客SQL--SQL必知必会刷题记录

SQL刷题

  • 1、group by 可加select后的别名
  • 2、substring_index使用
  • 3、自表的笛卡儿积就是for循环
  • 4、select 后不能接与group by无关字段(一对多问题)+多变量in操作
  • 5、子查询练习
  • 6、count+if+null可完成同表不同值的计数
  • 7、substring细节
  • 8、子查询好处
    • 子查询将各自的查询、计算等逻辑限制在各自的表内
    • 参考文献
    • 限定逻辑在特定范围内+子查询为我们想先操作逻辑(不会被MySQL优化)
    • 常见count(非null)、计数逻辑在内、排序逻辑在外、ifnull()置0
  • 9、left、right join + union实现MySQL不能实现的outer join
  • 10、union 生效在 order by之前

1、group by 可加select后的别名

牛客SQL--SQL必知必会刷题记录_第1张图片

select
substring_index(substring_index(`profile`,',',-2),',',1) as age,
count(device_id) as number
from user_submit
group by age
# 注:不能count(age),age字段不存在,不知道为啥。

2、substring_index使用

牛客SQL--SQL必知必会刷题记录_第2张图片

/*
select 'male',count(device_id) from user_submit where `profile` like '%,male'
union
select 'female',count(device_id) from user_submit where `profile` like '%female'
*/
# 变形
/*
select
if(`profile` like '%female','female','male') as gender,
count(1) as number
from user_submit
group by gender
*/
# 采用文本分隔函数,substring_index(str,substr,第几次出现,负数表示倒数 to 字符串末尾)
/*
select 
substring_index(`profile`,',','-1') as gender,
count(1) as number
from user_submit
group by gender
*/
# WHEN THEN 练习
select
CASE
when profile like '%female' then 'female'
when profile like '%,male' then 'male'
end
as gender,
count(1) as number
from user_submit
group by gender

3、自表的笛卡儿积就是for循环

牛客SQL--SQL必知必会刷题记录_第3张图片

# 1-如何确定总人数?2-如何确定前后两条都在刷题的人数?
# 1-总人数?不分组,直接去重计算device_id的个数。

# 2-前后两天都在刷题的人数?

/*select 
count(date2) / count(date1)
from (
select
    distinct qpd.device_id,
    qpd.date as date1,
    qpd2.date as date2
    from question_practice_detail as qpd
    left join question_practice_detail as qpd2
    on
    qpd.device_id = qpd2.device_id
    and
    1 = timestampdiff(day,qpd.date,qpd2.date)
) as next_time
*/
select
    distinct qpd.device_id,
    qpd.date as date1,
    qpd2.date as date2
    from question_practice_detail as qpd
    left join question_practice_detail as qpd2
    on
    qpd.device_id = qpd2.device_id
    and
    1 = timestampdiff(day,qpd.date,qpd2.date)

4、select 后不能接与group by无关字段(一对多问题)+多变量in操作

牛客SQL--SQL必知必会刷题记录_第4张图片

# 不能直接在外层select中取device_id
/*
select
u1.device_id,u1.university,u1.gpa
from user_profile u1
inner join 
(
select university,min(gpa) as gpa
    from user_profile
    group by university
) as u2
on 
u1.university = u2.university
and u1.gpa = u2.gpa
order by u1.university
*/
# 直接以in的方式
select
device_id,university,gpa
from user_profile
where
(university,gpa)
in
(
select university,min(gpa) from user_profile
    group by university
)
order by university

5、子查询练习

子查询把哪些必须分开的查询分开变成view,然后结合,再查出自己想要的字段。
毕竟group by 会和 一对多的字段冲突。
毕竟group by 会和 不同条件计数冲突。
牛客SQL--SQL必知必会刷题记录_第5张图片
牛客SQL--SQL必知必会刷题记录_第6张图片

/*
device_id 和question_id有一对多问题,在统计时就不能一起查出来。
所以,
第一步:先统计question_cnt,right_question_cnt。
第二步,再统计device_id,university
*/
select
u1.device_id,u1.university,
if(u2.question_cnt is null,0,u2.question_cnt),
if(d1.right_question_cnt is null,0,d1.right_question_cnt)
from user_profile u1
left join

( select device_id,
count(question_id) as question_cnt
from question_practice_detail
where
MONTH(date) = 8
group by device_id ) as u2

on
u1.device_id = u2.device_id

left join(
select device_id,count(result) as right_question_cnt
    from question_practice_detail
    where result = 'right'
    group by device_id
) as d1

on
d1.device_id = u2.device_id
where u1.university = '复旦大学'

/* 
两个子查询所产生的衍生视图,三表左外连接。
左外?因为8月份有复旦大学个别同学没有参加答题,题目要求记为0,内联会导致该用户丢失。
左外2?因为有同学答题全是错的,题目要求记为0,内联会导致没有用户答题记录,毕竟全错。

衍生视图1-2?答题数量肯定不能会答对数量一起count出来,
所以只有竖着拆出两表记录count(question_id),count(result_right_cnt)
两个count建立在不同的条件,所以肯定分开查询再联合成表之后,再查自己想要的字段。
*/

6、count+if+null可完成同表不同值的计数

1)成长需要过程,需要迭代的版本,不可能是一步登天的人。
2)count不统计null,所以可count+if+null或者sum|avg + if + (0,1)来完成不同条件的统计,减少多个子查询以及其中的重复表操作。
牛客SQL--SQL必知必会刷题记录_第7张图片
牛客SQL--SQL必知必会刷题记录_第8张图片
牛客SQL--SQL必知必会刷题记录_第9张图片

/*
取 浙大用户回答题目不同难度的个数 m
取 浙大用户回答题目不同难度的正确个数 n
得 正确率 = n / m
排 按正确率升序输出
*/
# 取1 
/*
select
difficult_level,
count(qpd.question_id) as question_cnt

from user_profile u
inner join question_practice_detail qpd
on
u.device_id = qpd.device_id

right join question_detail qd
on
qpd.question_id = qd.question_id
where university = '浙江大学'
group by difficult_level
*/
# 取2 
/*
select
difficult_level,
count(qpd.question_id) as question_right_cnt

from user_profile u
inner join question_practice_detail qpd
on
u.device_id = qpd.device_id
inner join question_detail qd
on
qpd.question_id = qd.question_id
group by difficult_level
where university = '浙江大学'
and
result = 'right'
*/
# 得 + 排 t1 left join t2表,毕竟分子可能为none,但也要记录下来。
/*
select
t1.difficult_level,
if(t2.question_right_cnt is null,0,t2.question_right_cnt) / t1.question_cnt as correct_rate
from
(
select
difficult_level,
count(qpd.question_id) as question_cnt
from user_profile u
inner join question_practice_detail qpd
on
u.device_id = qpd.device_id
inner join question_detail qd
on
qpd.question_id = qd.question_id
where university = '浙江大学'
group by difficult_level
) as t1
left join
(
select
difficult_level,
count(qpd.question_id) as question_right_cnt

from user_profile u
inner join question_practice_detail qpd
on
u.device_id = qpd.device_id
inner join question_detail qd
on
qpd.question_id = qd.question_id
where university = '浙江大学'
and
result = 'right'
group by difficult_level
) as t2
on
t1.difficult_level = t2.difficult_level
order by correct_rate
*/
# 注:count不统计null
# 改进:取正确的个数和取总个数可以一起取,用if+null来完成。
select
difficult_level,
count(if(b.result = 'right',b.question_id,null)) / count(b.question_id) as correct_rate
/*
除了count(),还可以用sum()和avg()来实现。
sum():sum(if(b.result = 'right',1,0))
avg():avg(if(b.result = 'right',1,0))
*/
from
(
select device_id
    from user_profile
    where university = '浙江大学' #提前把表变小
) as a
inner join
(
select device_id,question_id,result
    from question_practice_detail # 提前把表变小
) as b
on
a.device_id = b.device_id
inner join question_detail as c
on b.question_id = c.question_id
group by difficult_level
order by correct_rate

7、substring细节

牛客SQL--SQL必知必会刷题记录_第10张图片

select cust_id,
cust_name,
upper(concat(substring(cust_name,1,2),substring(cust_city,1,3))) as user_login
from Customers
# 注:substring(str,第几位而不是从0开始的index,截多少)

8、子查询好处

子查询可以生成轻量级table–view,这样可以where、distinct、select 指定字段等筛选条件来完成数据源table的瘦身。然后再用小的view去join 其它 table,不仅可以减少很多不必要的连接,而且还可以小表驱动大表,除此之外,逻辑分明,删选条件出现在自己的table中,而不是影响延申到整个大表。
总:子查询三点好处
1)逻辑分明,筛选条件在自己的table中,不影响其它表。
2)数据源的瘦身,减少接下来不必要的连接等所带来的时空开销。
3)如果存在索引,还可以小表驱动大表,减少时空开销。
当然,有时候子查询甚至是解题的关键,而不是提升时空效率的辅助。
牛客SQL--SQL必知必会刷题记录_第11张图片
牛客SQL--SQL必知必会刷题记录_第12张图片

# 非子查询方法
/*
select distinct o2.cust_id
from OrderItems o1
inner join Orders o2
on
o1.order_num = o2.order_num
where o1.item_price >= 10
*/
# 上述方法-缺点:要先连表,再筛选,所以会很大的表,浪费时间和空间,可子查询为小的view解决。
# 拿 OrderItems的小view,且去重了,避免没必要的连接
/*
select distinct order_num
from OrderItems
where item_price >=10
*/
# 用小view来inner join Orders表,这样小view来驱动大表,减少很多不必要的连接。
select distinct cust_id
from (
select distinct order_num
from OrderItems
where item_price >= 10
) as OI
inner join
Orders o1
on
OI.order_num = o1.order_num

牛客SQL--SQL必知必会刷题记录_第13张图片
牛客SQL--SQL必知必会刷题记录_第14张图片

/*
取哪些用户什么时候买了prod_id为BR01品牌的产品
注:BR01有多种产品;取了之后还需对购买日期进行升序排序
M1:直接inner join 两表,然后取买了prod_id为BR01的哪些用户和买的时间
*/
/*
select o.cust_id,o.order_date
from OrderItems oi
inner join Orders o
on oi.order_num = o.order_num
where oi.prod_id = 'BR01'
order by o.order_date
*/
/*
M1-defect:筛选逻辑不分明;有很多不必要的连接;没有小表驱动大表。
M2:采取子查询,筛选逻辑限定在各自的表之内,不忘外延申。
*/
# 取prod_id为BR01的商品名order_num
/*
select order_num
from OrderItems
where prod_id = 'BR01'
*/
# inner join Orders来取出对应order_num的记录中的cust_id和order_date,并对order_date进行排序
select cust_id,order_date
from 
(
select order_num
from OrderItems
where prod_id = 'BR01'
) as T1
inner join Orders o
on T1.order_num = o.order_num
order by order_date

牛客SQL--SQL必知必会刷题记录_第15张图片
牛客SQL--SQL必知必会刷题记录_第16张图片

/*
取购买了BR01产品的用户信息--cust_email。
注:用户信息和OrderItems中有个Orders表相连,而不是直接相连。
M1:直接三表相连,取对应条件--prod_id = 'BR01';
注:可小表驱动大表,用用户表来驱动order表。
*/
/*
select cust_email
from Customers c
inner join Orders o
on c.cust_id = o.cust_id
inner join OrderItems oi
on o.order_num = oi.order_num
where oi.prod_id = 'BR01'
*/
/*
M1-defect:筛选逻辑不分明;很多不必要的连接;小表驱动可以再明显点,即用小的view来驱动。
M2:用筛选逻辑分明的子查询来生成小的view,再进行驱动其它表,可以减少不必要的时空开销。
*/
# prod_id='BR01'的限定属于OrderItems表,所以先生成一个小view
/*
select order_num
from OrderItems
where prod_id = 'BR01'
*/
# M2:三表相连,用小view驱动,再取出需要的记录即可,没有额外的筛选条件
select cust_email
from 
(
select order_num
from OrderItems
where prod_id = 'BR01'
) as T1
inner join Orders as T2
on T1.order_num = T2.order_num
inner join Customers as T3
on T2.cust_id = T3.cust_id

牛客SQL--SQL必知必会刷题记录_第17张图片
牛客SQL--SQL必知必会刷题记录_第18张图片

/*
取用户购买商品的总金额?
总金额?item_price * quantity as total
当个用户的总金额?sum(total) as total_ordered + group by cust_id
M1:两表相连,以cust_id分组,求总金额。最后降序排序。
注:可小表Customers来驱动其它表
*/
/*
select
T1.cust_id,
sum(T2.item_price * T2.quantity) as total_ordered
from Orders as T1
inner join OrderItems as T2
on T2.order_num = T1.order_num
group by T1.cust_id
order by total_ordered desc
*/
/*
M1-defect: total_ordered是再T2表中计算出来的,再在T1表中进一步,所以计算逻辑需分层。
M2:子查询将计算逻辑限制在各自的表内
*/
# 取total_ordered & order_num
/*
select order_num,sum(item_price * quantity) as total
from OrderItems
group by order_num
*/
# view join Orders,拿到cust_id & total_ordered,并对total_ordered。
select cust_id,
sum(T1.total) as total_ordered
from
(
select order_num,sum(item_price * quantity) as total
from OrderItems
group by order_num
) as T1
inner join Orders T2
on T1.order_num = T2.order_num
group by cust_id
order by total_ordered desc

子查询将各自的查询、计算等逻辑限制在各自的表内

牛客SQL--SQL必知必会刷题记录_第19张图片
牛客SQL--SQL必知必会刷题记录_第20张图片

/* 取prod_id对应订单的总额度。
M1:连接Products和OrderItems表,以prod_name(不唯一)为组,对应的prod_name,以及sum(quantity)
注:不能以prod_id为组,因为我们取的是prod_name。虽然prod_id与prod_name一对一。
*/
/*
select
T1.prod_name,sum(quantity) as quant_sold
from Products T1
inner join OrderItems T2
on T1.prod_id = T2.prod_id
group by T1.prod_name
*/
/*
M1-defect:sum(quantity)求和逻辑应该限定在OrderItems表内,而不是延申到相连的大表中。
M2:用子查询将各自计算逻辑限定在各自的表中。
*/
# 在OrderItems表体现计算逻辑
/*
select prod_id,sum(quantity) as quant_sold
from OrderItems
group by prod_id
*/
# 将Products与子查询view相连,直接体现查询数据字段逻辑。
select prod_name,T2.quant_sold
from Products T1
inner join
(
select prod_id,sum(quantity) as quant_sold
from OrderItems
group by prod_id
) as T2
on T1.prod_id = T2.prod_id

牛客SQL--SQL必知必会刷题记录_第21张图片
牛客SQL--SQL必知必会刷题记录_第22张图片

/*
target:取Customers表中的cust_name,再取其用cust_id对应到Orders表中order_num字段。
M1:Customers join Orders表,再在该view基础上实现取字段逻辑。
注:Customer表相对于Orders表肯定小很多,需要进行小表驱动大表。
注:取出元素之后,还需对顾客名称和订单号进行升序
*/
/*
select cust_name,order_num
from Customers T1
inner join Orders T2
on T1.cust_id = T2.cust_id
order by cust_name,order_num
*/
/*
M1-defect:对顾客名称的排序逻辑应该限定在view中,因为是先按顾客名排的;
对order_num的排序应该限定在Orders中。
M2:采用子查询来将逻辑限定到自己的表中。
*/

select cust_name,order_num
from
(
    select order_num,cust_id
    from Orders
    order by order_num
) as T1
inner join Customers as T2
on T1.cust_id = T2.cust_id
order by cust_name
/*
注:由于MySQL会进行联表优化,所以会小表驱动大表.
所以无论把那个表写在前面,MySQL都会选一个小表来驱动。
所以这里采用单个子查询,让Orders的衍生出表操作先完成,再配合Customers表
*/

参考文献

[1] SQL联表细节,MySQL JOIN 的执行过程

限定逻辑在特定范围内+子查询为我们想先操作逻辑(不会被MySQL优化)

牛客SQL--SQL必知必会刷题记录_第23张图片
牛客SQL--SQL必知必会刷题记录_第24张图片
牛客SQL--SQL必知必会刷题记录_第25张图片

/*
target:取三表连接view中的cust_name,order_num,以及OrderTotal = quantity * item_price
注:结构需要对顾客名称及订单号进行升序。
M1:三表相连--cust_id + order_num,再升序即可
*/
/*
select cust_name,T2.order_num,
quantity * item_price as OrderTotal
from Customers T1
inner join Orders T2
on T1.cust_id = T2.cust_id
inner join OrderItems T3
on T2.order_num = T3.order_num
order by cust_name,T2.order_num
*/
/*
M1-defect:一些操作逻辑应该限定在最小范围的表或view中,
如:
1-乘法计算quantity * item_price 应限定在OrderItems中,
这样不仅计算逻辑操作不延伸,而且不暴露多余的field到接下来的view中。
2-对order_num的排序逻辑就该限定在Orders表中,毕竟order_nums是后排字段。
M2:子查询来将逻辑限定到特定的范围内。
*/
# 乘法逻辑
/*
select order_num,quantity * item_price as OrderTotal
from OrderItems
*/
# 外层排序逻辑,即对order_num排序
/*
select cust_id,T1.order_num
from Orders as T1
inner join
(
select order_num,quantity * item_price as OrderTotal
from OrderItems
) as T2
on T1.order_num = T2.order_num
order by T1.order_num
*/
# M2
select cust_name,T2.order_num,OrderTotal
from Customers as T1
inner join
(
select cust_id,T1.order_num,OrderTotal
from Orders as T1
inner join
(
select order_num,quantity * item_price as OrderTotal
from OrderItems
) as T2
on T1.order_num = T2.order_num
order by T1.order_num
) as T2
on T1.cust_id = T2.cust_id
order by cust_name

牛客SQL--SQL必知必会刷题记录_第26张图片
牛客SQL--SQL必知必会刷题记录_第27张图片

/*
target:查询Orders表中的cust_id和order_date字段。
当然这些记录的order_num字段必须所属prod_id为BR01的。
M1:OrderItems表和Orders表以order_num字段相连得到view。
当然要还要以prod_id=BR01来筛选,再生成的这个view。最后取出需要的字段。
注:需要根据购买日期进行升序。
*/
/*
select cust_id,order_date
from OrderItems as T1
inner join
Orders as T2
on T1.order_num = T2.order_num
where T1.prod_id = 'BR01'
order by order_date
*/
/*
M1-defect:逻辑溢出到联结表中;
M2:子查询将prod_id = BR01条件限定到OrderItems表中,且取一个只带order_num的字段暴露给其它表。
然后轻量级view驱动大表。
子查询也可把对order_date升序逻辑限定到Orders表中。
*/
# 筛选逻辑
/*
select order_num
from OrderItems
where prod_id = 'BR01'
*/
# 排序逻辑
/*
select *
from Orders
order by order_date
*/
# M2:将生成好的view连接,最后实现取字段逻辑
/*
select cust_id,order_date
from
(
select order_num
from OrderItems
where prod_id = 'BR01'
) as T1
inner join Orders as T2
on T1.order_num = T2.order_num
order by order_date
*/
# 注:MySQL做了优化,两个子查询时|两个表时,根本就不知道谁驱动了谁。
# 由于第一个子查询生成的view只有一个字段,所以用where来替代。
select cust_id,order_date
from Orders T1
where exists (
select 1 from OrderItems where prod_id = 'BR01' and T1.order_num = OrderItems.order_num
)
order by order_date

牛客SQL--SQL必知必会刷题记录_第28张图片
牛客SQL--SQL必知必会刷题记录_第29张图片
牛客SQL--SQL必知必会刷题记录_第30张图片

/*
target:返回订单总结不小于1000的客户名,订单号。然后对总额进行排序。
客户名在Customers表中,总额在OrdersItems表中,由中间表Orders表关联。
M1:三表关联生成view,计算订单额度,并进行大于等于1000筛选,最后取出客户没,订单号字段。再对总额排序。
*/
/*
select cust_name,sum(item_price * quantity) as total_price
from OrderItems as T1
inner join Orders as T2
on T1.order_num = T2.order_num
inner join Customers as T3
on T2.cust_id = T3.cust_id
group by cust_name
having total_price >= 1000
order by total_price
*/
/*
M1-defect:
1-计算逻辑和筛选逻辑没有限定到OrderItems表中,而且OrderItems表中由重复order_num。
2-Orders表可以先和Customers表连接,生成只带order_num和cust_name这样的view。
注:不知道谁驱动谁,Mysql做了优化,所以不把排序逻辑限定再OrderItems表中,再连接可能就乱了。
*/
# 计算逻辑
/*
select order_num,sum(item_price * quantity) as total_price
from OrderItems
group by order_num
having total_price >= 1000
*/
# 获取只带cust_name和order_num的view
/*
select cust_name,order_num
from Orders as T1
inner join Customers as T2
on T1.cust_id = T2.cust_id
*/
# M2:逻辑限定在各自的子查询中,然后连接view,实现取字段逻辑和排序逻辑
select cust_name,total_price
from
(
select order_num,sum(item_price * quantity) as total_price
from OrderItems
group by order_num
having total_price >= 1000
) as T1
inner join
(
select cust_name,order_num
from Orders as T1
inner join Customers as T2
on T1.cust_id = T2.cust_id
) as T2
on T1.order_num = T2.order_num
order by total_price

常见count(非null)、计数逻辑在内、排序逻辑在外、ifnull()置0

牛客SQL--SQL必知必会刷题记录_第31张图片
牛客SQL--SQL必知必会刷题记录_第32张图片

/*
target:取Vendors表中的vend_id;取Products表中以vend_id为组的产品数量。
转义:取Vendors表中的vend_id,以及该公司对应Products表中的产品有多少种。
M1:将Vendors表与Products表左连,然后以vend_id为组,取出vend_id,即产品种类数量。
注:count()聚集函数不统计为null的值。
*/
/*
select vend_id,count(prod_id) as prod_id
from Vendors left join Products using(vend_id)
group by Vendors.vend_id
order by Vendors.vend_id
*/
/*
M1-defect:计数逻辑应该限定再Products表中
M2:采用子查询先对Products表中以vend_id为组进行计数。
再right join Vendors表,最后对vend_id升序
注:应为right join 会出现none值,所以配合ifnull()函数来置0
*/
# 计数逻辑
/*
select vend_id,count(prod_id) as prod_id
from Products
group by vend_id
*/
/*
M2:Vendors left join view + 取vend_id + ifnull()置0 + order by vend_id
注:排序逻辑防止最后的view上,因为MySQL的优化,不知道谁驱动谁,所以限定再某表内,再连接可能会乱。
*/
select vend_id,ifnull(prod_id,0)
from Vendors left join
(
select vend_id,count(prod_id) as prod_id
from Products
group by vend_id
) as T 
using(vend_id)
order by vend_id

9、left、right join + union实现MySQL不能实现的outer join

牛客SQL--SQL必知必会刷题记录_第33张图片
牛客SQL--SQL必知必会刷题记录_第34张图片

/*
target:取Products表中的prod_name字段;取OrderItems表中的order_num字段。再对产品名升序。
M1:用Products表左联OrderItems表,取出Products表中的prod_name,及OrderItems表的order_num字段。
注:题目要求使用Outer join来连接,但是不肯可能有订单里的产品id在Products表中找不到,
只可能,Products的商品没有被下单,毕竟Products是主表,订单表是从表。
*/
/*
select prod_name,order_num
from Products as T1
left join OrderItems as T2
using (prod_id)
order by prod_name
*/
/*
M1-defect:没有做到题目的要求进行full outer join,但是Mysql不支持outer join。
target:MySQL如何实现outer join?
S:可以通过left join + union + right join
M2:通过left join 取Products全记录+OrderItems连接记录,
union
right join 取OrderItems没有与Products匹配上的记录,即Products.prod_id is null。
最后再对prod_name升序。
*/
# left join
/*
select prod_name,order_num
from Products left join OrderItems using(prod_id)
*/
# right join + OrderItems.prod_name is null
/*
select prod_name,order_num
from Products right join OrderItems using(prod_id) where Products.prod_name is null
*/
# M2:union view + 对prod_name升序
select prod_name,order_num
from
(
select prod_name,order_num
from Products left join OrderItems using(prod_id)
union
select prod_name,order_num
from Products right join OrderItems using(prod_id) where Products.prod_name is null
) as T
order by prod_name

牛客SQL--SQL必知必会刷题记录_第35张图片
牛客SQL--SQL必知必会刷题记录_第36张图片

/*
target:取出Products表中的prod_name字段;
取出OrderItems表中的订单数量,根据prod_name分组,
当其必须是一对一时--unique key,这里需要取prod_name字段。
最后对prod_name升序。
注:需要进行outer join,考虑到Products是主表,所以采用主表Products left join 从表OrderItems。
M1:将Products表与OrderItems根据prod_id left join相连,以prod_id分组,计算总订单数。
注:1-需要对prod_name升序;2-left join会产生none情况,直接count()聚集,它会忽略null值。
*/
/*
select prod_name,count(order_num)
from Products left join OrderItems using(prod_id)
group by prod_name
order by prod_name
*/
/*
M1-defect:
1-理论上应该以prod_id来分组,而不是prod_name。
2-计数逻辑应该限定在OrderItems表范围类。
M2:采用子查询来完成计数逻辑在OrderItems表中--生成view,再和Products表right join,完成字段的取出。
*/
# 计数逻辑
/*
select prod_id,count(order_num) as orders
from OrderItems
group by prod_id
*/
/*
M2:通过计数逻辑生成view,right join上Products表,取出prod_name和orders字段。
注:1-右连就有none,需要if来置0;2-还需对prod_name进行排序。
*/
/*
select prod_name,ifnull(orders,0)
from Products left join
(
select prod_id,count(order_num) as orders
from OrderItems
group by prod_id
) as T using(prod_id)
order by prod_name
*/
/*
M2-defect:没有按照题目考察的要求来,需要进行outer join。
MySQL如何进行outer join?
S:left join + union + right join + filed is null
M3:采用union + 再排序来解决。
*/
# left join + 子查询
/*
select prod_name,ifnull(orders,0)
from Products left join
(
select prod_id,count(order_num) as orders
from OrderItems
group by prod_id
) as T using(prod_id)
*/
# right join + field is null
/*
select prod_name,ifnull(orders,0)
from Products right join
(
select prod_id,count(order_num) as orders
from OrderItems
group by prod_id
) as T using(prod_id)
where Products.prod_name is null
*/
# M3:union + order by prod_name
select prod_name,orders
from 
(
select prod_name,ifnull(orders,0) as orders
from Products left join
(
select prod_id,count(order_num) as orders
from OrderItems
group by prod_id
) as T using(prod_id)
    
union
    
select prod_name,ifnull(orders,0) as orders
from Products right join
(
select prod_id,count(order_num) as orders
from OrderItems
group by prod_id
) as T using(prod_id)
where Products.prod_name is null
) as T
order by prod_name

10、union 生效在 order by之前

union 生效在 order by之前,order by 是对union后的view进行排序。要想order by在union 之前生效,只能子查询替代。
牛客SQL--SQL必知必会刷题记录_第37张图片

/*
M1:union + order by
*/
/*
select prod_name as name
from Products
union
select cust_name as name
from Customers
order by name
*/
/*
M1-替代
M2:将连接成的view 作为 table,然后实现取字段逻辑 和 排序逻辑
*/
select ne
from
(
    select prod_name as ne
    from Products
    union
    select cust_name as ne
    from Customers
) as T
order by ne

牛客SQL--SQL必知必会刷题记录_第38张图片

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;

你可能感兴趣的