高频 SQL 50 题（基础版）

SELECT
    name, population, area
FROM
    world
WHERE
    area >= 3000000 OR population >= 25000000
;

SELECT
    name, population, area
FROM
    world
WHERE
    area >= 3000000

UNION

SELECT
    name, population, area
FROM
    world
WHERE
    population >= 25000000
;

# Write your MySQL query statement below
select 
distinct author_id as id   
from Views where author_id = viewer_id 
order by author_id

select 
 p.product_name,
 s.year,
 s.price
from Sales s 
left join Product p
on s.product_id=p.product_id

SELECT customer_id, count(customer_id) count_no_trans
FROM Visits v
LEFT JOIN transactions t 
ON v.visit_id = t.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id;

SELECT customer_id,count(visit_id) as count_no_trans
FROM Visits
WHERE visit_id not in (SELECT DISTINCT visit_id FROM Transactions)
GROUP BY customer_id

select id
from
    (select 
        id,
        temperature,
        recordDate,
        lag(recordDate,1) over(order by recordDate) as last_date,
        lag(temperature,1) over(order by recordDate) as last_temperature
    from Weather) a
where temperature > last_temperature and datediff(recordDate, last_date) = 1

select 
    b.Id 
from 
    weather a 
inner join 
    weather b 
where 
    DATEDIFF(b.recordDate,a.recordDate)=1 
and b.Temperature > a.Temperature;

select w1.Id
from Weather as w1, Weather as w2
where TIMESTAMPDIFF(DAY, w2.RecordDate, w1.RecordDate) = 1 
AND w1.Temperature > w2.Temperature

select a.id 
    from weather a join weather b 
    on (a.recorddate = adddate(b.recorddate,INTERVAL 1 day))
where a.temperature > b.temperature

select 
    w.Id
from Weather w
join (
    select 
        RecordDate,Temperature
    from 
        Weather
) t1
on w.RecordDate = DATE_ADD(t1.RecordDate,INTERVAL 1 day)
where w.Temperature > t1.Temperature;

select
    machine_id,
    round(avg(if(activity_type='start', -timestamp, timestamp))*2, 3) processing_time 
from Activity 
group by machine_id

# Write your MySQL query statement below

select 
 machine_id
,round(sum((case when activity_type ='end' then timestamp else 0 end) - (case when activity_type ='start' then timestamp else 0 end))/count(distinct process_id ),3) as processing_time  
from Activity 
group by machine_id

# Write your MySQL query statement below
select
name,bonus
from Employee e left join Bonus b using(empId)
where bonus < 1000 or bonus is null;

# Write your MySQL query statement below
with aa as (
    select * from Students,Subjects
)
select
 aa.student_id
,aa.student_name
,aa.subject_name
,ifnull(count(s.subject_name),0) as attended_exams
from aa 
left join Examinations s using(student_id,subject_name)
group by
 aa.student_id
,aa.student_name
,aa.subject_name
order by  aa.student_id,aa.student_name

# 自连接
with cte1 as
(
    select *
    from students,subjects
)


select 
    a.student_id,
    a.student_name,
    a.subject_name,
    ifnull(count(b.subject_name),0) as attended_exams 
from cte1 a
left join Examinations b
using(student_id,subject_name)
group by 1,2,3
order by 1,2,3

作者：zg104
链接：https://leetcode.cn/problems/students-and-examinations/solution/by-zg104-0sww/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

SELECT
    Name
FROM
    Employee AS t1 JOIN
    (SELECT
        ManagerId
    FROM
        Employee
    GROUP BY ManagerId
    HAVING COUNT(ManagerId) >= 5) AS t2
    ON t1.Id = t2.ManagerId
;

select Name 
from Employee
where Id in
(
    select distinct ManagerId
    from Employee
    group by ManagerID
    having count(ManagerID)>=5
)

# Write your MySQL query statement below


select 
s.user_id
,round(
    sum(if(c.action = 'confirmed',1,0)) /
    count(s.user_id)
    ,2) as confirmation_rate
from Signups s left join  Confirmations c using(user_id)
group by 1

select *
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating DESC
;

