MYSQL力扣刷题高频50题基础版

SELECT c.customer_id
FROM Customer c
GROUP BY c.customer_id
HAVING COUNT(DISTINCT c.product_key) = (SELECT COUNT(*) FROM Product)

1757. 可回收且低脂的产品

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |
+-------------+---------+
product_id 是该表的主键（具有唯一值的列）。
low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。
recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。

编写解决方案找出既是低脂又是可回收的产品编号。

返回结果 无顺序要求 。

返回结果格式如下例所示：

示例 1：

输入：
Products 表：
+-------------+----------+------------+
| product_id  | low_fats | recyclable |
+-------------+----------+------------+
| 0           | Y        | N          |
| 1           | Y        | Y          |
| 2           | N        | Y          |
| 3           | Y        | Y          |
| 4           | N        | N          |
+-------------+----------+------------+
输出：
+-------------+
| product_id  |
+-------------+
| 1           |
| 3           |
+-------------+
解释：
只有产品 id 为 1 和 3 的产品，既是低脂又是可回收的产品。

解答：

select product_id from Products where low_fats="Y" and recyclable ="Y";

584. 寻找用户推荐人

select name from Customer where referee_id is null or referee_id !=2;

 

595. 大的国家

select name,population,area from World where area>=3000000 or population>=25000000;

 

1148. 文章浏览 I

select distinct author_id as id from Views where author_id=viewer_id order by id;

1683. 无效的推文

select tweet_id from Tweets where length(content)>15;

1378. 使用唯一标识码替换员工ID

select unique_id,name from Employees a left join EmployeeUNI b on a.id=b.id;

1068. 产品销售分析 I

select product_name,year,price from Sales a left join Product b on a.product_id = b.product_id;

 

1581. 进店却未进行过交易的顾客

select customer_id,count(customer_id)as count_no_trans from (select customer_id,transaction_id from visits left join transactions on visits.visit_id = transactions.visit_id) as a where transaction_id is null group by customer_id

这道题用了嵌套查询，感觉比较高端一点。首先visits和transaction进行左连接，那么来商场不买东西的人transaction_id 是空的，所以查找是空部分就行。

197. 上升的温度

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

要用到datediff，两个日期的天数差集。可以试一下w1.id.

1661. 每台机器的进程平均运行时间

看到一个新奇的做法

select machine_id,
round(sum(if(activity_type='end',timestamp,-timestamp))/count(distinct process_id),3) as processing_time
from activity
group by machine_id

为每个不同的machine_id计算时间。求和公式那里，如果type是end，则累加timestamp，不是的话就减去timestamp，这里真的很巧，distinct process_id，是计算每个machine_id组内不同的process_id的数量。保留小数点后三位。

577. 员工奖金

select a.name, b.bonus from Employee a left join Bonus b on a.empId=b.empId where 
b.bonus<1000 or b. bonus is null;

 1280. 学生们参加各科测试的次数

SELECT 
    a.student_id,
    a.student_name,
    b.subject_name,
    SUM(IF(c.subject_name = b.subject_name, 1, 0)) AS attended_exams
FROM 
    Students AS a
LEFT JOIN 
    Examinations AS c ON a.student_id = c.student_id
CROSS JOIN 
    Subjects AS b
GROUP BY 
    a.student_id,
    b.subject_name
ORDER BY 
    a.student_id ASC;

用cross join 了解students和subjuects表。

570. 至少有5名直接下属的经理

select e1.name from Employee e1 
left join Employee e2 on e1.id=e2.managerId
group by e1.id,e1.name
having count(e2.id)>=5;

1934. 确认率

SELECT 
    s.user_id,
    CASE 
        WHEN c.total IS NULL THEN 0.00
        ELSE ROUND((c.confirmed / c.total), 2)
    END AS confirmation_rate
FROM 
    (SELECT user_id FROM Signups) s
LEFT JOIN 
    (SELECT 
        user_id, 
        COUNT(*) AS total,
        SUM(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END) AS confirmed
     FROM Confirmations
     GROUP BY user_id) c
ON s.user_id = c.user_id
ORDER BY s.user_id;

620. 有趣的电影

select id,movie,description,rating from cinema where description !='boring' and id%2!=0 
order by rating desc;

1251. 平均售价

SELECT 
    u.product_id,
    ROUND(SUM(p.price * u.units) / SUM(u.units), 2) AS average_price
FROM 
    UnitsSold u
