高频 SQL 50 题(基础版)
*[注:题目来自力扣网]
1. 进店却未进行过交易的顾客
思路:
✅ 以Visits作为左表连Transactions, 没有只浏览光顾的的transaction_id是null
代码:
SELECT
customer_id,count(customer_id) count_no_trans
FROM
Visits as v
LEFT JOIN Transactions as t
on
v.visit_id = t.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id;
运行结果:
2. 上升的温度
思路:
✅ DATEDIFF() 函数返回两个日期之间的天数。
DATEDIFF(date1,date2) # date1 - date2
date1 和 date2 参数是合法的日期或日期/时间表达式。
注释:只有值的日期部分参与计算。
代码:
select
w1.id
from
Weather as w1
join
Weather as w2 # 两表交叉关联 内连接JOIN 笛卡尔积现象
on datediff(w1.recordDate,w2.recordDate) = 1
# DATEDIFF函数: DATEDIFF(date1, date2) #date1-date2
where
w1.Temperature > w2.Temperature;
运行结果:
3. 每台机器的进程平均运行时间
思路:
❓ round( avg( IF ( activity_type = 'start',- timestamp,+ timestamp ) ) * 2
⁉️ SUM求和,当进程为end是加上该时间戳,start时减去该时间戳,由于是根据machine_id进行GROUP BY的,所以要*2,ROUND方法对结果位数进行限制
代码:
#快速解法
select
machine_id,
round(avg(if(activity_type='start', -timestamp, timestamp))*2, 3) processing_time
from Activity
group by machine_id