Leetcode SQL基础50题

1148. 文章浏览 I

用distinct函数去重

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

先用group函数再用order函数

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

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

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

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

SELECT 
    v.customer_id, 
    COUNT(v.visit_id) AS count_no_trans  -- 统计该客户没有交易的访问次数
FROM 
    Visits v 
LEFT JOIN 
    Transactions t ON v.visit_id = t.visit_id
WHERE 
    t.transaction_id IS NULL  -- 筛选没有交易的访问记录
GROUP BY 
    v.customer_id;

197. 上升的温度

在只有一张表的情况下找表内数据的对应关系，可以通过连接方式将一张表变成两张表进行解题（JOIN），题干中要求找比前一天温度高的ID，两表对应关系可以通过日期 recordDate 建立连接等式，今天=昨天+1(时间日期函数DATEADD)或者今天-昨天=1（时间日期函数DATEDIFF），最后通过WHERE限制语句，比较连接表中temperature（int型数据）的大小。

SELECT a.id AS id
FROM Weather a
JOIN Weather b
ON a.recordDate = DATE_ADD(b.recordDate,INTERVAL 1 DAY)
WHERE a.temperature > b.temperature;

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

在这里插入代码片

select a.machine_id AS 'machine_id',
       ROUND(AVG(b.timestamp - a.timestamp) ,3) AS  'processing_time'
from Activity a join Activity b 
on a.machine_id=b.machine_id and 
   a.process_id =b.process_id and 
   a.activity_type = 'start' AND b.activity_type = 'end'
group by a.machine_id;

如果不用join怎么只用一张表来做

# Write your MySQL query statement below
SELECT machine_id AS 'machine_id',
    ROUND(
        SUM(IF(activity_type = 'start', -timestamp, timestamp))
        / COUNT(*) 
        * 2
        ,3) AS 'processing_time'
FROM Activity
GROUP BY machine_id
;

或者用distinct，用不用*2了

# Write your MySQL query statement below
SELECT machine_id AS 'machine_id',
    ROUND(
        SUM(IF(activity_type = 'start', -timestamp, timestamp))
        / COUNT(DISTINCT process_id) 
        ,3) AS 'processing_time'
FROM Activity
GROUP BY machine_id
;

也可以用case when then else end来判断

# Write your MySQL query statement below
SELECT machine_id AS 'machine_id',
    ROUND(
        SUM(
            CASE
                WHEN activity_type = 'start' THEN -timestamp
                ELSE timestamp
            END
        )
        / COUNT(DISTINCT process_id) 
    , 3) AS 'processing_time'
FROM Activity
GROUP BY machine_id
;

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

https://leetcode.cn/problems/students-and-examinations/solutions/3747647/mei-ge-bu-zou-xiang-jie-xiao-bai-ye-neng-m6le/?envType=study-plan-v2&envId=sql-free-50

CROSS JOIN：交叉连接，对表中每一行进行两两连接，即每个ID都将对应上所有的科目subject_name，不需要匹配条件

SELECT SS.student_id, SS.student_name,  SU.subject_name, COUNT(ES.subject_name) AS attended_exams
FROM Students SS
CROSS JOIN Subjects SU
LEFT JOIN Examinations ES
ON SS.student_id = ES.student_id
   AND SU.subject_name = ES.subject_name
GROUP BY student_id, subject_name
ORDER BY student_id, subject_name ;