sql学习二

一、 计算函数

1. datediff

datediff(日期1, 日期2)： 得到的结果是日期1与日期2相差的天数。 如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负

举例1：上升的温度

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。返回结果 不要求顺序 。

select w2.id
from Weather w1, Weather w2
where datediff(w1.recordDate, w2.recordDate) = -1 and w1.Temperature < w2.Temperature

举例2：查询近30天活跃用户数

请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。以 任意顺序 返回结果表。

select activity_date day, count(distinct user_id) as active_users
from Activity
# where activity_date between '2019-6-28' and '2019-7-27'
where 0 <= datediff('2019-7-27', activity_date) and datediff('2019-7-27', activity_date) < 30
group by activity_date;

2. all

编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。

/*
select customer_number
from Orders
group by customer_number
having count(order_number) >= all(
    select count(order_number)
    from Orders
    group by customer_number
);
*/
select customer_number
from Orders
group by customer_number
having count(order_number) = (
    select count(order_number)
    from Orders
    group by customer_number
    order by count(order_number) desc
    limit 0,1
);

3. year

编写一个 SQL 查询，该查询可以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。返回的结果集可以按 任意顺序 排列。

select user_id, max(time_stamp) as last_stamp
from Logins
where year(time_stamp) = 2020
group by user_id;

4. sum

写一段 SQL , 报告每个用户的旅行距离。返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

# Write your MySQL query statement below
select name, ifnull(sum(distance),0) as travelled_distance
from Users
left join Rides on Users.id = Rides.user_id
group by Rides.user_id
order by travelled_distance desc, name asc;

二、控制流

示例1：股票的资本损益

编写一个SQL查询来报告每支股票的资本损益，股票的资本损益是一次或多次买卖股票后的全部收益或损失，以任意顺序返回结果即可。

select stock_name,sum(
    case
        when operation = 'Buy' then -price
    else price
    end
) as capital_gain_loss
from Stocks
group by stock_name;

三、过滤 group by + having

示例1：查找重复的电子邮箱

select Email
from Person
group by Email
having count(Email) > 1;

示例2：合作过至少三次的演员和导演

写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

select actor_id, director_id
from ActorDirector
group by actor_id, director_id
having count(*) >= 3;

示例3：销售分析

编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品，以 任意顺序 返回结果表。

select Product.product_id, product_name
from Product, Sales
where Product.product_id = Sales.product_id
group by Product.product_id
having sum(sale_date < '2019-01-01') = 0 and sum(sale_date > '2019-03-31') = 0;