【MySQL】高频 SQL 50 题（基础版）

高频SQL50题（基础版）

1.查询

2.连接

MySQL多表查询（联合查询、连接查询、子查询）

left join 左连接

我们首先执行LEFT JOIN操作，将两个表的数据基于 id 列进行组合。同样，我们使用 LEFT JOIN 来确保将所有 Employees 表中的行都包含在结果中，即使在 EmployeeUNI 表中没有匹配的行。
由于我们想要从组合表中检索列 unique_id 和 name ，所以我们将从 EmployeeUNI 表选择 unique_id 列，从 Employees 表选择 name 列。完整代码如下：

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

使用left join或者join都可以，因为这里的case是Sales表中的Sale_id是包含于Product表中的product_id的，所以不会出现null的情况。
内连接（INNER JOIN）只返回两个表中都有匹配的行。
左连接（LEFT JOIN）返回左表（这里是 Employees 表）的所有行，如果右表（这里是 EmployeeUNI 表）中没有匹配的行，则结果集中的对应列将为 NULL。

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

解1.左连接，然后找出null就行了
连接出来的null正是我们需要的，再按customer_id聚合就好了。
超出时间限制了从😱

# Write your MySQL query statement below
select customer_id,count(customer_id) count_no_trans
from Visits
left join Transactions
on Visits.visit_id=Transactions.visit_id
where transaction_id is null
group by customer_id

解2. NOT IN
先在交易表找到不重复的visit_id，然后再在Visits表去掉这些id，就是浏览了不交易的记录

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

交叉联结

使用交叉联结会cross join将两个表中所有的数据两两组合。
如何找到 “昨天”（前一天），两个时间计算的函数：

datediff(日期1, 日期2)：
得到的结果是日期1与日期2相差的天数。
如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。
另一个关于时间计算的函数是：
timestampdiff(时间类型, 日期1, 日期2)
这个函数和上面diffdate的正、负号规则刚好相反。
日期1大于日期2，结果为负，日期1小于日期2，结果为正。
在“时间类型”的参数位置，通过添加“day”, “hour”, “second”等关键词，来规定计算天数差、小时数差、还是分钟数差。

# Write your MySQL query statement below
select a.id
from Weather as a
cross join Weather as b
on datediff(a.recordDate,b.recordDate)=1
where a.temperature>b.temperature

在这里插入图片描述

解法1.最直接的方式

# Write your MySQL query statement below
select t1.machine_id,round(avg(t2.timestamp-t1.timestamp),3) as processing_time
from Activity t1,Activity t2
where 
        t1.machine_id=t2.machine_id
    and t2.activity_type='end'
    and t1.activity_type='start'
    and t1.process_id=t2.process_id
group by machine_id

解法2：使用CASE …THEN…方法
具体解释

# Write your MySQL query statement below
SELECT 
    machine_id, 
    ROUND(
        SUM(
            CASE
            WHEN activity_type = 'end' THEN timestamp 
            ELSE -timestamp 
            END
            ) / count(distinct process_id), 
            3
            ) AS processing_time
FROM activity
GROUP BY machine_id

GROUP BY和HAVING

-- 从 Employee 表中选择经理的姓名
SELECT e1.name
FROM Employee e1
-- 使用左连接将 Employee 表自身连接，e1 表示经理，e2 表示员工
LEFT JOIN Employee e2 
ON e1.id = e2.managerId
-- 按照经理的姓名进行分组
GROUP BY e2.managerId
-- 使用 HAVING 子句过滤出管理员工数量超过 4 人的经理
HAVING COUNT(e2.managerId) >= 5;

1934.确认率—— IFNULL( , )，IF( , , , )

# Write your MySQL query statement below
select Signups.user_id ,ifnull(round(sum(if(Confirmations.action ='confirmed',1,0))/count(Confirmations.action),2),0)as confirmation_rate
from Signups
left join Confirmations
on Signups.user_id=Confirmations.user_id
group by Signups.user_id

3.聚合函数

620.有趣的电影 ——order by

ASC：表示升序排序，是默认的排序方式。
DESC：表示降序排序。

# Write your MySQL query statement below
select *
from cinema
where description!='boring' and id%2=1 
order by rating DESC

1251.平均售价——between…and…

between…and…判断一个时间是否在两个日期范围之内

# Write your MySQL query statement below
select Prices.product_id,ifnull(round(sum(price*units)/sum(units),2),0) as average_price
from Prices
left join UnitsSold
on Prices.product_id=UnitsSold.product_id and UnitsSold.purchase_date between Prices.start_date and end_date
group by Prices.product_id