sql基础day06

以下题目均来自leetcode官网
本文仅用于记录学习进度

1、 1164. 指定日期的产品价格

#写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。
# 以 任意顺序 返回结果表。
#分成2部分。把第一次change_date在2019-08-16之后的单独分出来，这部分的price肯定是10，因为初始值是10。
#剩下的作为另外一部分，然后两部分做union。 另外一部分中，选出在2019-08-16之前最大的那天的price作为最终price就行了。
select product_id, '10' as new_price, change_date
from products
group by product_id
having min(change_date) > '2019-08-16'
union all
select *
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
)
;

2 603. 连续空余座位

#编写一个SQL查询来报告电影院所有连续可用的座位
#返回按 seat_id 升序排序 的结果表。
#思路：要找连续的三个数，那么找第二个数 他的id 和 前一个或者后一个数的 id 的差绝对值为 1
#根据此思路 可以将表自连接 以id差的绝对值为连接条件，并且free = 1
select distinct(c1.seat_id)
from cinema c1
         join cinema c2
              on abs(c2.seat_id - c1.seat_id) = 1
where c1.free = 1
  and c2.free = 1
order by c1.seat_id

3、1731 每位经理下属员工的数量

# 1731. 每位经理的下属员工数量
# 编写SQL查询需要听取汇报的所有经理的ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。
# 返回的结果集需要按照 employee_id 进行排序。
select e1.reports_to employee_id, e2.name name, count(*) reports_count, round(avg(e1.age), 0) average_age
from employees e1,
     employees e2
where e1.reports_to = e2.employee_id
group by e1.reports_to
order by employee_id

4、1747. 应该被禁止的 Leetflex 账户

# 编写一个SQL查询语句，查找那些应该被禁止的Leetflex帐户编号 account_id 。
# 如果某个帐户在某一时刻从两个不同的网络地址登录了，则这个帐户应该被禁止
# 思路 ：如果 同一用户的不同ip 出现了login 或者logout 在 另外一条的 login和logout之间的情况
select distinct l1.account_id
from loginfo l1,
     loginfo l2
where l1.account_id = l2.account_id and l1.ip_address != l2.ip_address
    and (l1.login between l2.login and l2.logout
   or l1.logout between l2.login and l2.logout)

5、181. 超过经理收入的员工

select e2.name
from employee e1 ,employee e2 
where e1.id = e2.managerId and e2.salary > e1.salary;

6、1459、矩形面积

# 写一个 SQL 语句，报告由表中任意两点可以形成的所有 边与坐标轴平行 且 面积不为零 的矩形。
# 结果表中的每一行包含三列 (p1, p2, area) 如下:
# p1 和 p2 是矩形两个对角的 id
# 矩形的面积由列 area 表示
# 请按照面积 area 大小降序排列；如果面积相同的话, 则按照 p1 升序排序；若仍相同，则按 p2 升序排列。

# 思路：只要两点的 x 值 并且 y 值 不相等就能构成 矩形，面积等于 x的差 * y的差
select p1.id                                                       p1,
       p2.id                                                       p2,
       abs(p1.x_value - p2.x_value) * abs(p1.y_value - p2.y_value) area
from Points p1,
     Points p2
where p1.id < p2.id #重点：如果没有这个条件那么就会有多个面积相同的数据，并且无法去重
  and p1.x_value != p2.x_value
  and p1.y_value != p2.y_value
order by 3 desc, 1, 2;

7、180、连续出现的数字

做到第603题后再回来做这道题有的思路： 连续三条一样，那么我们就去找出第二条的连接条件，然后根据id分组 找出大于等于两条的即可

select distinct l1.num ConsecutiveNums
from logs l1 left join logs l2
on l1.num = l2.num and abs(l1.id-l2.id) = 1
group by l1.id
having count(l1.id) >= 2