# Write your MySQL query statement below
select 
id,movie,description,rating
from cinema
where description != 'boring'
and (id%2) = 1
order by rating desc;

select
 p.product_id
,round((sum(units*price)/sum(units)),2) as average_price
from Prices p left join UnitsSold u on p.product_id = u.product_id
    and purchase_date >= start_date 
    and purchase_date <= end_date
group by p.product_id

# Write your MySQL query statement below
select p.project_id, ROUND(AVG(e.experience_years), 2) as average_years 
from Project p, Employee e 
where p.employee_id = e.employee_id 
group by p.project_id;

# Write your MySQL query statement below
select 
    project_id
    ,round((sum(experience_years)/count(p.employee_id)),2) as average_years
from Project p
left join Employee e using(employee_id)
group by project_id

# Write your MySQL query statement below
select
contest_id
,round((100*count(user_id)/(select count(1) from Users))
    ,2) as percentage
from Register
group by contest_id
order by 2 desc,1 asc

# Write your MySQL query statement below

select 
query_name
,round((avg(rating/position)),2) as quality
,round((sum(if(rating < 3,1,0 ))/count(*)) *100,2) as poor_query_percentage
from Queries
group by query_name

# Write your MySQL query statement below
select 
date_format(trans_date,'%Y-%m') as month
,country
,count(id) as trans_count
,sum(if(state='approved',1,0)) as approved_count
,sum(amount) as trans_total_amount
,sum(if(state='approved',amount,0)) as approved_total_amount
from Transactions
group by date_format(trans_date,'%Y-%m'),country

# Write your MySQL query statement below
select 
substr(trans_date,1,7) as month
,country
,count(id) as trans_count
,sum(if(state='approved',1,0)) as approved_count
,sum(amount) as trans_total_amount
,sum(if(state='approved',amount,0)) as approved_total_amount
from Transactions
group by substr(trans_date,1,7),country

# Write your MySQL query statement below

select
    round(sum(if(order_date=customer_pref_delivery_date,1,0))/
    count(*)
    *100,2) as immediate_percentage
from Delivery where (customer_id,order_date) in (
    select customer_id,min(order_date) as order_date from Delivery 
    group by customer_id
)

select
ifnull(round(
    count(distinct player_id)/
    (select count(distinct player_id) from Activity)
    ,2)
    ,0) as fraction
from Activity
where (player_id,event_date) in 
(
    select player_id,date(min(event_date)+1) from Activity
    group by player_id
)

select 
    round(
        sum(if((datediff(a.event_date,b.first_date)=1),1,0)
        )/(select count(distinct player_id) from Activity)
        ,2
    ) as fraction
from Activity a , (
    select player_id,min(event_date) as first_date from Activity 
    group by player_id
) b where a.player_id = b.player_id

select 
round(avg(b.event_date is not null),2) as fraction
from (select 
player_id,min(event_date) as first_date
from Activity 
group by player_id) a  left join Activity b 
on a.player_id=b.player_id and datediff(b.event_date,a.first_date)=1;

# Write your MySQL query statement below
select 
teacher_id
,count(distinct subject_id) as cnt
from Teacher
group by 1

# Write your MySQL query statement below
select 
activity_date as day
,count(distinct user_id) as active_users
from 
Activity
where datediff('2019-07-27',activity_date) < 30 and activity_date<='2019-07-27'
group by activity_date

# Write your MySQL query statement below
select activity_date day, count(distinct user_id) active_users
from Activity
where date_add(activity_date, interval 29 day)>='2019-07-27' and activity_date<='2019-07-27'
group by activity_date;

# Write your MySQL query statement below

# 其实题目要求“仅在2019-01-01至2019-03-31之间出售的商品”翻译过来就是“所有售出日期都在这个时间内”，也就是“在这个时间内售出的商品数量等于总商品数量”，这样就不难写出这个语句


select 
distinct 
 s.product_id
,p.product_name
from Sales s inner join Product p on s.product_id = p.product_id
and s.product_id not in (
    select  product_id from Sales where sale_date not between '2019-01-01'  and '2019-03-31'
)
;

# Write your MySQL query statement below

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

