【MySQL基础刷题】总结题型（三）

1.查询结果的质量和占比

avg大神啊…

SELECT query_name, 
ROUND(avg(rating / position), 2) as quality, 
ROUND(avg(rating < 3)*100, 2) as poor_query_percentage 
FROM Queries 
WHERE query_name is not null
GROUP BY query_name;

2.每月交易I

其中DATE_FORMAT是一个日期格式化函数，用于将日期按照指定的格式转换为字符串输出

# Write your MySQL query statement below
select
    DATE_FORMAT(trans_date, '%Y-%m') as month,
    country,
    count(*) as trans_count,
    count(case when state = 'approved' then 1 end) as approved_count,
    sum(amount) as trans_total_amount,
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from
    Transactions
group by
    date_format(trans_date, '%y-%m'), country;

其中count(case when state = 'approved' then 1 end)，不可以添加else 0，因为count应该只统计非NULL的条目。

3.销售分析III

商品需要只在春季销售！

2号商品在春季和夏季都销售过，所以不能算

# Write your MySQL query statement below
select p.product_id as product_id, product_name
from Product p join Sales s on p.product_id = s.product_id
group by p.product_id
having sum(case when sale_date between '2019-01-01' and '2019-03-31' then 0 else 1 end) = 0;

4.只出现一次的最大数字

出现频率为1，且只出现一次
这题很简单，放进来主要是学习库函数COALESCE

COALESCE 是一个用于返回第一个非 NULL 值的 SQL 函数。在参数列表中，它会依次检查每个参数的值，返回第一个非 NULL 的值。如果所有参数都是 NULL，则返回 NULL。
COALESCE(value1, value2, ..., valueN)

• value1, value2, …, valueN 是一组表达式或列，COALESCE 会按顺序检查这些值，返回第一个非 NULL 的值。
• 如果所有参数都是 NULL，则返回 NULL。

# Write your MySQL query statement below
SELECT COALESCE(
    (SELECT num
     FROM MyNumbers
     GROUP BY num
     HAVING COUNT(*) = 1
     ORDER BY num DESC
     LIMIT 1),
    NULL
) AS num;

5.买下所有产品的客户

报告 Customer 表中购买了 Product 表中所有产品的客户的 id。

返回结果表 无顺序要求 。

select distinct customer_id
from Customer
group by customer_id
having count(distinct product_key) = (select count(*) from Product)

6.员工的直属部门

一个员工可以属于多个部门。当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为’N’.

注意当条件之一无法直接使用时的写法

SELECT employee_id, department_id
FROM Employee
WHERE primary_flag = 'Y'
   OR employee_id IN (
       SELECT employee_id
       FROM Employee
       GROUP BY employee_id
       HAVING COUNT(*) = 1
   );

7.指定日期的产品价格

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

本题的关键点在找到 2019-08-16 前所有有改动的产品及其最新价格和没有没有修改过价格的产品。

我们可以先找到所有的产品，再找到所有 2019-08-16 前有修改的产品和他们最新的价格，使用 left join 将两个查询联合。如果产品没有价格，说明没有修改过，设置为 10，如果有价格，设置为最新的价格。

IFNULL(x1, x2) ：如果 x1 为 NULL， 返回 x2，否则返回 x1。

1. 找出所有产品：

select distinct product_id 
from products

2. 找到2019-08-16前所有有改动的产品的最新价格

先使用max函数找到产品最新修改时间。实用where限制时间小于等于2019-08-16

select product_id, max(change_date)
from products
where change_date <= '2019-08-16'
group by product_id

再使用where查询，得到最新的价格

select product_id, new_price 
from products
where (product_id, change_date) in (
    select product_id, max(change_date)
    from products
    where change_date <= '2019-08-16'
    group by product_id
)

代码逻辑汇总

select p1.product_id, ifnull(p2.new_price, 10) as price
from (
    select distinct product_id
    from products
) as p1 -- 所有的产品
left join (
    select product_id, new_price 
    from products
    where (product_id, change_date) in (
        select product_id, max(change_date)
        from products
        where change_date <= '2019-08-16'
        group by product_id
    )
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id