目錄

• • 引子
  • 十大SQL
  • • 1. 統(tǒng)計班級總分前十名
    • 2. 刪除重復(fù)記錄, 且保留一條
    • 3. 最大連續(xù)登陸天數(shù)的問題
    • 4. 計算除去部門最高工資，和最低工資的平均工資
    • 5. 計算占比和同比增長
    • 6. 算成績
    • 7.算昨天每個城市top 10消費金額的用戶，輸出city_id,city_name,uid, 消費總金額
    • 8. 求連續(xù)點擊三次的用戶數(shù)，中間不能有別人的點擊 ，最大連續(xù)天數(shù)的變形問題
    • 9. AB球隊得分流水表，得到連續(xù)三次得分的隊員名字 和每次趕超對手的球員名字
    • 10. 舉例說明內(nèi)連接、外連接、左連接、右連接的區(qū)別
  • SQL語法圖解
  • 窗口函數(shù)
  • 參考

引子

哪些是程序員的通用能力？算法、正則表達(dá)式和SQL。這三樣，是程序員的基本功，就跟數(shù)學(xué)公式一樣，它不涉及智商，但關(guān)乎你的學(xué)習(xí)態(tài)度，牽扯到程序員的面子。面試官考這些時，如果連這個都不會，就會遭到鄙視。有次有個應(yīng)聘者質(zhì)疑我，說術(shù)業(yè)有專攻，你不該考這些問題，說我這是在問茴字的四種寫法。

十大SQL

1. 統(tǒng)計班級總分前十名

表結(jié)構(gòu)stu_score：(student_id, course_id, score)

select student_id, sum(score) as s from stu_score group by student_id order by s desc limit 10

這道題比較基本，考察聚合函數(shù)用法。下面兩個進(jìn)階一點：
要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排序，若人數(shù)相同，按課程號升序排序

select 課程號, count(學(xué)號) as 選修人數(shù) from score group by 課程號 having count(學(xué)號)>=2 order by 選修人數(shù) desc, 課程號 asc;

查詢沒有學(xué)全所有課的學(xué)生的學(xué)號、姓名

select 學(xué)號,姓名 from student where 學(xué)號 in (select 學(xué)號 from score group by 學(xué)號 having count(課程號) < (select count(課程號) from course));

2. 刪除重復(fù)記錄, 且保留一條

表結(jié)構(gòu): (book_id, book_name)
從書籍列表里，刪除書名重復(fù)的記錄，保留book_id最小的記錄：

delete from ebook where book_name in (select book_name from ebook group by book_name having count(*) > 1)and book_id not in (select min(book_id) from ebook group by book_name having count(*)>1);

這個考察了子查詢和min()函數(shù)以及having子句的使用。很多數(shù)據(jù)庫都支持這種子查詢。注意，上述SQL在MySQL中執(zhí)行會報錯：

[HY000][1093] You can't specify target table 'ebook' for update in FROM clause

這是因為MySQL不允許你在做子查詢時去修改表。trick的辦法是創(chuàng)建臨時表：

delete from ebook where book_name in (select t1.book_name from (select book_name from ebook group by book_name having count(*) > 1) as t1)and book_id not in (select t2.id from (select min(book_id) as id from ebook group by book_name having count(*)>1) as t2);

上面創(chuàng)建了兩張臨時表t1和t2。這樣MySQL就可以執(zhí)行了。

3. 最大連續(xù)登陸天數(shù)的問題

題目: 找出連續(xù)7天登陸，連續(xù)30天登陸的用戶。
考察點：窗口函數(shù)

select * from （select user_id ,count(1) as numfrom(select user_id,date_sub(log_in_date, rank) dtsfrom (select user_id,log_in_date, row_number() over(partitioned by user_id order by log_in_date ) as rankfrom user_log)t)agroup by dts ）b where num = 7

4. 計算除去部門最高工資，和最低工資的平均工資

emp 表：（id 員工 id ，deptno 部門編號，salary 工資）

核心是使用窗口函數(shù)降序和升序分別排一遍就取出了最高和最低。

select a.deptno，avg(a.salary) from (select *, rank() over( partition by deptno order by salary ) as rank_1, rank() over( partition by deptno order by salary desc) as rank_2 from emp) a group by a.deptno where a.rank_1 >1 and a.rank_2 >1

5. 計算占比和同比增長