# Write your MySQL query statement below
select class
from (select 
class
,count(distinct student) as num
from Courses
group by class) as class
where num >=5

# Write your MySQL query statement below
select 
class
from Courses
group by class
having count(distinct student) >= 5

# Write your MySQL query statement below
select 
user_id
,count(distinct follower_id) as followers_count
from Followers
group by user_id

# Write your MySQL query statement below
select
max(num) as num 
from (select 
num as num
from MyNumbers
group by num
having count(num) =1) a

select 
customer_id
from Customer c
group by  customer_id 
having count(distinct product_key) =
(select count(distinct product_key) from Product)

select 
    t2.employee_id,
    t2.name,
    count(1) as reports_count,
    round(avg(t1.age)) as average_age
from Employees t1, Employees t2
where t1.reports_to = t2.employee_id
group by 1 
order by 1

# Write your MySQL query statement below
SELECT employee_id AS 'employee_id', 
    department_id AS 'department_id'
FROM Employee 
WHERE primary_flag = 'Y'
UNION               #联合查询，自动去重
SELECT employee_id AS 'employee_id', 
    department_id AS 'department_id'
FROM Employee
GROUP BY employee_id
HAVING COUNT(department_id) = 1
;

# Write your MySQL query statement below

SELECT
    employee_id
    ,IF(
        COUNT(department_id)=1
        ,department_id
        ,MAX(
            IF(primary_flag="Y",department_id,NULL)
        )
    ) AS department_id
FROM
    Employee
GROUP BY
    employee_id

/* Write your T-SQL query statement below */
select employee_id, department_id from
(
    select 
        *,
        count(*) over(partition by employee_id) as cnt
    from Employee
) a 
where primary_flag='Y' or cnt=1

# Write your MySQL query statement below
select 
employee_id,department_id
from Employee
where primary_flag ='Y'
or employee_id  in (
select distinct employee_id from Employee 
group by employee_id
having count(employee_id) = 1
)

 

# Write your MySQL query statement below
select 
employee_id,department_id
from Employee
where primary_flag ='Y'
or employee_id not in (
select distinct employee_id from Employee 
group by employee_id
having count(employee_id) >1
)

 

# Write your MySQL query statement below
select
x,y,z
,case when 
    x+y >z and x+z >y and y+z >x then 'Yes'
else 'No' end as triangle

from Triangle

select
    x,y,z,if(x+y>z and x+z>y and y+z >x,'Yes','No') as triangle
from Triangle

# Write your MySQL query statement below
select

distinct a.Num ConsecutiveNums
from Logs a,Logs b,Logs c
where a.id = b.id -1 and b.id = c.id -1
and a.Num = b.Num and b.Num = c.Num

select 
    distinct t.num as ConsecutiveNums 
from
(
    select
        num,
        lag(num, 1) over(order by id) as num1,
        lag(num, 2) over(order by id) as num2
    from Logs 
) t
where t.num = t.num1 and t.num1 = t.num2
;

select 
    distinct t.num as ConsecutiveNums 
from
(
    select
        num, 
        lag(num, 1, null) over(order by id) as lag_num, 
        lead(num, 1, null) over(order by id) as lead_num
    from Logs
) t
where t.num = t.lag_num and t.num = t.lead_num
;

select
    distinct t.num as ConsecutiveNums 
from
(
    select 
        id,
        num,
        row_number() over(order by id) as rn,
        row_number() over(partition by num order by id) as id_rn
    from Logs 
) t
group by t.num, (t.rn - t.id_rn)
having count(1) >= 3
;

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

分成2部分。把第一次change_date在2019-08-16之后的单独分出来，这部分的price肯定是10，因为初始值是10。
剩下的作为另外一部分，然后两部分做union。 另外一部分中，选出在2019-08-16之前最大的那天的price作为最终price就行了。

(select product_id as product_id,10 as price 
from Products 
group by product_id
having min(change_date)>'2019-08-16')
union
(select product_id,new_price as price 
from Products 
where (product_id,change_date) in
                                (select product_id,max(change_date) as max_date
                                 from Products
                                 where change_date<='2019-08-16'
                                 group by product_id))

