十题左右，便于复习
- 1.查询结果的质量和占比
- 2.每月交易I
- 3.销售分析III
- 4.只出现一次的最大数字
- 5.买下所有产品的客户
- 6.员工的直属部门
- 7.指定日期的产品价格
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。
- 找出所有产品：
select distinct product_id
from products
- 找到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
