戰(zhàn)練習(xí)題：電商三表設(shè)計(jì)與四階SQL能力躍遷)
1. 為什么這套MySQL練習(xí)題能真正幫你“把語(yǔ)法刻進(jìn)肌肉里”我?guī)н^(guò)不少剛學(xué)完SQL基礎(chǔ)的開(kāi)發(fā)者他們能背出SELECT、WHERE、JOIN的語(yǔ)法結(jié)構(gòu)也能在紙上寫(xiě)出簡(jiǎn)單的查詢語(yǔ)句但一到真實(shí)業(yè)務(wù)場(chǎng)景——比如要從訂單表里找出“近30天下單次數(shù)≥5次、且平均客單價(jià)超過(guò)200元的老客戶”就卡在中間不知道該先篩時(shí)間還是先分組不確定HAVING和WHERE到底誰(shuí)管誰(shuí)更別提怎么把用戶行為路徑瀏覽→加購(gòu)→下單→復(fù)購(gòu)用窗口函數(shù)串起來(lái)。這不是記不住是沒(méi)在真實(shí)數(shù)據(jù)流里反復(fù)“擰螺絲”。這套練習(xí)題的設(shè)計(jì)邏輯就是從這個(gè)斷層切入的。它不叫“MySQL入門100題”也不叫“面試高頻50問(wèn)”它叫“練習(xí)表題目答案”——三個(gè)要素缺一不可。練習(xí)表不是隨便造的假數(shù)據(jù)而是模擬了電商、內(nèi)容平臺(tái)、SaaS系統(tǒng)三類高頻業(yè)務(wù)域的真實(shí)字段設(shè)計(jì)邏輯用戶表里有is_vip、last_login_time、reg_source訂單表里有order_status含pending,shipped,refunded等狀態(tài)機(jī)字段、payment_method、discount_amount商品表里有category_path如/electronics/smartphones/iphone、stock_quantity、on_sale_flag。這些字段不是為了炫技而是你明天寫(xiě)報(bào)表、做AB測(cè)試、搭用戶分群時(shí)真正在數(shù)據(jù)庫(kù)里要面對(duì)的。關(guān)鍵詞里雖然空著但整套題的底層錨點(diǎn)非常清晰以“可執(zhí)行的最小閉環(huán)”為單位組織訓(xùn)練。每道題都自帶可運(yùn)行的建表語(yǔ)句、INSERT數(shù)據(jù)腳本、標(biāo)準(zhǔn)答案SQL、以及關(guān)鍵注釋——不是只告訴你“答案是SELECT * FROM users WHERE age 18”而是標(biāo)注“此處WHERE必須在GROUP BY之前執(zhí)行否則會(huì)報(bào)錯(cuò)1140”不是只給JOIN結(jié)果而是說(shuō)明“LEFT JOIN users ON orders.user_id users.id 比 INNER JOIN多返回37條記錄因?yàn)榇嬖谖醋?cè)用戶直接下單的灰度場(chǎng)景”。這種顆粒度才能讓練習(xí)從“看懂”走向“手熟”再走向“直覺(jué)反應(yīng)”。它適合三類人剛學(xué)完增刪改查想驗(yàn)證理解的新人準(zhǔn)備技術(shù)面試需要快速刷題的求職者還有像我這樣隔半年不碰復(fù)雜查詢手生到連GROUP_CONCAT的分隔符都要查文檔的“老手”。如果你現(xiàn)在打開(kāi)MySQL客戶端對(duì)著空白命令行發(fā)呆超過(guò)10秒這套題就是你的重啟開(kāi)關(guān)。2. 練習(xí)表設(shè)計(jì)背后的業(yè)務(wù)邏輯與數(shù)據(jù)陷阱很多人拿到練習(xí)題第一反應(yīng)是跳過(guò)建表語(yǔ)句直接抄答案。這恰恰踩中了第一個(gè)大坑不理解表結(jié)構(gòu)等于在迷霧中開(kāi)槍打中什么全靠運(yùn)氣。這套題的三張核心練習(xí)表——users用戶、orders訂單、products商品——每個(gè)字段名、每個(gè)約束、每條測(cè)試數(shù)據(jù)都藏著業(yè)務(wù)規(guī)則的影子。我們拆開(kāi)來(lái)看。2.1 users表不只是ID和姓名而是用戶生命周期的切片CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, age TINYINT CHECK (age BETWEEN 0 AND 120), is_vip BOOLEAN DEFAULT FALSE, reg_source ENUM(web, ios, android, wechat) NOT NULL, last_login_time DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );重點(diǎn)不在語(yǔ)法而在字段選擇邏輯reg_source用ENUM而非VARCHAR不是為了省那幾個(gè)字節(jié)而是強(qiáng)制約束數(shù)據(jù)來(lái)源渠道的合法性——業(yè)務(wù)系統(tǒng)里不可能出現(xiàn)reg_sourceqq這種臟數(shù)據(jù)枚舉值就是產(chǎn)品埋點(diǎn)規(guī)范的數(shù)據(jù)庫(kù)落地。last_login_time允許NULL是因?yàn)樾伦?cè)用戶可能從未登錄而created_at用TIMESTAMP自動(dòng)填充確保注冊(cè)時(shí)間不可篡改。這兩個(gè)時(shí)間字段的組合直接支撐“7日留存率”計(jì)算COUNT(DISTINCT CASE WHEN DATEDIFF(last_login_time, created_at) 6 THEN id END) / COUNT(*)。is_vip設(shè)為BOOLEAN但實(shí)際業(yè)務(wù)中VIP狀態(tài)往往關(guān)聯(lián)會(huì)員等級(jí)、到期時(shí)間、權(quán)益包這里簡(jiǎn)化成布爾值是為了聚焦練習(xí)目標(biāo)——當(dāng)你需要擴(kuò)展時(shí)自然會(huì)意識(shí)到該加vip_level TINYINT和vip_expire_date DATE。提示練習(xí)題里插入的100條users數(shù)據(jù)中有12條reg_sourcewechat且is_vipTRUE但last_login_time全部為空。這是模擬微信小程序靜默授權(quán)注冊(cè)后用戶未主動(dòng)登錄的典型場(chǎng)景。很多初學(xué)者寫(xiě)“活躍VIP用戶”查詢時(shí)會(huì)漏掉這個(gè)NULL判斷直接用WHERE last_login_time IS NOT NULL AND is_vipTRUE結(jié)果漏掉這批用戶。2.2 orders表狀態(tài)機(jī)驅(qū)動(dòng)的業(yè)務(wù)事實(shí)表CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, order_status ENUM(pending, paid, shipped, delivered, refunded, cancelled) NOT NULL DEFAULT pending, amount DECIMAL(10,2) NOT NULL, discount_amount DECIMAL(10,2) DEFAULT 0.00, created_at DATETIME NOT NULL, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (product_id) REFERENCES products(id) );這張表是練習(xí)題的“心臟”所有復(fù)雜查詢都繞不開(kāi)它order_status的ENUM值順序不是隨意排的pending→paid→shipped→delivered是正向流轉(zhuǎn)refunded和cancelled是終態(tài)。這意味著“已支付未發(fā)貨訂單”必須寫(xiě)成WHERE order_status IN (paid, shipped)而不是WHERE order_status ! pending——后者會(huì)錯(cuò)誤包含已退款訂單。amount和discount_amount分開(kāi)存儲(chǔ)是為了支持“滿300減50”這類營(yíng)銷活動(dòng)的獨(dú)立核算。練習(xí)題第27題要求“統(tǒng)計(jì)各渠道首單優(yōu)惠力度”就必須用SUM(discount_amount)/COUNT(*)而不是用AVG(amount)來(lái)倒推。updated_at的ON UPDATE機(jī)制讓“最近24小時(shí)訂單狀態(tài)變更”查詢變得簡(jiǎn)單WHERE updated_at DATE_SUB(NOW(), INTERVAL 1 DAY)。但要注意如果訂單狀態(tài)從shipped變deliveredupdated_at會(huì)更新而created_at不變——這是區(qū)分“新訂單”和“訂單更新”的關(guān)鍵。注意練習(xí)題數(shù)據(jù)中orders表有237條記錄其中order_statusrefunded的有19條但discount_amount全部為0。這是因?yàn)橥丝畎l(fā)生在支付后優(yōu)惠已核銷退款金額只退實(shí)付部分。很多人在寫(xiě)“退款訂單平均優(yōu)惠”時(shí)會(huì)誤用AVG(discount_amount)得到0而正確邏輯是關(guān)聯(lián)payments表本題未提供需自行補(bǔ)充思路。2.3 products表分類體系與庫(kù)存策略的映射CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, category_path VARCHAR(255) NOT NULL, price DECIMAL(10,2) NOT NULL, stock_quantity INT NOT NULL DEFAULT 0, on_sale_flag BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );category_path字段是點(diǎn)睛之筆它存儲(chǔ)的是/electronics/smartphones/iphone這樣的完整路徑而非簡(jiǎn)單的category_id。這直接支持“查看智能手機(jī)品類下所有iPhone型號(hào)的銷量TOP10”這類需求WHERE category_path LIKE /electronics/smartphones/iphone%。但這也帶來(lái)陷阱LIKE /electronics/%會(huì)匹配到/electronics/smartphones和/electronics/accessories而LIKE /electronics/smartphones%才精準(zhǔn)。練習(xí)題第41題“統(tǒng)計(jì)各一級(jí)品類銷售額”就要求你用SUBSTRING_INDEX(category_path, /, 3)提取/electronics部分再GROUP BY。stock_quantity設(shè)為INT而非TINYINT是因?yàn)閹?kù)存可能上萬(wàn)如圖書(shū)而on_sale_flag是布爾值但實(shí)際業(yè)務(wù)中促銷可能有多個(gè)維度限時(shí)、限量、滿減這里簡(jiǎn)化處理只為聚焦“是否在售”這一核心判斷。這三張表的關(guān)聯(lián)不是教科書(shū)式的“用戶-訂單-商品”三角而是帶著業(yè)務(wù)傷疤的真實(shí)關(guān)系orders.user_id外鍵指向users.id但users表里沒(méi)有total_spent字段——因?yàn)檫@個(gè)值應(yīng)該實(shí)時(shí)計(jì)算避免冗余更新失敗products表沒(méi)有sales_count因?yàn)殇N量要按時(shí)間維度聚合不能靜態(tài)存儲(chǔ)。練習(xí)題的答案SQL里所有SUM()、COUNT()、AVG()都不是為了湊數(shù)而是對(duì)這種“不冗余設(shè)計(jì)”的必然響應(yīng)。3. 題目分層從語(yǔ)法校驗(yàn)到業(yè)務(wù)推演的四階躍遷這套題的100道題不是線性排列的而是按認(rèn)知負(fù)荷嚴(yán)格分層。我把它拆成四個(gè)階段每個(gè)階段解決一類典型問(wèn)題對(duì)應(yīng)不同的思維模式切換。跳著做題可以但跳過(guò)某個(gè)階段后面就會(huì)卡住。3.1 第一階單表CRUD的“肌肉反射”訓(xùn)練題號(hào)1-25目標(biāo)不是學(xué)會(huì)語(yǔ)法而是讓基礎(chǔ)操作變成條件反射。比如題3“查詢年齡大于25歲的用戶郵箱按注冊(cè)時(shí)間倒序排列”SELECT email FROM users WHERE age 25 ORDER BY created_at DESC;看似簡(jiǎn)單但隱藏三個(gè)易錯(cuò)點(diǎn)ORDER BY created_at DESC不是last_login_time——題目明確要求“按注冊(cè)時(shí)間”新手常憑直覺(jué)選活躍時(shí)間WHERE age 25不是25——題目說(shuō)“大于”邊界必須精確結(jié)果只取email字段不加*——練習(xí)題強(qiáng)調(diào)“只查需要的字段”這是性能意識(shí)的起點(diǎn)。這一階的題答案SQL長(zhǎng)度通常不超過(guò)3行但每道題都強(qiáng)迫你檢查WHERE條件是否精準(zhǔn)ORDER BY字段是否存在SELECT是否最小化做完這25道你會(huì)發(fā)現(xiàn)自己寫(xiě)簡(jiǎn)單查詢時(shí)手指已經(jīng)自動(dòng)敲出SELECT ... FROM ... WHERE ... ORDER BY的節(jié)奏不用再想語(yǔ)法樹(shù)。3.2 第二階多表關(guān)聯(lián)的“關(guān)系破譯”實(shí)戰(zhàn)題號(hào)26-50進(jìn)入這里你開(kāi)始面對(duì)真實(shí)世界的混亂。題28“查詢所有已支付訂單的用戶名、商品名、訂單金額按金額從高到低排序”SELECT u.username, p.name, o.amount FROM orders o JOIN users u ON o.user_id u.id JOIN products p ON o.product_id p.id WHERE o.order_status paid ORDER BY o.amount DESC;關(guān)鍵在JOIN順序和條件位置必須用JOIN users和JOIN products不能用LEFT JOIN——因?yàn)轭}目要求“已支付訂單”意味著訂單必須關(guān)聯(lián)有效用戶和商品LEFT JOIN會(huì)引入NULL污染結(jié)果WHERE o.order_status paid必須放在JOIN之后如果寫(xiě)成JOIN users u ON o.user_id u.id AND o.order_status paid邏輯就錯(cuò)了這是在關(guān)聯(lián)時(shí)過(guò)濾訂單但o.order_status屬于orders表關(guān)聯(lián)條件只能用關(guān)聯(lián)字段如o.user_id u.id狀態(tài)過(guò)濾必須在WHEREORDER BY o.amount DESC明確指定表別名o.避免多表同名字段如users和products都有created_at的歧義。這一階的題答案SQL開(kāi)始出現(xiàn)表別名、顯式JOIN、WHERE條件分層。做完后你看到業(yè)務(wù)需求里的“用戶訂單商品”組合第一反應(yīng)不再是“我要連三張表”而是“哪張是主表關(guān)聯(lián)字段是什么過(guò)濾條件該放ON還是WHERE”。3.3 第三階聚合分析的“業(yè)務(wù)指標(biāo)”構(gòu)建題號(hào)51-75這里開(kāi)始燒腦。題53“統(tǒng)計(jì)每個(gè)用戶的訂單總數(shù)、總金額、平均客單價(jià)并篩選出總金額超過(guò)1000元的用戶”SELECT u.id, u.username, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount, ROUND(AVG(o.amount), 2) AS avg_order_amount FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.username HAVING SUM(o.amount) 1000;核心是理解GROUP BY和HAVING的協(xié)作用LEFT JOIN而非INNER JOIN因?yàn)橐白?cè)但從未下單的用戶”他們的order_count0total_amountNULLGROUP BY u.id, u.username必須包含SELECT中所有非聚合字段這是SQL92標(biāo)準(zhǔn)強(qiáng)制要求HAVING SUM(o.amount) 1000不能用WHERE——因?yàn)镾UM()是聚合函數(shù)WHERE在分組前執(zhí)行無(wú)法訪問(wèn)聚合結(jié)果HAVING在分組后執(zhí)行專為此設(shè)計(jì)ROUND(AVG(o.amount), 2)AVG對(duì)NULL自動(dòng)忽略所以未下單用戶的avg_order_amount是NULL符合業(yè)務(wù)邏輯。這一階的題答案SQL里必然出現(xiàn)GROUP BY、聚合函數(shù)、HAVING。做完后你讀到“復(fù)購(gòu)率”“LTV”“GMV”這些指標(biāo)詞腦子里自動(dòng)浮現(xiàn)對(duì)應(yīng)的SQL骨架先JOIN關(guān)聯(lián)再GROUP BY主體然后SUM/COUNT/AVG計(jì)算最后HAVING篩選。3.4 第四階高級(jí)特性的“業(yè)務(wù)推演”挑戰(zhàn)題號(hào)76-100這是拉開(kāi)差距的地方。題89“查詢每個(gè)用戶最近一次下單的時(shí)間、商品名、訂單狀態(tài)并標(biāo)記是否為VIP用戶”SELECT u.id, u.username, u.is_vip, latest_orders.last_order_time, p.name AS last_product_name, o.order_status AS last_order_status FROM users u JOIN ( SELECT user_id, MAX(created_at) AS last_order_time FROM orders GROUP BY user_id ) AS latest_orders ON u.id latest_orders.user_id JOIN orders o ON u.id o.user_id AND o.created_at latest_orders.last_order_time JOIN products p ON o.product_id p.id;這里用了三層嵌套子查詢latest_orders先算出每個(gè)用戶的最大created_at這是“最近一次”的定義主查詢用這個(gè)結(jié)果JOIN orders但必須加AND o.created_at latest_orders.last_order_time因?yàn)橐粋€(gè)用戶可能同秒下多單只取時(shí)間戳匹配的訂單最后JOIN products獲取商品名。更難的是題97“使用窗口函數(shù)為每個(gè)用戶的訂單按時(shí)間排序標(biāo)記首次下單、末次下單、以及是否為當(dāng)月首單”SELECT user_id, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_seq, FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order_time, LAST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_time, CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id, YEAR(created_at), MONTH(created_at) ORDER BY created_at) 1 THEN yes ELSE no END AS is_monthly_first_order FROM orders;窗口函數(shù)的難點(diǎn)在于理解PARTITION BY分組和ORDER BY組內(nèi)排序的雙重作用以及ROWS BETWEEN對(duì)LAST_VALUE的影響。這些題不是考你會(huì)不會(huì)寫(xiě)而是考你能不能把業(yè)務(wù)語(yǔ)言“當(dāng)月首單”精準(zhǔn)翻譯成SQL邏輯。做完這一階你再看業(yè)務(wù)方的需求文檔能直接腦內(nèi)編譯成SQL而不是先畫(huà)流程圖再翻譯。4. 答案解析的“防坑指南”為什么標(biāo)準(zhǔn)答案長(zhǎng)這樣很多人對(duì)照答案時(shí)只看SQL是否跑通卻忽略了答案里那些看似多余的細(xì)節(jié)。這些細(xì)節(jié)正是資深開(kāi)發(fā)者踩坑后留下的路標(biāo)。我們以題66為例“查詢訂單金額排名前10的用戶ID、用戶名、總金額并顯示其排名”SELECT u.id, u.username, SUM(o.amount) AS total_amount, ROW_NUMBER() OVER (ORDER BY SUM(o.amount) DESC) AS rank_num FROM users u JOIN orders o ON u.id o.user_id GROUP BY u.id, u.username ORDER BY total_amount DESC LIMIT 10;標(biāo)準(zhǔn)答案里藏著五個(gè)關(guān)鍵決策點(diǎn)每個(gè)都對(duì)應(yīng)一個(gè)常見(jiàn)誤區(qū)4.1 聚合函數(shù)的位置為什么SUM()在SELECT里不在WHERE里新手常寫(xiě)WHERE SUM(o.amount) 100這會(huì)報(bào)錯(cuò)。因?yàn)閃HERE在分組前執(zhí)行此時(shí)o.amount還是單條記錄沒(méi)有“總金額”概念。SUM()必須出現(xiàn)在SELECT或HAVING中這是SQL執(zhí)行順序決定的鐵律FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT。答案把SUM()放在SELECT既計(jì)算了總金額又為窗口函數(shù)ROW_NUMBER()提供了排序依據(jù)。4.2 排名函數(shù)的選擇為什么用ROW_NUMBER()而不是RANK()或DENSE_RANK()ROW_NUMBER()1,2,3,4,5無(wú)并列嚴(yán)格遞增RANK()1,2,2,4,5并列后跳過(guò)如兩個(gè)第2名則下一個(gè)為第4名DENSE_RANK()1,2,2,3,4并列后不跳過(guò) 題干說(shuō)“排名前10”未說(shuō)明并列如何處理默認(rèn)采用最嚴(yán)格的ROW_NUMBER()確保結(jié)果恰好10條。如果業(yè)務(wù)要求“銷售額并列第5名的用戶都算TOP10”才用RANK()。4.3 LIMIT的位置為什么放在最后而不是子查詢里有人會(huì)寫(xiě)子查詢SELECT * FROM (SELECT ... GROUP BY ... ORDER BY SUM() DESC) t LIMIT 10。這在邏輯上等價(jià)但效率更低子查詢要先算出所有用戶的總金額再排序再取10條而主查詢的LIMIT 10在最終結(jié)果集上截?cái)郙ySQL優(yōu)化器可能提前終止。標(biāo)準(zhǔn)答案的寫(xiě)法更符合執(zhí)行引擎的優(yōu)化路徑。4.4 字段別名的強(qiáng)制使用為什么AS rank_num不能省略在應(yīng)用層如Python的pandas讀取結(jié)果時(shí)列名是rank_num而不是ROW_NUMBER() OVER (...)這種長(zhǎng)字符串。省略AS會(huì)導(dǎo)致列名不可讀增加后續(xù)處理成本。練習(xí)題所有答案都強(qiáng)制使用有意義的別名這是生產(chǎn)環(huán)境的必備習(xí)慣。4.5 JOIN類型的選擇為什么用INNER JOIN而不是LEFT JOIN題干是“訂單金額排名”隱含前提是有訂單的用戶。如果用LEFT JOIN會(huì)把未下單用戶SUM(o.amount)NULL也納入ROW_NUMBER()會(huì)給NULL排一個(gè)序號(hào)污染TOP10結(jié)果。INNER JOIN是業(yè)務(wù)語(yǔ)義的顯式聲明我們只關(guān)心有交易行為的用戶。提示題66的答案SQL在MySQL 8.0可直接運(yùn)行但在5.7及以下版本不支持窗口函數(shù)。此時(shí)需用變量模擬rank : rank 1 AS rank_num。練習(xí)題答案頁(yè)專門標(biāo)注了各題的MySQL版本兼容性這是很多免費(fèi)題庫(kù)忽略的關(guān)鍵信息。5. 實(shí)戰(zhàn)復(fù)盤我在某電商項(xiàng)目中如何用這套題救火去年幫某電商團(tuán)隊(duì)優(yōu)化一個(gè)慢查詢背景是運(yùn)營(yíng)每天要導(dǎo)出“近7天高價(jià)值用戶清單”原SQL跑了12分鐘-- 原始SQL已脫敏 SELECT DISTINCT u.id, u.username, u.email FROM users u WHERE u.id IN ( SELECT user_id FROM orders WHERE created_at 2023-01-01 ) AND u.id IN ( SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) 5000 );這個(gè)SQL的問(wèn)題正是練習(xí)題第33、57、62題的綜合體。我打開(kāi)練習(xí)題文檔快速定位到對(duì)應(yīng)解法5.1 問(wèn)題定位三重嵌套重復(fù)掃描外層WHERE u.id IN (子查詢1)和u.id IN (子查詢2)導(dǎo)致users表被掃描兩次子查詢1SELECT user_id FROM orders WHERE created_at ...沒(méi)有索引全表掃描orders子查詢2GROUP BY user_id HAVING SUM(amount) 5000同樣全表掃描且GROUP BY開(kāi)銷大。5.2 重構(gòu)思路用練習(xí)題第68題的“單次聚合條件標(biāo)記”解法練習(xí)題第68題是“查詢近30天下單且總金額超2000的用戶返回ID、用戶名、總金額、訂單數(shù)”。它的答案是SELECT u.id, u.username, COALESCE(SUM(o.amount), 0) AS total_amount, COALESCE(COUNT(o.id), 0) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.created_at DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY u.id, u.username HAVING total_amount 2000;關(guān)鍵創(chuàng)新點(diǎn)LEFT JOIN ... AND o.created_at ...把時(shí)間過(guò)濾條件從WHERE移到JOIN的ON子句這樣即使用戶無(wú)近30天訂單也會(huì)保留用戶記錄SUM()返回NULLCOALESCE轉(zhuǎn)為0單次JOINGROUP BY避免多次子查詢HAVING直接過(guò)濾聚合結(jié)果。我依此重構(gòu)生產(chǎn)SQLSELECT u.id, u.username, u.email, COALESCE(SUM(o.amount), 0) AS total_amount, COALESCE(COUNT(o.id), 0) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.created_at DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY u.id, u.username, u.email HAVING total_amount 5000;執(zhí)行時(shí)間從12分鐘降到3.2秒。更關(guān)鍵的是我順手給orders.created_at和orders.user_id加了聯(lián)合索引這是練習(xí)題第15題“為高頻查詢字段添加索引”的實(shí)踐延伸。5.3 后續(xù)優(yōu)化用練習(xí)題第92題的“物化視圖”思路雖然快了但每天凌晨跑還是占資源。我參考練習(xí)題第92題“創(chuàng)建月度銷售匯總表”建議團(tuán)隊(duì)建一張user_weekly_value表每天凌晨用上述SQL刷新業(yè)務(wù)查詢直接讀這張輕量表。這比臨時(shí)計(jì)算快10倍且數(shù)據(jù)一致性更好。這套題的價(jià)值從來(lái)不是讓你記住100個(gè)答案而是當(dāng)你面對(duì)一個(gè)陌生的、帶著業(yè)務(wù)氣味的SQL需求時(shí)能迅速在腦中調(diào)出對(duì)應(yīng)的練習(xí)題編號(hào)知道“哦這題我做過(guò)解法是XXX要注意YYY”。它把零散的知識(shí)點(diǎn)織成了一張可檢索、可復(fù)用的網(wǎng)。6. 進(jìn)階建議如何把練習(xí)題變成你的SQL能力加速器做完100道題只是起點(diǎn)。真正的高手會(huì)用這套題做三件事建立自己的錯(cuò)題本、構(gòu)造業(yè)務(wù)變體題、反向設(shè)計(jì)練習(xí)表。這才是讓它真正長(zhǎng)在你身上的方法。6.1 動(dòng)手建一個(gè)“錯(cuò)題-原因-修正”三欄筆記不要只抄答案要記錄你卡住的瞬間。比如題44“查詢所有未下單用戶的用戶名和郵箱”你可能寫(xiě)了-- 你的錯(cuò)誤答案 SELECT username, email FROM users WHERE id NOT IN (SELECT user_id FROM orders);運(yùn)行發(fā)現(xiàn)結(jié)果為空但明明有用戶沒(méi)下單。原因SELECT user_id FROM orders返回了NULL值比如某條訂單user_id被誤設(shè)為NULL而NOT IN (..., NULL)永遠(yuǎn)為FALSE。這是SQL里經(jīng)典的NULL陷阱。正確答案是SELECT username, email FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL); -- 或更優(yōu)雅的LEFT JOIN SELECT u.username, u.email FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.user_id IS NULL;在你的錯(cuò)題本里記下錯(cuò)題編號(hào)44卡點(diǎn)描述NOT IN遇到NULL時(shí)邏輯失效根本原因SQL中任何與NULL的比較, !, IN, NOT IN都返回UNKNOWNWHERE只接受TRUE修正方案過(guò)濾NULL或改用LEFT JOIN關(guān)聯(lián)練習(xí)題題12NULL安全的COUNT、題31IS NULL/IS NOT NULL用法每周回顧錯(cuò)題本你會(huì)發(fā)現(xiàn)很多“新問(wèn)題”只是舊陷阱換個(gè)馬甲。6.2 給每道題加一個(gè)“業(yè)務(wù)變體”自己出題題22是“查詢訂單金額大于平均訂單金額的訂單ID、用戶ID、金額”。這是基礎(chǔ)版。你可以給自己加難度變體1時(shí)間維度“查詢2023年Q4訂單金額大于該季度平均訂單金額的訂單”——需要加WHERE時(shí)間過(guò)濾再用子查詢算季度均值變體2關(guān)聯(lián)維度“查詢iPhone品類下訂單金額大于該品類平均訂單金額的訂單”——需要JOIN products表用category_path篩選再算品類均值變體3動(dòng)態(tài)閾值“查詢訂單金額大于其用戶歷史平均訂單金額的訂單”——要用窗口函數(shù)AVG(amount) OVER (PARTITION BY user_id)。自己出題的過(guò)程就是把知識(shí)點(diǎn)從“識(shí)別”升級(jí)到“生成”的過(guò)程。當(dāng)你能設(shè)計(jì)出變體題說(shuō)明你已經(jīng)吃透了底層邏輯。6.3 反向設(shè)計(jì)基于業(yè)務(wù)需求倒推練習(xí)表結(jié)構(gòu)假設(shè)你現(xiàn)在負(fù)責(zé)一個(gè)在線教育平臺(tái)需要統(tǒng)計(jì)“課程完課率”。你會(huì)怎么設(shè)計(jì)練習(xí)表courses表course_id, name, total_lessons總課時(shí)users表user_id, usernameuser_course_progress表user_id, course_id, completed_lessons已完成課時(shí)last_access_time然后設(shè)計(jì)題“查詢完課率completed_lessons/total_lessons大于80%的課程名稱、完課率、學(xué)習(xí)用戶數(shù)”。這個(gè)過(guò)程會(huì)讓你深刻理解表結(jié)構(gòu)設(shè)計(jì)不是技術(shù)問(wèn)題而是業(yè)務(wù)抽象能力的體現(xiàn)。練習(xí)題的users/orders/products是電商范式你得學(xué)會(huì)把它遷移到教育、醫(yī)療、物流等任何領(lǐng)域。最后分享一個(gè)小技巧把練習(xí)題答案SQL里的所有關(guān)鍵字SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT用不同顏色高亮打印出來(lái)貼在顯示器邊框。每次寫(xiě)SQL前掃一眼這個(gè)“語(yǔ)法羅盤”肌肉記憶會(huì)越來(lái)越強(qiáng)。SQL不是靠背出來(lái)的是靠一遍遍寫(xiě)、一遍遍錯(cuò)、一遍遍修正刻進(jìn)手指和大腦的。 MySQL實(shí)戰(zhàn)練習(xí)題：電商三表設(shè)計(jì)與四階SQL能力躍遷
1. 為什么這套MySQL練習(xí)題能真正幫你“把語(yǔ)法刻進(jìn)肌肉里”我?guī)н^(guò)不少剛學(xué)完SQL基礎(chǔ)的開(kāi)發(fā)者他們能背出SELECT、WHERE、JOIN的語(yǔ)法結(jié)構(gòu)也能在紙上寫(xiě)出簡(jiǎn)單的查詢語(yǔ)句但一到真實(shí)業(yè)務(wù)場(chǎng)景——比如要從訂單表里找出“近30天下單次數(shù)≥5次、且平均客單價(jià)超過(guò)200元的老客戶”就卡在中間不知道該先篩時(shí)間還是先分組不確定HAVING和WHERE到底誰(shuí)管誰(shuí)更別提怎么把用戶行為路徑瀏覽→加購(gòu)→下單→復(fù)購(gòu)用窗口函數(shù)串起來(lái)。這不是記不住是沒(méi)在真實(shí)數(shù)據(jù)流里反復(fù)“擰螺絲”。這套練習(xí)題的設(shè)計(jì)邏輯就是從這個(gè)斷層切入的。它不叫“MySQL入門100題”也不叫“面試高頻50問(wèn)”它叫“練習(xí)表題目答案”——三個(gè)要素缺一不可。練習(xí)表不是隨便造的假數(shù)據(jù)而是模擬了電商、內(nèi)容平臺(tái)、SaaS系統(tǒng)三類高頻業(yè)務(wù)域的真實(shí)字段設(shè)計(jì)邏輯用戶表里有is_vip、last_login_time、reg_source訂單表里有order_status含pending,shipped,refunded等狀態(tài)機(jī)字段、payment_method、discount_amount商品表里有category_path如/electronics/smartphones/iphone、stock_quantity、on_sale_flag。這些字段不是為了炫技而是你明天寫(xiě)報(bào)表、做AB測(cè)試、搭用戶分群時(shí)真正在數(shù)據(jù)庫(kù)里要面對(duì)的。關(guān)鍵詞里雖然空著但整套題的底層錨點(diǎn)非常清晰以“可執(zhí)行的最小閉環(huán)”為單位組織訓(xùn)練。每道題都自帶可運(yùn)行的建表語(yǔ)句、INSERT數(shù)據(jù)腳本、標(biāo)準(zhǔn)答案SQL、以及關(guān)鍵注釋——不是只告訴你“答案是SELECT * FROM users WHERE age 18”而是標(biāo)注“此處WHERE必須在GROUP BY之前執(zhí)行否則會(huì)報(bào)錯(cuò)1140”不是只給JOIN結(jié)果而是說(shuō)明“LEFT JOIN users ON orders.user_id users.id 比 INNER JOIN多返回37條記錄因?yàn)榇嬖谖醋?cè)用戶直接下單的灰度場(chǎng)景”。這種顆粒度才能讓練習(xí)從“看懂”走向“手熟”再走向“直覺(jué)反應(yīng)”。它適合三類人剛學(xué)完增刪改查想驗(yàn)證理解的新人準(zhǔn)備技術(shù)面試需要快速刷題的求職者還有像我這樣隔半年不碰復(fù)雜查詢手生到連GROUP_CONCAT的分隔符都要查文檔的“老手”。如果你現(xiàn)在打開(kāi)MySQL客戶端對(duì)著空白命令行發(fā)呆超過(guò)10秒這套題就是你的重啟開(kāi)關(guān)。2. 練習(xí)表設(shè)計(jì)背后的業(yè)務(wù)邏輯與數(shù)據(jù)陷阱很多人拿到練習(xí)題第一反應(yīng)是跳過(guò)建表語(yǔ)句直接抄答案。這恰恰踩中了第一個(gè)大坑不理解表結(jié)構(gòu)等于在迷霧中開(kāi)槍打中什么全靠運(yùn)氣。這套題的三張核心練習(xí)表——users用戶、orders訂單、products商品——每個(gè)字段名、每個(gè)約束、每條測(cè)試數(shù)據(jù)都藏著業(yè)務(wù)規(guī)則的影子。我們拆開(kāi)來(lái)看。2.1 users表不只是ID和姓名而是用戶生命周期的切片CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, age TINYINT CHECK (age BETWEEN 0 AND 120), is_vip BOOLEAN DEFAULT FALSE, reg_source ENUM(web, ios, android, wechat) NOT NULL, last_login_time DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );重點(diǎn)不在語(yǔ)法而在字段選擇邏輯reg_source用ENUM而非VARCHAR不是為了省那幾個(gè)字節(jié)而是強(qiáng)制約束數(shù)據(jù)來(lái)源渠道的合法性——業(yè)務(wù)系統(tǒng)里不可能出現(xiàn)reg_sourceqq這種臟數(shù)據(jù)枚舉值就是產(chǎn)品埋點(diǎn)規(guī)范的數(shù)據(jù)庫(kù)落地。last_login_time允許NULL是因?yàn)樾伦?cè)用戶可能從未登錄而created_at用TIMESTAMP自動(dòng)填充確保注冊(cè)時(shí)間不可篡改。這兩個(gè)時(shí)間字段的組合直接支撐“7日留存率”計(jì)算COUNT(DISTINCT CASE WHEN DATEDIFF(last_login_time, created_at) 6 THEN id END) / COUNT(*)。is_vip設(shè)為BOOLEAN但實(shí)際業(yè)務(wù)中VIP狀態(tài)往往關(guān)聯(lián)會(huì)員等級(jí)、到期時(shí)間、權(quán)益包這里簡(jiǎn)化成布爾值是為了聚焦練習(xí)目標(biāo)——當(dāng)你需要擴(kuò)展時(shí)自然會(huì)意識(shí)到該加vip_level TINYINT和vip_expire_date DATE。提示練習(xí)題里插入的100條users數(shù)據(jù)中有12條reg_sourcewechat且is_vipTRUE但last_login_time全部為空。這是模擬微信小程序靜默授權(quán)注冊(cè)后用戶未主動(dòng)登錄的典型場(chǎng)景。很多初學(xué)者寫(xiě)“活躍VIP用戶”查詢時(shí)會(huì)漏掉這個(gè)NULL判斷直接用WHERE last_login_time IS NOT NULL AND is_vipTRUE結(jié)果漏掉這批用戶。2.2 orders表狀態(tài)機(jī)驅(qū)動(dòng)的業(yè)務(wù)事實(shí)表CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, order_status ENUM(pending, paid, shipped, delivered, refunded, cancelled) NOT NULL DEFAULT pending, amount DECIMAL(10,2) NOT NULL, discount_amount DECIMAL(10,2) DEFAULT 0.00, created_at DATETIME NOT NULL, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (product_id) REFERENCES products(id) );這張表是練習(xí)題的“心臟”所有復(fù)雜查詢都繞不開(kāi)它order_status的ENUM值順序不是隨意排的pending→paid→shipped→delivered是正向流轉(zhuǎn)refunded和cancelled是終態(tài)。這意味著“已支付未發(fā)貨訂單”必須寫(xiě)成WHERE order_status IN (paid, shipped)而不是WHERE order_status ! pending——后者會(huì)錯(cuò)誤包含已退款訂單。amount和discount_amount分開(kāi)存儲(chǔ)是為了支持“滿300減50”這類營(yíng)銷活動(dòng)的獨(dú)立核算。練習(xí)題第27題要求“統(tǒng)計(jì)各渠道首單優(yōu)惠力度”就必須用SUM(discount_amount)/COUNT(*)而不是用AVG(amount)來(lái)倒推。updated_at的ON UPDATE機(jī)制讓“最近24小時(shí)訂單狀態(tài)變更”查詢變得簡(jiǎn)單WHERE updated_at DATE_SUB(NOW(), INTERVAL 1 DAY)。但要注意如果訂單狀態(tài)從shipped變deliveredupdated_at會(huì)更新而created_at不變——這是區(qū)分“新訂單”和“訂單更新”的關(guān)鍵。注意練習(xí)題數(shù)據(jù)中orders表有237條記錄其中order_statusrefunded的有19條但discount_amount全部為0。這是因?yàn)橥丝畎l(fā)生在支付后優(yōu)惠已核銷退款金額只退實(shí)付部分。很多人在寫(xiě)“退款訂單平均優(yōu)惠”時(shí)會(huì)誤用AVG(discount_amount)得到0而正確邏輯是關(guān)聯(lián)payments表本題未提供需自行補(bǔ)充思路。2.3 products表分類體系與庫(kù)存策略的映射CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, category_path VARCHAR(255) NOT NULL, price DECIMAL(10,2) NOT NULL, stock_quantity INT NOT NULL DEFAULT 0, on_sale_flag BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );category_path字段是點(diǎn)睛之筆它存儲(chǔ)的是/electronics/smartphones/iphone這樣的完整路徑而非簡(jiǎn)單的category_id。這直接支持“查看智能手機(jī)品類下所有iPhone型號(hào)的銷量TOP10”這類需求WHERE category_path LIKE /electronics/smartphones/iphone%。但這也帶來(lái)陷阱LIKE /electronics/%會(huì)匹配到/electronics/smartphones和/electronics/accessories而LIKE /electronics/smartphones%才精準(zhǔn)。練習(xí)題第41題“統(tǒng)計(jì)各一級(jí)品類銷售額”就要求你用SUBSTRING_INDEX(category_path, /, 3)提取/electronics部分再GROUP BY。stock_quantity設(shè)為INT而非TINYINT是因?yàn)閹?kù)存可能上萬(wàn)如圖書(shū)而on_sale_flag是布爾值但實(shí)際業(yè)務(wù)中促銷可能有多個(gè)維度限時(shí)、限量、滿減這里簡(jiǎn)化處理只為聚焦“是否在售”這一核心判斷。這三張表的關(guān)聯(lián)不是教科書(shū)式的“用戶-訂單-商品”三角而是帶著業(yè)務(wù)傷疤的真實(shí)關(guān)系orders.user_id外鍵指向users.id但users表里沒(méi)有total_spent字段——因?yàn)檫@個(gè)值應(yīng)該實(shí)時(shí)計(jì)算避免冗余更新失敗products表沒(méi)有sales_count因?yàn)殇N量要按時(shí)間維度聚合不能靜態(tài)存儲(chǔ)。練習(xí)題的答案SQL里所有SUM()、COUNT()、AVG()都不是為了湊數(shù)而是對(duì)這種“不冗余設(shè)計(jì)”的必然響應(yīng)。3. 題目分層從語(yǔ)法校驗(yàn)到業(yè)務(wù)推演的四階躍遷這套題的100道題不是線性排列的而是按認(rèn)知負(fù)荷嚴(yán)格分層。我把它拆成四個(gè)階段每個(gè)階段解決一類典型問(wèn)題對(duì)應(yīng)不同的思維模式切換。跳著做題可以但跳過(guò)某個(gè)階段后面就會(huì)卡住。3.1 第一階單表CRUD的“肌肉反射”訓(xùn)練題號(hào)1-25目標(biāo)不是學(xué)會(huì)語(yǔ)法而是讓基礎(chǔ)操作變成條件反射。比如題3“查詢年齡大于25歲的用戶郵箱按注冊(cè)時(shí)間倒序排列”SELECT email FROM users WHERE age 25 ORDER BY created_at DESC;看似簡(jiǎn)單但隱藏三個(gè)易錯(cuò)點(diǎn)ORDER BY created_at DESC不是last_login_time——題目明確要求“按注冊(cè)時(shí)間”新手常憑直覺(jué)選活躍時(shí)間WHERE age 25不是25——題目說(shuō)“大于”邊界必須精確結(jié)果只取email字段不加*——練習(xí)題強(qiáng)調(diào)“只查需要的字段”這是性能意識(shí)的起點(diǎn)。這一階的題答案SQL長(zhǎng)度通常不超過(guò)3行但每道題都強(qiáng)迫你檢查WHERE條件是否精準(zhǔn)ORDER BY字段是否存在SELECT是否最小化做完這25道你會(huì)發(fā)現(xiàn)自己寫(xiě)簡(jiǎn)單查詢時(shí)手指已經(jīng)自動(dòng)敲出SELECT ... FROM ... WHERE ... ORDER BY的節(jié)奏不用再想語(yǔ)法樹(shù)。3.2 第二階多表關(guān)聯(lián)的“關(guān)系破譯”實(shí)戰(zhàn)題號(hào)26-50進(jìn)入這里你開(kāi)始面對(duì)真實(shí)世界的混亂。題28“查詢所有已支付訂單的用戶名、商品名、訂單金額按金額從高到低排序”SELECT u.username, p.name, o.amount FROM orders o JOIN users u ON o.user_id u.id JOIN products p ON o.product_id p.id WHERE o.order_status paid ORDER BY o.amount DESC;關(guān)鍵在JOIN順序和條件位置必須用JOIN users和JOIN products不能用LEFT JOIN——因?yàn)轭}目要求“已支付訂單”意味著訂單必須關(guān)聯(lián)有效用戶和商品LEFT JOIN會(huì)引入NULL污染結(jié)果WHERE o.order_status paid必須放在JOIN之后如果寫(xiě)成JOIN users u ON o.user_id u.id AND o.order_status paid邏輯就錯(cuò)了這是在關(guān)聯(lián)時(shí)過(guò)濾訂單但o.order_status屬于orders表關(guān)聯(lián)條件只能用關(guān)聯(lián)字段如o.user_id u.id狀態(tài)過(guò)濾必須在WHEREORDER BY o.amount DESC明確指定表別名o.避免多表同名字段如users和products都有created_at的歧義。這一階的題答案SQL開(kāi)始出現(xiàn)表別名、顯式JOIN、WHERE條件分層。做完后你看到業(yè)務(wù)需求里的“用戶訂單商品”組合第一反應(yīng)不再是“我要連三張表”而是“哪張是主表關(guān)聯(lián)字段是什么過(guò)濾條件該放ON還是WHERE”。3.3 第三階聚合分析的“業(yè)務(wù)指標(biāo)”構(gòu)建題號(hào)51-75這里開(kāi)始燒腦。題53“統(tǒng)計(jì)每個(gè)用戶的訂單總數(shù)、總金額、平均客單價(jià)并篩選出總金額超過(guò)1000元的用戶”SELECT u.id, u.username, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount, ROUND(AVG(o.amount), 2) AS avg_order_amount FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.username HAVING SUM(o.amount) 1000;核心是理解GROUP BY和HAVING的協(xié)作用LEFT JOIN而非INNER JOIN因?yàn)橐白?cè)但從未下單的用戶”他們的order_count0total_amountNULLGROUP BY u.id, u.username必須包含SELECT中所有非聚合字段這是SQL92標(biāo)準(zhǔn)強(qiáng)制要求HAVING SUM(o.amount) 1000不能用WHERE——因?yàn)镾UM()是聚合函數(shù)WHERE在分組前執(zhí)行無(wú)法訪問(wèn)聚合結(jié)果HAVING在分組后執(zhí)行專為此設(shè)計(jì)ROUND(AVG(o.amount), 2)AVG對(duì)NULL自動(dòng)忽略所以未下單用戶的avg_order_amount是NULL符合業(yè)務(wù)邏輯。這一階的題答案SQL里必然出現(xiàn)GROUP BY、聚合函數(shù)、HAVING。做完后你讀到“復(fù)購(gòu)率”“LTV”“GMV”這些指標(biāo)詞腦子里自動(dòng)浮現(xiàn)對(duì)應(yīng)的SQL骨架先JOIN關(guān)聯(lián)再GROUP BY主體然后SUM/COUNT/AVG計(jì)算最后HAVING篩選。3.4 第四階高級(jí)特性的“業(yè)務(wù)推演”挑戰(zhàn)題號(hào)76-100這是拉開(kāi)差距的地方。題89“查詢每個(gè)用戶最近一次下單的時(shí)間、商品名、訂單狀態(tài)并標(biāo)記是否為VIP用戶”SELECT u.id, u.username, u.is_vip, latest_orders.last_order_time, p.name AS last_product_name, o.order_status AS last_order_status FROM users u JOIN ( SELECT user_id, MAX(created_at) AS last_order_time FROM orders GROUP BY user_id ) AS latest_orders ON u.id latest_orders.user_id JOIN orders o ON u.id o.user_id AND o.created_at latest_orders.last_order_time JOIN products p ON o.product_id p.id;這里用了三層嵌套子查詢latest_orders先算出每個(gè)用戶的最大created_at這是“最近一次”的定義主查詢用這個(gè)結(jié)果JOIN orders但必須加AND o.created_at latest_orders.last_order_time因?yàn)橐粋€(gè)用戶可能同秒下多單只取時(shí)間戳匹配的訂單最后JOIN products獲取商品名。更難的是題97“使用窗口函數(shù)為每個(gè)用戶的訂單按時(shí)間排序標(biāo)記首次下單、末次下單、以及是否為當(dāng)月首單”SELECT user_id, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_seq, FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order_time, LAST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_time, CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id, YEAR(created_at), MONTH(created_at) ORDER BY created_at) 1 THEN yes ELSE no END AS is_monthly_first_order FROM orders;窗口函數(shù)的難點(diǎn)在于理解PARTITION BY分組和ORDER BY組內(nèi)排序的雙重作用以及ROWS BETWEEN對(duì)LAST_VALUE的影響。這些題不是考你會(huì)不會(huì)寫(xiě)而是考你能不能把業(yè)務(wù)語(yǔ)言“當(dāng)月首單”精準(zhǔn)翻譯成SQL邏輯。做完這一階你再看業(yè)務(wù)方的需求文檔能直接腦內(nèi)編譯成SQL而不是先畫(huà)流程圖再翻譯。4. 答案解析的“防坑指南”為什么標(biāo)準(zhǔn)答案長(zhǎng)這樣很多人對(duì)照答案時(shí)只看SQL是否跑通卻忽略了答案里那些看似多余的細(xì)節(jié)。這些細(xì)節(jié)正是資深開(kāi)發(fā)者踩坑后留下的路標(biāo)。我們以題66為例“查詢訂單金額排名前10的用戶ID、用戶名、總金額并顯示其排名”SELECT u.id, u.username, SUM(o.amount) AS total_amount, ROW_NUMBER() OVER (ORDER BY SUM(o.amount) DESC) AS rank_num FROM users u JOIN orders o ON u.id o.user_id GROUP BY u.id, u.username ORDER BY total_amount DESC LIMIT 10;標(biāo)準(zhǔn)答案里藏著五個(gè)關(guān)鍵決策點(diǎn)每個(gè)都對(duì)應(yīng)一個(gè)常見(jiàn)誤區(qū)4.1 聚合函數(shù)的位置為什么SUM()在SELECT里不在WHERE里新手常寫(xiě)WHERE SUM(o.amount) 100這會(huì)報(bào)錯(cuò)。因?yàn)閃HERE在分組前執(zhí)行此時(shí)o.amount還是單條記錄沒(méi)有“總金額”概念。SUM()必須出現(xiàn)在SELECT或HAVING中這是SQL執(zhí)行順序決定的鐵律FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT。答案把SUM()放在SELECT既計(jì)算了總金額又為窗口函數(shù)ROW_NUMBER()提供了排序依據(jù)。4.2 排名函數(shù)的選擇為什么用ROW_NUMBER()而不是RANK()或DENSE_RANK()ROW_NUMBER()1,2,3,4,5無(wú)并列嚴(yán)格遞增RANK()1,2,2,4,5并列后跳過(guò)如兩個(gè)第2名則下一個(gè)為第4名DENSE_RANK()1,2,2,3,4并列后不跳過(guò) 題干說(shuō)“排名前10”未說(shuō)明并列如何處理默認(rèn)采用最嚴(yán)格的ROW_NUMBER()確保結(jié)果恰好10條。如果業(yè)務(wù)要求“銷售額并列第5名的用戶都算TOP10”才用RANK()。4.3 LIMIT的位置為什么放在最后而不是子查詢里有人會(huì)寫(xiě)子查詢SELECT * FROM (SELECT ... GROUP BY ... ORDER BY SUM() DESC) t LIMIT 10。這在邏輯上等價(jià)但效率更低子查詢要先算出所有用戶的總金額再排序再取10條而主查詢的LIMIT 10在最終結(jié)果集上截?cái)郙ySQL優(yōu)化器可能提前終止。標(biāo)準(zhǔn)答案的寫(xiě)法更符合執(zhí)行引擎的優(yōu)化路徑。4.4 字段別名的強(qiáng)制使用為什么AS rank_num不能省略在應(yīng)用層如Python的pandas讀取結(jié)果時(shí)列名是rank_num而不是ROW_NUMBER() OVER (...)這種長(zhǎng)字符串。省略AS會(huì)導(dǎo)致列名不可讀增加后續(xù)處理成本。練習(xí)題所有答案都強(qiáng)制使用有意義的別名這是生產(chǎn)環(huán)境的必備習(xí)慣。4.5 JOIN類型的選擇為什么用INNER JOIN而不是LEFT JOIN題干是“訂單金額排名”隱含前提是有訂單的用戶。如果用LEFT JOIN會(huì)把未下單用戶SUM(o.amount)NULL也納入ROW_NUMBER()會(huì)給NULL排一個(gè)序號(hào)污染TOP10結(jié)果。INNER JOIN是業(yè)務(wù)語(yǔ)義的顯式聲明我們只關(guān)心有交易行為的用戶。提示題66的答案SQL在MySQL 8.0可直接運(yùn)行但在5.7及以下版本不支持窗口函數(shù)。此時(shí)需用變量模擬rank : rank 1 AS rank_num。練習(xí)題答案頁(yè)專門標(biāo)注了各題的MySQL版本兼容性這是很多免費(fèi)題庫(kù)忽略的關(guān)鍵信息。5. 實(shí)戰(zhàn)復(fù)盤我在某電商項(xiàng)目中如何用這套題救火去年幫某電商團(tuán)隊(duì)優(yōu)化一個(gè)慢查詢背景是運(yùn)營(yíng)每天要導(dǎo)出“近7天高價(jià)值用戶清單”原SQL跑了12分鐘-- 原始SQL已脫敏 SELECT DISTINCT u.id, u.username, u.email FROM users u WHERE u.id IN ( SELECT user_id FROM orders WHERE created_at 2023-01-01 ) AND u.id IN ( SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) 5000 );這個(gè)SQL的問(wèn)題正是練習(xí)題第33、57、62題的綜合體。我打開(kāi)練習(xí)題文檔快速定位到對(duì)應(yīng)解法5.1 問(wèn)題定位三重嵌套重復(fù)掃描外層WHERE u.id IN (子查詢1)和u.id IN (子查詢2)導(dǎo)致users表被掃描兩次子查詢1SELECT user_id FROM orders WHERE created_at ...沒(méi)有索引全表掃描orders子查詢2GROUP BY user_id HAVING SUM(amount) 5000同樣全表掃描且GROUP BY開(kāi)銷大。5.2 重構(gòu)思路用練習(xí)題第68題的“單次聚合條件標(biāo)記”解法練習(xí)題第68題是“查詢近30天下單且總金額超2000的用戶返回ID、用戶名、總金額、訂單數(shù)”。它的答案是SELECT u.id, u.username, COALESCE(SUM(o.amount), 0) AS total_amount, COALESCE(COUNT(o.id), 0) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.created_at DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY u.id, u.username HAVING total_amount 2000;關(guān)鍵創(chuàng)新點(diǎn)LEFT JOIN ... AND o.created_at ...把時(shí)間過(guò)濾條件從WHERE移到JOIN的ON子句這樣即使用戶無(wú)近30天訂單也會(huì)保留用戶記錄SUM()返回NULLCOALESCE轉(zhuǎn)為0單次JOINGROUP BY避免多次子查詢HAVING直接過(guò)濾聚合結(jié)果。我依此重構(gòu)生產(chǎn)SQLSELECT u.id, u.username, u.email, COALESCE(SUM(o.amount), 0) AS total_amount, COALESCE(COUNT(o.id), 0) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.created_at DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY u.id, u.username, u.email HAVING total_amount 5000;執(zhí)行時(shí)間從12分鐘降到3.2秒。更關(guān)鍵的是我順手給orders.created_at和orders.user_id加了聯(lián)合索引這是練習(xí)題第15題“為高頻查詢字段添加索引”的實(shí)踐延伸。5.3 后續(xù)優(yōu)化用練習(xí)題第92題的“物化視圖”思路雖然快了但每天凌晨跑還是占資源。我參考練習(xí)題第92題“創(chuàng)建月度銷售匯總表”建議團(tuán)隊(duì)建一張user_weekly_value表每天凌晨用上述SQL刷新業(yè)務(wù)查詢直接讀這張輕量表。這比臨時(shí)計(jì)算快10倍且數(shù)據(jù)一致性更好。這套題的價(jià)值從來(lái)不是讓你記住100個(gè)答案而是當(dāng)你面對(duì)一個(gè)陌生的、帶著業(yè)務(wù)氣味的SQL需求時(shí)能迅速在腦中調(diào)出對(duì)應(yīng)的練習(xí)題編號(hào)知道“哦這題我做過(guò)解法是XXX要注意YYY”。它把零散的知識(shí)點(diǎn)織成了一張可檢索、可復(fù)用的網(wǎng)。6. 進(jìn)階建議如何把練習(xí)題變成你的SQL能力加速器做完100道題只是起點(diǎn)。真正的高手會(huì)用這套題做三件事建立自己的錯(cuò)題本、構(gòu)造業(yè)務(wù)變體題、反向設(shè)計(jì)練習(xí)表。這才是讓它真正長(zhǎng)在你身上的方法。6.1 動(dòng)手建一個(gè)“錯(cuò)題-原因-修正”三欄筆記不要只抄答案要記錄你卡住的瞬間。比如題44“查詢所有未下單用戶的用戶名和郵箱”你可能寫(xiě)了-- 你的錯(cuò)誤答案 SELECT username, email FROM users WHERE id NOT IN (SELECT user_id FROM orders);運(yùn)行發(fā)現(xiàn)結(jié)果為空但明明有用戶沒(méi)下單。原因SELECT user_id FROM orders返回了NULL值比如某條訂單user_id被誤設(shè)為NULL而NOT IN (..., NULL)永遠(yuǎn)為FALSE。這是SQL里經(jīng)典的NULL陷阱。正確答案是SELECT username, email FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL); -- 或更優(yōu)雅的LEFT JOIN SELECT u.username, u.email FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.user_id IS NULL;在你的錯(cuò)題本里記下錯(cuò)題編號(hào)44卡點(diǎn)描述NOT IN遇到NULL時(shí)邏輯失效根本原因SQL中任何與NULL的比較, !, IN, NOT IN都返回UNKNOWNWHERE只接受TRUE修正方案過(guò)濾NULL或改用LEFT JOIN關(guān)聯(lián)練習(xí)題題12NULL安全的COUNT、題31IS NULL/IS NOT NULL用法每周回顧錯(cuò)題本你會(huì)發(fā)現(xiàn)很多“新問(wèn)題”只是舊陷阱換個(gè)馬甲。6.2 給每道題加一個(gè)“業(yè)務(wù)變體”自己出題題22是“查詢訂單金額大于平均訂單金額的訂單ID、用戶ID、金額”。這是基礎(chǔ)版。你可以給自己加難度變體1時(shí)間維度“查詢2023年Q4訂單金額大于該季度平均訂單金額的訂單”——需要加WHERE時(shí)間過(guò)濾再用子查詢算季度均值變體2關(guān)聯(lián)維度“查詢iPhone品類下訂單金額大于該品類平均訂單金額的訂單”——需要JOIN products表用category_path篩選再算品類均值變體3動(dòng)態(tài)閾值“查詢訂單金額大于其用戶歷史平均訂單金額的訂單”——要用窗口函數(shù)AVG(amount) OVER (PARTITION BY user_id)。自己出題的過(guò)程就是把知識(shí)點(diǎn)從“識(shí)別”升級(jí)到“生成”的過(guò)程。當(dāng)你能設(shè)計(jì)出變體題說(shuō)明你已經(jīng)吃透了底層邏輯。6.3 反向設(shè)計(jì)基于業(yè)務(wù)需求倒推練習(xí)表結(jié)構(gòu)假設(shè)你現(xiàn)在負(fù)責(zé)一個(gè)在線教育平臺(tái)需要統(tǒng)計(jì)“課程完課率”。你會(huì)怎么設(shè)計(jì)練習(xí)表courses表course_id, name, total_lessons總課時(shí)users表user_id, usernameuser_course_progress表user_id, course_id, completed_lessons已完成課時(shí)last_access_time然后設(shè)計(jì)題“查詢完課率completed_lessons/total_lessons大于80%的課程名稱、完課率、學(xué)習(xí)用戶數(shù)”。這個(gè)過(guò)程會(huì)讓你深刻理解表結(jié)構(gòu)設(shè)計(jì)不是技術(shù)問(wèn)題而是業(yè)務(wù)抽象能力的體現(xiàn)。練習(xí)題的users/orders/products是電商范式你得學(xué)會(huì)把它遷移到教育、醫(yī)療、物流等任何領(lǐng)域。最后分享一個(gè)小技巧把練習(xí)題答案SQL里的所有關(guān)鍵字SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT用不同顏色高亮打印出來(lái)貼在顯示器邊框。每次寫(xiě)SQL前掃一眼這個(gè)“語(yǔ)法羅盤”肌肉記憶會(huì)越來(lái)越強(qiáng)。SQL不是靠背出來(lái)的是靠一遍遍寫(xiě)、一遍遍錯(cuò)、一遍遍修正刻進(jìn)手指和大腦的。