# Write your MySQL query statement below
select 
person_name
from (select 
 turn
,person_name
,sum(weight) over(order by turn) as total
from Queue) a where total <= 1000 order by turn desc limit 1

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 1

with aa as (
    SELECT 'Low Salary' as category
    union all
    SELECT 'Average Salary' as category
    union all
    SELECT 'High Salary' as category
),bb as (SELECT
case when income < 20000 then 'Low Salary'
     when income >= 20000  and income <= 50000 then 'Average Salary'   
     when income > 50000  then 'High Salary'  
     end as  category
     ,count(account_id) as accounts_count
from  Accounts b

group by 
case when income < 20000 then 'Low Salary'
     when income >= 20000  and income <= 50000 then 'Average Salary'   
     when income > 50000  then 'High Salary'  
     end
     ) 
     select aa.category,ifnull(bb.accounts_count,0) as accounts_count   from aa left join bb using(category)

## 拼接临时表

select 
    a.category
    ,ifnull(b.cnt,0) as accounts_count
from (select 
    'Low Salary' as category
union all
select 
    'Average Salary' as category
union all
select 
    'High Salary' as category) a left join (
        select 
        case when income < 20000 then 'Low Salary'
              when income > 50000 then 'High Salary'
         else 'Average Salary'
             end as category
        ,count(1) as cnt
        from Accounts
        group by case when income < 20000 then 'Low Salary'
              when income > 50000 then 'High Salary'
         else 'Average Salary'
             end
    ) b
    on a.category = b.category

SELECT
    s1.id, COALESCE(s2.student, s1.student) AS student
FROM
    seat s1
        LEFT JOIN
    seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;

# 1.查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
    select 
    a1.name as results
    from (    select 
    u.name,count(user_id) as num
    from MovieRating m left join Users u using(user_id)
    group by u.name
    order by count(user_id) desc,u.name asc limit 1) a1
    union all

## 2.查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称
    select 
    a2.title as results
    from (    select 
    title,avg(rating) as avg_num
    from MovieRating m left join Movies mo using(movie_id)
    where date_format(created_at,'%Y-%m') = '2020-02'
    group by title
    order by avg(rating) desc,title asc limit 1) a2

## union all 拼接
select results from (select 
     name as results
from MovieRating left join Users using(user_id)
group by name
order by count(movie_id) desc,name limit 1) a

union all select results from (
    select 
     title as results
from MovieRating left join Movies using(movie_id)
where year(created_at) = '2020' and month(created_at) = '02'
group by title 
order by avg(rating) desc,title limit 1
) b

