每日刷力扣SQL题(六)

1978.上级经理已离职的公司员工

select employee_id 
from Employees
where salary < 30000 and manager_id not in (select distinct employee_id from Employees
)
order by employee_id ASC

626 换座位

 在 SQL 中实现每两行交换数据可以通过使用窗口函数和自连接来完成。

# Write your MySQL query statement below
With RankedValues AS
(select id , student,ROW_NUMBER() OVER (order by id) AS rn from Seat 
),
SwappedValues AS
(select CASE when rn%2 = 1 then id + 1ELSE id -1 END AS new_id ,studentfrom RankedValues
)
selectROW_NUMBER() over(order by new_id) as id,student
from SwappedValues
order by id ASC

解释

1. RankedValues CTE: 使用 ROW_NUMBER() 函数为每一行分配一个序号 rn，以便我们可以知道每行的位置。

2. SwappedValues CTE: 根据行号 rn 的奇偶性，决定新的 ID。奇数行的 ID 加 1，偶数行的 ID 减 1。

3. 最终选择: 再次使用 ROW_NUMBER() 来生成新 ID，并按新 ID 排序，得到交换后的结果。

   注意事项

4. 此方法假设表的行数是偶数。如果是奇数，最后一行将保持不变。
5. 确保在实际使用中对数据的完整性和约束条件进行处理。

其他解法：使用CASE

select ( CASE WHEN id % 2 = 0 THEN (id - 1)WHEN id % 2 = 1 AND id < (select MAX(ID) from Seat) THEN id + 1ELSE idEND
) id, student from Seat order by id

SELECT(CASEWHEN MOD(id, 2) != 0 AND counts != id THEN id + 1WHEN MOD(id, 2) != 0 AND counts = id THEN idELSE id - 1END) AS id,student
FROMseat,(SELECTCOUNT(*) AS countsFROMseat) AS seat_counts
ORDER BY id ASC;

1341.电影评分 

使用UNION ALL 来联合所有查询 并且使用DATE_FORMAT来格式化日期

# Write your MySQL query statement below
(select u.name as results
from Users u join MovieRating mr on u.user_id = mr.user_id
group by u.user_id
order by count(*) DESC,name ASC
limit 1
)
UNION ALL
(select m.title as resultsfrom Movies m  left join MovieRating mr on m.movie_id = mr.movie_id and DATE_FORMAT(mr.created_at ,'%Y-%m') = '2020-02'group by mr.movie_id order by AVG(mr.rating) DESC,title limit 1 
)