力扣高频SQL50题知识点

一、查询select product_id from Products where low_fatsY and recyclableY;select 输出 from 表名where 条件select name from Customer where referee_id ! 2 or referee_id is NULL;对于条件为某一元素为NULL用“元素 is NULL”select name,population,area from World where area 3000000 or population 25000000;select 输出 from 表名 注意输出顺序select author_id as id from Views # 重命名 where author_id viewer_id group by author_id order by id;group by 分组去重order by 升序排列select tweet_id from Tweets where length(content)15;length( ) 表示字符数二、连接1.左连接select EmployeeUNI.unique_id, Employees.name from Employees left join EmployeeUNI on Employees.id EmployeeUNI.id;SELECT ...FROM 表ALEFT JOIN 表B ON 表A.字段 表B.字段;左连接LEFT JOIN左连接以左表LEFT TABLE为基准返回左表中的所有记录右表中匹配的记录如果存在如果右表中没有匹配则返回NULL值2.统计次数select customer_id, count(*) as count_no_trans from visits left join transactions on visits.visit_id transactions.visit_id where transactions.transaction_id is null group by customer_id;select customer_id, count(*) as count_no_trans选择客户ID和统计无交易访问次数count(*)计算符合条件的记录数as count_no_trans给结果列起别名from visits指定主表为visits表包含所有访问记录left join transactions on visits.visit_id transactions.visit_id左连接transactions表连接条件两个表的visit_id相等左连接确保visits表的所有记录都会被保留where transactions.transaction_id is null筛选条件只保留transactions表中没有对应记录的访问即那些访问了购物中心但没有进行交易的记录group by customer_id按客户ID分组对每个客户统计其无交易访问的次数3.自连接自连接T197将同一表自连接两次分别作为今天和昨天使用datediff函数确保日期差为1天datediff函数计算两个日期之间的天数差确保只比较相邻日期比较温度并筛选出更高的记录select w1.id from Weather w1, Weather w2 where datediff (w1.recordDate, w2.recordDate) 1 and w1.Temperature w2.Temperature;自连接T1661将Activity表自连接两次分别作为start和end通过machine_id和process_id匹配同一进程计算时间差并求平均值select a.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条件通过JOIN条件匹配同一进程的start和end记录计算时间差1a.machine_id b.machine_id作用确保连接的是同一台机器的记录原因每台机器有独立的进程不同机器的进程ID可能重复示例机器0的进程0与机器1的进程0不会混淆2a.process_id b.process_id作用确保连接的是同一进程的start和end记录原因每个进程有唯一的process_id示例进程0的start记录只与进程0的end记录匹配3a.activity_type ‘start’作用筛选出start类型的记录作为左表原因确保左表只包含开始时间示例只选择start行排除end行4b.activity_type ‘end’作用筛选出end类型的记录作为右表原因确保右表只包含结束时间示例只选择end行排除start行ROUND(..., 3)将平均值四舍五入保留3位小数4.字查询子查询T97通过子查询获取前一天的温度比较当前温度和前一天温度select id from Weather w1 where Temperature ( select Temperature from Weather w2 where datediff(w1.recordDate, w2.recordDate) 1 );子查询T1661select machine_id, ROUND(AVG(end_time - start_time), 3) as processing_time from ( select machine_id, process_id, max(case when activity_type end then timestamp END) as end_time, max(case when activity_type start then timestamp END) as start_time from Activity group by machine_id, process_id ) as process_times group by machine_id;逐行解析1select machine_id, process_id选择机器ID和进程ID作为分组依据每行代表一个独立的进程2max(case when activity_type end then timestamp END) as end_time作用提取每个进程的结束时间戳聚合函数MAX确保同一进程只取一个结束时间条件判断当活动类型为’end’时取timestamp值示例进程0的结束时间1.5203max(case when activity_type start then timestamp END) as start_time条件判断当活动类型为’start’时取timestamp值聚合函数MAX确保同一进程只取一个开始时间作用提取每个进程的开始时间戳示例进程0的开始时间0.712工作原理条件筛选CASE WHEN activity_type end THEN timestamp END只提取活动类型为’end’的时间戳其他情况返回NULL聚合处理MAX(...)从同一进程的所有记录中取最大值由于每个进程只有一个’end’记录实际返回该结束时间NULL值被自动忽略4group by machine_id, process_id分组依据按机器和进程分组作用将同一进程的start和end记录合并为一行结果每个进程一行包含开始和结束时间max函数用于从一组值中找出最大值MAX([ALL | DISTINCT] expression)核心特性1忽略NULL值MAX()自动跳过NULL值无数据时返回NULL2适用数据类型数值型整数、浮点数日期时间型datetime, timestamp字符串型按字典序比较3DISTINCT选项可选参数但通常不使用因为最大值不受重复值影响MAX(DISTINCT column)与MAX(column)结果相同常见用法场景1基础最大值查询SELECT MAX(salary) AS highest_salary FROM employees;2分组最大值最常用SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id;3条件聚合高级用法SELECT department_id, MAX(CASE WHEN gender M THEN salary END) AS male_max_salary, MAX(CASE WHEN gender F THEN salary END) AS female_max_salary FROM employees GROUP BY department_id;聚合函数汇总函数作用NULL处理典型场景MAX()最大值忽略找出最高薪资、最新时间MIN()最小值忽略找出最早时间、最低价格AVG()平均值忽略计算平均分数、平均时长SUM()总和忽略计算总销售额、总时长COUNT()计数特殊处理统计记录数量CASE表达式case when condition1 then result1 when condition2 then result2 else default_result end