【SQL练习】面试之查询篇

 这几天面试发现SQL快忘记了，重新复习吧

183 从不订购的客户

# 写法1
# Write your MySQL query statement below
select Name as 'Customers'
from Customers
where Id not in(select CustomerId from Orders);

# 写法2
select Name as Customers
from Customers a left join Orders b
on a.id = b.CustomerId
where b.id is null;

• 总结：注意join的使用，join总结可以见该博客。

1873 计算特殊奖金

# 写法1
# Write your MySQL query statement below
select employee_id, if((employee_id % 2 = 1 and name not like 'M%'), salary, 0) bonus
from Employees
order by employee_id 

# 写法2
# Write your MySQL query statement below
select employee_id, if((employee_id % 2 = 1 and left(name,1)!='M'), salary, 0) bonus
from Employees
order by employee_id

总结

• left的使用，left(str,len)表示从str最左边一个字符开始返回指定数目(len)的字符。若len的值大于 str 的长度,则返回字符表达式的全部字符str。如果 len为负值或 0,则返回空字符串。

1398 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

# Write your MySQL query statement below
select c.customer_id, c.customer_name 
from Customers c left join Orders o
on c.customer_id = o.customer_id
group by customer_id
having sum(product_name='A') > 0 and sum(product_name='B') > 0 and sum(product_name='C') = 0

总结

• 该题我总容易写出where product_name='A'，但这无法做到三个条件查询，我们应该考虑先分组；然后根据分组后的product_name的数量来过滤不符合条件的用户。
• 一个用户可以重复买多个同类产品，因此A，B产品数量应大于0，而不应该存在C，因此C产品的数量为0。
• 该题目中用到sum函数里的按条件查询，如下。
  • sum函数中使用if判断条件格式为：sum(if(条件,列值,0))
    • sum(if(order_type = 0, real_price, 0))
  • 使用case when：sum(case when a > 0 then a else 0 end )

拓展

 count函数中使用if判断条件格式为：
 1.统计总数，count(if(条件字段名=值,true,null))
 2.统计总数去重复值，count(DISTINCT 需要计算count的字段名,if(条件字段名=值,true,null))

SELECT
    date(create_time) AS '当天日期',
    sum(real_price) AS '当天总收入',
    sum(IF (order_type = 0, real_price, 0)) AS '当天支付收入',
    sum(IF (order_type = 1, real_price, 0)) AS '当天打赏收入',
    count(DISTINCT open_id) AS '付费总人数',
    count(DISTINCT open_id,IF (order_type = 0, TRUE, NULL)) AS '支付人数',
    count(DISTINCT open_id,IF (order_type = 1, TRUE, NULL)) AS '打赏人数',
    count(id) AS '付费订单总数',
　　count(DISTINCT id,IF (order_type = 0, TRUE, NULL)) AS '支付订单数',
　　count(DISTINCT id,IF (order_type = 1, TRUE, NULL)) AS '打赏订单数'
FROM
orders
WHERE
    'real_price' != 1
    AND 'status' != 0
GROUP BY DATE(create_time)

1112 每位学生的最高成绩

# 写法1
# Write your MySQL query statement below
select student_id, min(course_id) course_id, grade
from Enrollments 
where (student_id, grade) in (
    select student_id, max(grade)
    from Enrollments
    group by student_id
)
group by student_id
order by student_id

# 写法2
# Write your MySQL query statement below
select student_id, course_id, grade
from (
    select student_id, course_id, grade, 
    rank() over(partition by student_id order by grade desc, course_id) as rank
    from Enrollments
) as t
where rank = 1

总结

• 在该题中用到了窗口函数rank，rank窗口函数用法如下，我们使用的是rank() over(partition by student_id order by grade desc, course_id)。

拓展

1. 窗口函数的应用

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

2. 窗口函数的语法

<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum, avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

3. 专用窗口函数rank, dense_rank, row_number的区别？

举个例子

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

得到结果

从上面的结果可以看出：

1. rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

2. dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

3. row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

参考

1. SQL窗口函数