SQL零基础入门教程（5）互联网大厂面试常考SQL题

计算用户次日留存率： WITH first_login AS (SELECT user_id, MIN(login_date) AS first_date FROM users GROUP BY user_id), retained_users AS (SELECT DISTINCT a.user_id FROM first_logina JOIN usersb ON a.user_id = b.user_id AND b.login_date = DATE_ADD(a.first_date, INTERVAL 1 DAY)) SELECT ROUND((SELECT COUNT() FROM retained_users) / (SELECT COUNT() FROM first_login) 1 00, 2 ) AS next_day_retention_rate;
股票最佳买卖时机： SELECT s1 .date AS buy_date, s2 .date AS sell_date, (s2 .price
s1 .price) AS max_profit FROM stocks s1 JOIN stocks s2 ON s2 .date > s1 .date WHERE (s2 .price
s1 .price) = (SELECT MAX(s2 b.price
s1 b.price) FROM stocks s1 b JOIN stocks s2 b ON s2 b.date > s1 b.date) LIMIT 1 ;
查询所有课程≥8 0分的学生： SELECT name FROM scores GROUP BY name HAVING MIN(score) >= 8 0;
查询各科第三名学生： WITH subject_rank AS (SELECT name, subject, score, DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS subject_rnk FROM scores), total_score AS (SELECT name, SUM(score) AS total_score, RANK() OVER (ORDER BY SUM(score) DESC) AS total_rnk FROM scores GROUP BY name) SELECT sr.name, sr.subject, sr.score, ts.total_score, ts.total_rnk FROM subject_rank sr JOIN total_score ts ON sr.name = ts.name WHERE sr.subject_rnk = 3 ;
计算每日累计销售额： SELECT employee_id, sale_date, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS cumulative_sales FROM sales ORDER BY employee_id, sale_date;
计算最高销售额及其占比： SELECT employee_id, MAX(sale_amount) AS max_sale, ROUND(MAX(sale_amount) / SUM(sale_amount) OVER (PARTITION BY employee_id) 1 00, 2 ) AS max_sale_percentage FROM sales GROUP BY employee_id;
高效统计发货地址搜索次数： --阶段1 ：去重计数 WITH addr_counts AS (SELECT product_address, COUNT(DISTINCT requestid) AS partial_count FROM search_logs GROUP BY product_address, requestid) --阶段2 ：汇总 SELECT product_address, SUM(partial_count) AS total_searches FROM addr_counts GROUP BY product_address;
实操提醒：优先用DENSE_RANK()，分阶段聚合防倾斜。

Mysql45题

今天在图书馆看到一个学生，拿着厚厚的书本，旁边堆满了笔记和教材。
我突然想起，这学生大概是在为即将到来的期末考试做准备吧。
等等，我好像也有个类似的情况，那次考试前，我整整准备了两个星期，每天都是从早上学到深夜。
最后成绩还不错，但那时候真的很累啊。
说起来，考试这事儿，除了考验知识，也是在考验人的意志力。
不过，这也许就是学习的乐趣所在吧。

SQL50题及答案

好的，下面是针对你提出的问题的SQL查询语句：
1 .1 查询同时存在"01 "课程和"02 "课程的情况 sql SELECT S.SID, S.SNAME, SC1 .CID AS Course01 , SC2 .CID AS Course02 , SC1 .GRADE AS Grade01 , SC2 .GRADE AS Grade02 FROM Student S JOIN SC SC1 ON S.SID = SC1 .SID AND SC1 .CID = '01 ' JOIN SC SC2 ON S.SID = SC2 .SID AND SC2 .CID = '02 '
1 .2 查询存在"01 "课程但可能不存在"02 "课程的情况(不存在时显示为null) sql SELECT S.SID, S.SNAME, SC1 .CID AS Course01 , SC1 .GRADE AS Grade01 , SC2 .CID AS Course02 , SC2 .GRADE AS Grade02 FROM Student S LEFT JOIN SC SC1 ON S.SID = SC1 .SID AND SC1 .CID = '01 ' LEFT JOIN SC SC2 ON S.SID = SC2 .SID AND SC2 .CID = '02 '
1 .3 查询不存在"01 "课程但存在"02 "课程的情况 sql SELECT S.SID, S.SNAME, SC2 .CID AS Course02 , SC2 .GRADE AS Grade02 FROM Student S LEFT JOIN SC SC1 ON S.SID = SC1 .SID AND SC1 .CID = '01 ' LEFT JOIN SC SC2 ON S.SID = SC2 .SID AND SC2 .CID = '02 ' WHERE SC1 .CID IS NULL
4 .1 查有成绩的学生信息 sql SELECT S.SID, S.SNAME, SC.CID, SC.GRADE FROM Student S JOIN SC ON S.SID = SC.SID
1 5 .1 按各科成绩进行排序，并显示排名，Score重复时合并名次 sql SELECT S.SID, S.SNAME, SC.CID, SC.GRADE, DENSE_RANK() OVER (PARTITION BY S.SID ORDER BY SC.GRADE DESC) AS Rank FROM Student S JOIN SC ON S.SID = SC.SID
1 6 .1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺 sql SELECT S.SID, S.SNAME, SUM(SC.GRADE) AS TotalGrade, RANK() OVER (ORDER BY SUM(SC.GRADE) DESC) AS Rank FROM Student S JOIN SC ON S.SID = SC.SID GROUP BY S.SID, S.SNAME
以上SQL语句都是基于常见的数据库表结构编写的，具体的表名和字段名可能需要根据你的数据库结构调整。