宁夏固原建设网站,做电子画册的网站,杭州建设行业网站,wordpress上传思源字体文章目录 主要内容一.SQL练习题1.1789-员工的直属部门代码如下#xff08;示例#xff09;: 2.610-判断三角形代码如下#xff08;示例#xff09;: 3.180-连续出现的数字代码如下#xff08;示例#xff09;: 4.1164-指定日期的产品价格代码如下#xff08;示例#x… 文章目录 主要内容一.SQL练习题1.1789-员工的直属部门代码如下示例: 2.610-判断三角形代码如下示例: 3.180-连续出现的数字代码如下示例: 4.1164-指定日期的产品价格代码如下示例: 5.1204-最后一个能进入巴士的人代码如下示例: 6.1907-按分类统计薪水代码如下示例: 7.1978-上级经理已离职的公司员工代码如下示例: 8.626-换座位代码如下示例: 9.1341-电影评分代码如下示例: 10.1321-餐馆营业额变化增长代码如下示例: 总结 主要内容
LeetCode-高频SQL50题 31-40
一.SQL练习题 1.1789-员工的直属部门 代码如下示例:
# Write your MySQL query statement below
select employee_id,department_id
from employee
where primary_flag Y
group by 1
union
select employee_id,department_id
from employee
group by 1
having count(*)1;2.610-判断三角形 代码如下示例:
# Write your MySQL query statement below
select x,y,z,if(xyz 2* greatest(x,y,z),Yes,No) as triangle
from triangle;3.180-连续出现的数字 代码如下示例:
# Write your MySQL query statement below
select distinct num as ConsecutiveNums
from logs
where (id1,num) in (select * from logs)
and (id2,num) in (select * from logs);4.1164-指定日期的产品价格 代码如下示例:
# Write your MySQL query statement below
select product_id,new_price as price
from products
where (product_id,change_date) in(select product_id,max(change_date) as max_datefrom productswhere change_date 2019-08-16group by product_id)
union
select product_id,10 as price
from products
group by product_id
having min(change_date) 2019-08-16
order by product_id;
5.1204-最后一个能进入巴士的人 代码如下示例:
# Write your MySQL query statement below
select a.person_name
from queue a,queue b
where a.turn b.turn
group by a.person_id
having sum(b.weight) 1000
order by a.turn desc
limit 16.1907-按分类统计薪水 代码如下示例:
# Write your MySQL query statement below
SELECT Low Salary AS category,SUM(CASE WHEN income 20000 THEN 1 ELSE 0 END) AS accounts_count
FROM AccountsUNION
SELECT Average Salary category,SUM(CASE WHEN income 20000 AND income 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM AccountsUNION
SELECT High Salary category,SUM(CASE WHEN income 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM Accounts
7.1978-上级经理已离职的公司员工 代码如下示例:
# Write your MySQL query statement below
select e1.employee_id
from employees e1
left join employees e2
on e1.manager_id e2.employee_id
where e1.manager_id is not null
and e2.employee_id is null
and e1.salary 30000
order by e1.employee_id;8.626-换座位 代码如下示例:
# Write your MySQL query statement below
select (case when mod(id,2) 1 and id (select count(*) from seat ) then idwhen mod(id,2) 1 then id1else id-1end)as id,student
from seat
order by id;法2SELECT(CASEWHEN MOD(id, 2) ! 0 AND counts ! id THEN id 1WHEN MOD(id, 2) ! 0 AND counts id THEN idELSE id - 1END) AS id,studentFROMseat,(SELECTCOUNT(*) AS countsFROMseat) AS seat_countsORDER BY id ASC;
9.1341-电影评分 代码如下示例:
# Write your MySQL query statement below
(#评论电影数量最多且字典序较小的用户名select users.name as resultsfrom MovieRatingjoin users on MovieRating.user_id users.user_idgroup by MovieRating.user_idorder by count(MovieRating.user_id) desc,users.namelimit 1
)
union all (#2020年2月份平均评分最高且字典序较小的电影名select Movies.title as resultsfrom MovieRatingjoin Movies on MovieRating.movie_id Movies.movie_idwhere MovieRating.created_at 2020-02-01and MovieRating.created_at 2020-03-01group by MovieRating.movie_idorder by avg(MovieRating.rating) desc,Movies.titlelimit 1
)10.1321-餐馆营业额变化增长 代码如下示例:
# Write your MySQL query statement below
select distinct visited_on,sum_amount as amount,round(sum_amount/7,2) as average_amount
from (select visited_on,sum(amount) over (order by visited_on rows 6 preceding) as sum_amountfrom (select visited_on,sum(amount) as amountfrom customergroup by visited_on) TT
) LL
where datediff(visited_on,(select min(visited_on) from customer)) 6;总结
以上是今天要讲的内容练习了一些高频SQL题。
