【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_idand 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(CASEWHEN 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