不使用窗口（自连接） 注意细节！ ①取distinct（日期）：小细节，因为一天有多次消费 ②自连接的条件：0<=datediff(被减数，减数）<=6 ；也可以这样写”between 0 and 6“ ③group by （distinct日期） ④求平均时不能使用avg！因为有的天有多笔消费
select
distinct a.visited_on,sum(b.amount) as amount,round(sum(b.amount)/7,2) as average_amount
from Customer a join Customer b 
on datediff(a.visited_on,b.visited_on) between 0 and 6
group by a.visited_on,a.customer_id
having count(distinct b.visited_on) >=7
order by a.visited_on;

# Write your MySQL query statement below
SELECT
 a.visited_on,
 sum( b.amount ) AS amount,
 round(sum( b.amount ) / 7, 2 ) AS average_amount 
FROM
 ( SELECT DISTINCT visited_on FROM customer ) a JOIN customer b 
  ON datediff( a.visited_on, b.visited_on ) BETWEEN 0 AND 6 
WHERE
 a.visited_on >= (SELECT min( visited_on ) FROM customer ) + 6 
GROUP BY
 a.visited_on

select 
 a2.visited_on,a2.amount,a2.average_amount
from (
select 
     visited_on
     ,row_number() over(order by visited_on) as rn 
    ,sum(amount) over(order by visited_on rows 6 preceding) as amount 
    ,round((avg(amount) over(order by visited_on rows 6 preceding) ),2) as average_amount
from (select
visited_on,sum(amount) as amount
from Customer 
group by visited_on
order by visited_on) a1
) a2 where a2.rn >=7

select 
 distinct a2.visited_on,a2.amount,a2.average_amount
from (
select 
     visited_on
    ,sum(amount) over(order by visited_on rows 6 preceding) as amount 
    ,round((avg(amount) over(order by visited_on rows 6 preceding) ),2) as average_amount
from (select
visited_on,sum(amount) as amount
from Customer 
group by visited_on
order by visited_on) a1
) a2 where datediff(a2.visited_on,(select min(visited_on) from Customer)) >= 6

select 
aa.id,count(aa.id) as num
from (select
requester_id as id
from RequestAccepted a 
union all
select
accepter_id as id
from RequestAccepted b ) aa
group by id order by num desc limit 1

## 1. 他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同

select 
round(sum(TIV_2016),2) as TIV_2016
from insurance where TIV_2015 in (select
TIV_2015
from insurance 
group by TIV_2015
having count(TIV_2015) >1
)
and CONCAT(LAT, LON) in (
    select
CONCAT(LAT, LON)
from insurance 
group by LAT, LON
having count(*) = 1
)

select 
    round(sum(TIV_2016),2) as TIV_2016
from insurance where TIV_2015 in (
    select 
distinct TIV_2015
from insurance
group by TIV_2015 having count(TIV_2015) >1
)
and concat(',',LAT,LON) not in (
    select 
    concat(',',LAT,LON)
    from insurance
    group by concat(',',LAT,LON) 
    having count(*) >1
)

# Write your MySQL query statement below

select 
    d.name as 'Department',e1.name as Employee,e1.salary as Salary
from Employee e1
    join Department d on e1.departmentId = d.id
where 3 > (
    select 
        count(distinct e2.salary) 
    from Employee e2 
    where e2.salary > e1.salary
        and e1.departmentId = e2.DepartmentId
)

# Write your MySQL query statement below
select 
a.name as Department,a.Employee,a.Salary
from (select
 d.name
 ,e.name as Employee
 ,e.salary as Salary
,dense_rank() over(partition by e.departmentId order by e.salary desc ) as rn
from Employee e left join Department d on e.departmentId = d.id
group by e.id,e.name,e.salary) a where a.rn <= 3;

# Write your MySQL query statement below
select user_id,
CONCAT(Upper(left(name,1)),Lower(substring(name,2))) name 
from users 
order by user_id

# Write your MySQL query statement below

select
*
from Patients
where conditions like '% DIAB1%' or conditions like 'DIAB1%'

# Write your MySQL query statement below


SELECT
    *
FROM
    PATIENTS
WHERE
    CONDITIONS REGEXP '^DIAB1|\\sDIAB1';

DELETE FROM Person
WHERE id NOT IN(
    SELECT
        id
    FROM (
        SELECT
            MIN(id) as id
            FROM Person
            Group by email
    )t
)

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

# Write your MySQL query statement below

select 
max(salary) as SecondHighestSalary 
from Employee where salary < (select 
max(salary) as salary
from Employee)

select(
    select
        salary
    from (
        select
            distinct(salary),
            rank() over(order by salary desc) rk
        from Employee
    ) a where rk = 2
) as SecondHighestSalary

select
sell_date,count(distinct product) as num_sold
,group_concat(
    distinct product 
    order by product separator ','
) products
from Activities
group by sell_date
order by sell_date

# Write your MySQL query statement below
with aa as(
    select 
    product_id,sum(unit) as units
    from Orders 
    where date_format(order_date,'%Y-%m') = '2020-02'
    group by product_id
    having units >=100
)
select
bb.product_name
,aa.units as unit

from aa left join Products bb using(product_id)

select
    product_name,
    sum(unit) unit
from Orders O
left join Products P using(product_id)
where order_date like '2020-02%'
group by product_name
having unit >= 100

# Write your MySQL query statement below
SELECT 
    user_id AS 'user_id',
    name AS 'name',
    mail AS 'mail'
FROM
    Users
WHERE mail regexp '^[a-zA-Z][a-zA-Z0-9/\\_\\.\\-]*@leetcode\\.com'
;