t_user記錄了用戶注冊時間和平臺，統(tǒng)計2018年1月份
每天各平臺(“ios”,“android”,“h5”)注冊用戶總量占所有平臺總用戶的比例，以及各平臺注冊用戶按周同比增長（與一周前相比）的比例

建表語句

create table t_user ( uid BIGINT COMMENT "用戶id" , reg_time STRING COMMENT "注冊時間,如2018-07-01 08:11:39" , platform STRING COMMENT "注冊平臺，包括app ios h5" );

解答：
知識點：窗口函數(shù)。
注意：如果存在某天的缺失數(shù)據(jù)，偏移函數(shù)會有錯誤

SELECT a.reg_date ,a.platform ,ROUND(a.reg_num/sum(a.reg_num)over(PARTITION BY a.reg_date),4) as rate ,ROUND((a.reg_num-a.reg_num_7)/a.reg_num_7,4) as rate_week FROM( SELECT DATE(reg_time) as reg_date ,platform ,COUNT(uid) as reg_num ,lag(COUNT(uid),7)over(PARTITION BY platform ORDER BY DATE(reg_time)) as reg_num_7 FROM t_user WHERE SUBSTR(reg_time,1,7)='2018-01' GROUP BY DATE(reg_time),platform ) a ;

6. 算成績

表名：subject_scores
輸入
Name subject score
王建國 數(shù)學(xué) 95
王建國 語文 89
李雪琴 數(shù)學(xué) 100
李雪琴 語文 100
李雪琴 英語 100

輸出
Name math chinese English
王建國 95 89 0
李雪琴 100 100 100

解答：
所涉知識點：GROUP BY 和 CASE WHEN 實現(xiàn)行變列
注意:(1)空的數(shù)據(jù)這里判斷為0；(2)CASE WHEN 前要使用聚合函數(shù)，不然報錯）

SELECT name, MAX(CASE subject WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as math, MAX(CASE subject WHEN '語文' THEN score ELSE 0 END) as chinese, MAX(CASE subject WHEN '英語' THEN score ELSE 0 END) as English FROM subject_scores GROUP BY name;

7.算昨天每個城市top 10消費金額的用戶，輸出city_id,city_name,uid, 消費總金額

表名：orders
每次消費記錄一條
city_id,city_name,uid,order_id,amount,pay_order_time, pay_date
解答：(窗口函數(shù))

SELECT a.city_id, a.city_name, a.uid, a.pay_amount as '消費總金額' FROM ( SELECT city_id,city_name,uid,SUM(amount) as pay_amount,RANK()over(PARTITION BY city_id ORDER BY SUM(amount) DESC) as rank_no FROM orders WHERE pay_date='2020-01-01' GROUP BY city_id,city_name,uid ) a WHERE a.rank_no<=10;

8. 求連續(xù)點擊三次的用戶數(shù)，中間不能有別人的點擊 ，最大連續(xù)天數(shù)的變形問題

總結(jié)：相鄰問題的本質(zhì)就是基于研究對象(比如用戶、會員、員工等),利用窗口函數(shù)對時間字段進(jìn)行有差別的排序，然后基于研究對象和新增的{排序差值列}，進(jìn)行分組計數(shù)的求連續(xù)點擊、簽到、復(fù)購等業(yè)務(wù)問題的計算;

''' a表記錄了點擊的流水信息，包括用戶id ，和點擊時間 usr_id a a b a a a a click_time t1 t2 t3 t4 t5 t6 t7''' -- 方式一： use demo; WITH t1 AS (SELECT MemberID AS user_id, STime AS click_timeFROM OrderListWHERE MemberID IS NOT NULL/*選取demo.OrderList 作為底表測試數(shù)據(jù)*/AND DATE_FORMAT(STime, '%Y-%m') = '2017-02'), t2 AS (SELECT *, row_number() OVER (ORDER BY click_time) AS rank1, row_number() OVER (PARTITION BY user_id ORDER BY click_time) AS rank2FROM t1), t3 AS (SELECT *, rank1 - rank2 AS diffFROM t2), t4 AS (SELECT DISTINCT user_idFROM t3GROUP BY user_id, diffHAVING COUNT(1) > 3) -- SELECT * from t4 ; SELECT * FROM t3 WHERE user_id IN (SELECT user_idFROM t4 ) ORDER BY user_id, diff, click_time; -- 方式二： SELECT DISTINCT user_id FROM (SELECT *, rank_1 - rank_2 AS diffFROM (SELECT *,row_number() OVER (ORDER BY click_time) AS rank_1,row_number() OVER (PARTITION BY user_id ORDER BY click_time) AS rank_2FROM (SELECT MemberID AS user_id, STime AS click_timeFROM OrderListWHERE MemberID IS NOT NULL /*選取demo.OrderList 作為底表測試數(shù)據(jù)*/AND DATE_FORMAT(STime, '%Y-%m') = '2017-02') a) b ) c GROUP BY diff, user_id HAVING COUNT(1) > 3;

9. AB球隊得分流水表，得到連續(xù)三次得分的隊員名字 和每次趕超對手的球員名字

表結(jié)構(gòu):

create table bktab (team string comment '球隊名稱',number int comment '球員號碼',score_time string comment '得分時間',score int comment '得分分?jǐn)?shù)',name string comment '球員姓名') comment 'AB球隊得分流水表' row format delimited fields terminated by '\t' lines terminated by '\n' stored as orc;

分析思路:
1.按score_time 對全局排序
2.獲取當(dāng)前行 A隊累計得分 B隊累計得分
3.獲取 當(dāng)前 A隊累計得分 與 B隊累計得分的差值
4.當(dāng)前行差值 與上一行差值,發(fā)生符合變化時,表示 分?jǐn)?shù)發(fā)生了反超