JOIN 
    Prices p ON u.product_id = p.product_id
    AND u.purchase_date >= p.start_date
    AND u.purchase_date <= p.end_date
GROUP BY 
    u.product_id;

1075. 项目员工 I

select a.project_id,round(sum(b.experience_years) /count(a.employee_id),2)average_years
from Project a
join Employee b
on a.employee_id=b.employee_id
group by a.project_id

1633. 各赛事的用户注册率
 

select a.contest_id,round(count(*)/(select count(*)from users)*100,2)percentage
from Register a
join Users b
on a.user_id=b.user_id
group by a.contest_id
order by percentage desc,a.contest_id

1211. 查询结果的质量和占比

select query_name,round(avg(rating/position),2)as quality,round(avg(rating<3)*100,2)as poor_query_percentage
from queries
where query_name is not null
group by query_name;

1193. 每月交易 I

select date_format(trans_date,'%Y-%m') as month,
country,
count(*) 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 month,country

这个时间格式要查一下，先查出这个段时间的内容！

1174. 即时食物配送 II

select round(avg(if (a.order_date=a.customer_pref_delivery_date,1,0))*100,2) immediate_percentage
from Delivery a
right join
(select customer_id,min(order_date) min_date from Delivery group by customer_id) temp
on a.customer_id=temp.customer_id and a.order_date=temp.min_date

550. 游戏玩法分析 IV

SELECT 
    ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM 
    Activity
WHERE 
    (player_id, event_date) IN (
        SELECT 
            player_id, 
            MIN(event_date) + INTERVAL 1 DAY
        FROM 
            Activity
        GROUP BY 
            player_id
    );

先找到每个用户最小的时间，然后加一天。再查总表的id和时间存不存在在这个表里。

2356. 每位教师所教授的科目种类的数量
 

select teacher_id,count(distinct subject_id) as cnt
from teacher
group by teacher_id

1141. 查询近30天活跃用户数

select activity_date day,
    count(distinct user_id) active_users
from activity
group by day
having datediff('2019-07-27',day) between 0 and 29

1084. 销售分析III

​

SELECT 
    b.product_id, 
    b.product_name
FROM 
    Product AS b
WHERE 
    b.product_id IN (
        SELECT 
            a.product_id
        FROM 
            Sales AS a
        WHERE 
            a.sale_date BETWEEN '2019-01-01' AND '2019-03-31'
    )
AND 
    b.product_id NOT IN (
        SELECT 
            a.product_id
        FROM 
            Sales AS a
        WHERE 
            a.sale_date < '2019-01-01' OR a.sale_date > '2019-03-31'
    );

[点击并拖拽以移动]
​

596. 超过5名学生的课

select class from Courses
group by class
having count(distinct student)>=5;

排名排的好后。

1729. 求关注者的数量

select user_id,count(follower_id) followers_count from Followers
group by user_id
order by user_id

619. 只出现一次的最大数字

select max(num) num from MyNumbers where num not in
(select num from MyNumbers group by num
having count(num)>1)

先找出出现次数大于1的数字组成一个表，然后再从原表中找出不在次数大于1的表。

1045. 买下所有产品的客户

SELECT c.customer_id
FROM Customer c
GROUP BY c.customer_id
HAVING COUNT(DISTINCT c.product_key) = (SELECT COUNT(*) FROM Product)

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

select b.employee_id,b.name,count(a.employee_id) as reports_count,round(avg(a.age),0) as average_age
from Employees a  join Employees b
on a.reports_to=b.employee_id
group by b.employee_id
order by b.employee_id

1789.员工的直属部门

select distinct employee_id,department_id from employee
where primary_flag='Y' or employee_id in(select employee_id from employee group by employee_id having count(department_id)=1)

 思路：先查找primary_flag='Y'的员工，然后看到有一些一个人一个部门但不是直属的，应该要是直属，所以再查找一次，根据employee_id分组，统计那些组里，department等于1的id，那么他们也算直属部门。

610.判断三角形

select * ,case 
when x+y>z and x+z>y and y+z>x then 'Yes' 
else 'No'
End as triangle
From Triangle;

 这里记得case前面要有一个逗号，就算换行了也要。

 

180. 连续出现的数字

select distinct l1.num as ConsecutiveNums
from Logs l1
Join Logs l2 on l1.id=l2.id-1 and l1.num=l2.num
join Logs l3 on l2.id=l3.id -1 and l2.num=l3.num;

用自连接，比较每行的num字段与下一行 和下下一行