-- 查詢sql selectteam,number,score_time,score,name,ateam_score,bteam_score from (selectteam,number,score_time,score,name,ateam_score,bteam_score,diff_score,lag(diff_score) over (order by score_time asc) as pre_diff_score,case when diff_score > 0 and lag(diff_score) over (order by score_time asc) < 0 then 1when diff_score < 0 and lag(diff_score) over (order by score_time asc) > 0 then 1when diff_score is not null and lag(diff_score) over (order by score_time asc) is null then 1else 0end as if_surpassfrom (selectteam,number,score_time,score,name,sum(if(team = 'A',score,0)) over (order by score_time asc) as ateam_score,sum(if(team = 'B',score,0)) over (order by score_time asc) as bteam_score,sum(if(team = 'A',score,0)) over (order by score_time asc) - sum(if(team = 'B',score,0)) over (order by score_time asc) as diff_scorefrom bktab) t1 ) t2 where if_surpass = 1 ;

10. 舉例說明內(nèi)連接、外連接、左連接、右連接的區(qū)別

下圖展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相關(guān)的 7 種用法：

查詢所有課程成績小于60分學(xué)生的學(xué)號、姓名

SELECT A.學(xué)號,B.姓名 FROM score A LEFT JOIN student B ON A.學(xué)號 = B.學(xué)號 GROUP BY A.學(xué)號 HAVING MAX(成績) < 60; SELECT customer.last_name, city.name FROM customer INNER JOIN cityON customer.id = city.customer_id; SELECT c.last_name AS lname, t.name AS city FROM customer AS c INNER JOIN city AS tON c.id = t.customer_id; SELECT last_name FROM customer INTERSECT SELECT last_name FROM employee;

You can join tables using JOIN, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN (please see the courses listed at the end of this article for more information). In this example, we want to join data from the tables customer and city. INNER JOIN needs to come after FROM and the name of the first table, customer. After INNER JOIN, place the name of the second table, city. The records with data from both tables are matched by ON with the condition to join. The records in the table city are matched to the records from the table customer if they have the same value in the column id in the table customer and in the column customer_id in the table city.

SQL語法圖解

窗口函數(shù)

窗口函數(shù)的基本語法如下：

<窗口函數(shù)> over (partition by <用于分組的列名>order by <用于排序的列名>)

窗口函數(shù)包括：
1） 專用窗口函數(shù)，如rank, dense_rank, row_number等專用窗口函數(shù)
2） 聚合函數(shù)，如sum. avg, count, max, min等

因為窗口函數(shù)是對where或者group by子句處理后的結(jié)果進(jìn)行操作，所以窗口函數(shù)原則上只能寫在select子句中。

參考

• https://zhuanlan.zhihu.com/p/92654574

總結(jié)

以上是生活随笔為你收集整理的十个精妙绝伦的SQL语句，说尽SQL精华的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。