数据库的查询题做过不少但像“10-142 6-4 查询厂商D生产的PC和便携式电脑的平均价格”这种题每次拿出来给新人做考核都能炸出一堆问题。不是题目本身有多难而是它把SQL里最常用的三个能力——多表关联、结果集合并、聚合计算——全揉在了一句话里。你以为你懂了JOIN懂了AVG真上手一写发现要么结果对不上要么连“平均价格”到底应该怎么理解都没想清楚。这道题出自经典的数据库课程习题集数据模型是很多教材里都会用的“厂商-产品-型号”三表结构。表面上是查平均值实际上考的是你能不能在设计不规整的表结构里把数据捞对。今天我就以这道题为主线把从建表、造数、写SQL、验证结果到踩坑排查的完整过程拆开讲一遍顺便聊几个工程上真正用得上的写法。1. 题目背后这个查询题到底在考你什么1.1 经典的产品-型号-分类数据模型先还原一下题目对应的表结构。这个题的原始数据模型来自斯坦福大学的数据库公开课练习也被国内很多教材引用了一共三张表Product产品、PC台式机、Laptop便携式电脑。Product表保存的是“型号”和“厂商”的对应关系字段主要有三个maker厂商名称题目里用单个大写字母表示比如A、B、C、Dmodel型号编号是全表唯一的主键type产品类型取值一般是pc、laptop、printerPC表和Laptop表分别保存两类电脑的具体配置和价格。PC表有model、speed主频、ram内存、hd硬盘、price价格Laptop表在这基础上多一个screen屏幕尺寸字段。这个模型在设计上很典型也很有教学意义。Product表相当于一个“登记处”只记录型号属于谁、是什么类型具体数据则按类型拆到不同业务表里。这样设计的好处是打印机、PC、笔记本各自的属性不一样没必要硬塞在一张表里但它带来的问题也很明显——你想查一个厂商的全部产品必须跨表查甚至要多次跨表。1.2 题目里的三个隐藏考点很多新手看到“查询厂商D生产的PC和便携式电脑的平均价格”第一反应是这题简单AVG套一个条件不就行了。真上手才发现厂商D不在PC表里也不在Laptop表里而是在Product表里。你得先通过Product表找到厂商D有哪些型号再拿着这些型号去PC表和Laptop表里找价格。这里隐藏着三个核心考点。第一个是多表关联。型号是Product表的主键也是PC表、Laptop表的外键。厂商和价格之间隔了一层必须通过JOIN或子查询把两张表串起来。第二个是结果集合并。题目说的是“PC和便携式电脑”的平均价格Desktop机和笔记本的价格在两张不同的表里。想算一个总平均值就得把两个表的数据纵向合并想分别看两类产品的平均值又得各自聚合后再合并结果。这个“合并”不是JOIN而是UNION或UNION ALL很多人在这里翻车。第三个是聚合计算。AVG本身不难难的是搞清楚AVG的作用范围。是只对PC算平均值还是只对笔记本算平均值还是把两种产品放一起算一个平均值题目表述有歧义实际工作中也经常遇到这种“需求一句话、理解各不同”的情况。这道题能把这三个点一次性练扎实而且还不涉及窗口函数、CASE WHEN这些进阶语法非常适合当入门到进阶的过渡题。2. 实操第一步建表、造数、看清数据形态2.1 建表语句与设计考量做题之前先把环境准备好。我用的是MySQL的语法写的但除了个别细节这套表结构和SQL语句在SQL Server、PostgreSQL、Oracle上都能跑顶多调整一下字符串类型和自增语法。CREATE TABLE Product ( maker VARCHAR(10), model VARCHAR(20) PRIMARY KEY, type VARCHAR(10) ); CREATE TABLE PC ( model VARCHAR(20) PRIMARY KEY, speed DECIMAL(6,2), ram INT, hd INT, price DECIMAL(8,2) ); CREATE TABLE Laptop ( model VARCHAR(20) PRIMARY KEY, speed DECIMAL(6,2), ram INT, hd INT, screen DECIMAL(4,1), price DECIMAL(8,2) );这里有个工程上的细节值得说Product表里model为什么是主键因为在真实的企业数据里一个型号在不同场景下会出现多条记录比如同一个型号卖到不同地区但在这种练习模型里model就是“一个型号对应一个产品配置”的唯一标识。把model设为主键能有效防止重复数据。另外PC表和Laptop表的model既是主键同时也是指向Product表的外键。你建表的时候可以加上FOREIGN KEY约束但很多练习环境里不加原因是不想让学生在一开始就被外键约束带来的插入顺序问题卡住。我自己练习时一般会加上因为能顺手练到“先插主表再插子表”的习惯。2.2 插入测试数据造数这一步别偷懒数据的质量和覆盖度直接决定你验证SQL时能不能发现问题。我按经典的教材数据来刻意让厂商D拥有2台PC和3台笔记本方便你手算验证。INSERT INTO Product VALUES (A, 1001, pc), (A, 1002, pc), (A, 1003, pc), (A, 2001, laptop), (A, 2002, laptop), (B, 1004, pc), (B, 1005, pc), (B, 2003, laptop), (C, 1006, pc), (C, 2004, laptop), (D, 1007, pc), (D, 1008, pc), (D, 2005, laptop), (D, 2006, laptop), (D, 2007, laptop);INSERT INTO PC VALUES (1001, 2.66, 1024, 250, 2114), (1002, 2.10, 512, 250, 995), (1003, 1.42, 512, 80, 478), (1004, 2.80, 1024, 250, 649), (1005, 3.20, 2048, 320, 629), (1006, 2.20, 1024, 200, 989), (1007, 2.20, 1024, 200, 789), (1008, 2.66, 2048, 250, 849);INSERT INTO Laptop VALUES (2001, 2.00, 2048, 240, 20.1, 1899), (2002, 1.73, 1024, 160, 17.0, 1499), (2003, 1.80, 512, 60, 15.4, 549), (2004, 2.00, 1024, 120, 15.4, 799), (2005, 2.16, 1024, 120, 17.0, 1099), (2006, 2.00, 2048, 160, 15.4, 949), (2007, 1.83, 1024, 80, 13.3, 679);数据插完后先做一步验证把厂商D的型号和PC、Laptop的价格手工列出来。厂商D的PC是1007和1008价格分别是789和849笔记本是2005、2006、2007价格分别是1099、949、679。所以PC平均价是(789849)/2819笔记本平均价是(1099949679)/3909混合平均价是(7898491099949679)/5873。后面SQL跑出来的结果必须和这三个数对上对不上就是写错了。2.3 用最简单查询验证数据形态正式写题之前先把数据捞出来看一眼。这一步看着简单作用非常大——它能帮你确认JOIN的键有没有问题、厂商D到底有哪些型号、价格字段有没有NULL。SELECT p.maker, p.model, p.type, pc.price FROM Product p LEFT JOIN PC pc ON p.model pc.model WHERE p.maker D AND p.type pc;执行完你能看到两行记录PC表对应的价格都在没有一个NULL。如果哪一行price是NULL说明数据没配对或者本来就有缺值这时候直接求AVG结果会把你坑惨。因为AVG会忽略NULL值你以为是5行平均实际它可能只用了4行。这一步还顺手帮你验证了LEFT JOIN的方向。这里用LEFT JOIN而不是INNER JOIN是因为我想看到Product表里所有厂商D的PC型号即使PC表里找不到对应配置也能显示出来。实际排查的时候LEFT JOIN能帮你发现“Product表里登记了型号但PC表里没配置数据”这种脏数据。3. 核心查询三种写法与对比分析3.1 方案一UNION ALL合并后统一求平均先解决“PC和便携式电脑的平均价格”里最容易产生歧义的问题这两种理解都合理。一种是算一个混合平均价另一种是分别显示PC平均价和笔记本平均价。先把这两种写法都跑通。如果需求是“厂商D所有PC和笔记本放在一起平均价格是多少”逻辑上需要两步先把厂商D的PC价格和笔记本价格纵向合并成一个临时结果集再对这个结果集求AVG。SQL非常直观SELECT AVG(all_price) AS avg_price FROM ( SELECT pc.price AS all_price FROM PC pc JOIN Product p ON pc.model p.model WHERE p.maker D AND p.type pc UNION ALL SELECT laptop.price AS all_price FROM Laptop laptop JOIN Product p ON laptop.model p.model WHERE p.maker D AND p.type laptop ) AS t;执行结果应该是873.0000。关键点就在UNION ALL而不是UNION。这两个操作的区别是UNION会去掉两个结果集之间的重复行而UNION ALL原样保留所有行。如果厂商D恰好有一台PC和一台笔记本价格完全一样用UNION会把两条记录合并成一条平均价就被算错了。在处理“合并明细求聚合”的场景下99%的情况都应该用UNION ALL只有明确需要去重时才用UNION。3.2 方案二分别求平均再用UNION ALL合并结果现在换一种理解方式需求可能是“分别看一下PC的平均价格和笔记本的平均价格”。这时要分两步走每一步先按类型把对应表中的数据聚合再把两个聚合结果合并成一个结果集。SELECT PC AS product_type, AVG(pc.price) AS avg_price FROM PC pc JOIN Product p ON pc.model p.model WHERE p.maker D AND p.type pc UNION ALL SELECT Laptop AS product_type, AVG(laptop.price) AS avg_price FROM Laptop laptop JOIN Product p ON laptop.model p.model WHERE p.maker D AND p.type laptop;执行结果应该是一个两行两列的结果集第一行PC、819第二行Laptop、909。这个写法比方案一的附加价值在于它不只是算一个数而是把两类产品分别列出来了。业务上这种“分组看指标”的需求更多比如运营想看台式机和笔记本各自的均价以便决定下个月的进货策略。题目如果没有明确说“合并成一个数”我个人的习惯是优先用这种分别统计的写法信息量更大也更贴近真实需求。两种方案的比例关系其实能反映一条数据规律厂商D的PC均价低于笔记本均价混合均价873落在了819和909之间接近笔记本均价一侧因为笔记本的数量更多。AVG本质上就是所有值的总和除以行数哪一类产品行数多最终结果就会往哪一侧偏移。3.3 方案三JOIN多表关联的写法还有一种更常见的写法是直接在JOIN之后对同一张结果集做聚合。比如单独算PC平均价SQL可以更简短SELECT AVG(pc.price) AS avg_pc_price FROM PC pc JOIN Product p ON pc.model p.model WHERE p.maker D;注意这里我没有在WHERE里写p.type pc为什么因为PC表里存的数据本身就全是PCPC表的model不会出现在Laptop表里。也就是说能和你当前连接的PC表匹配上的Product记录type必然是pc。从结果正确性来说不加type条件没毛病。但问题在于如果某一天Product表里出现了一条typeprinter的打印机型号恰好这个型号也出现在PC表里数据录入错误不写type条件就会把打印机价格混进PC平均价里。所以我的建议是写SQL时不能让结果依赖“数据恰好是干净的”该加的条件还是加上。宁可多写一个条件也不要把正确性押在数据质量上。那能不能一条SQL同时算PC和笔记本的均价呢可以不借助UNION但写法比较绕。比较常规的做法是分别查两次再在应用层合并或者用条件聚合——把两种价格分别放进CASE WHEN里SELECT AVG(CASE WHEN p.type pc THEN pc.price END) AS avg_pc_price, AVG(CASE WHEN p.type laptop THEN laptop.price END) AS avg_laptop_price FROM Product p LEFT JOIN PC pc ON p.model pc.model LEFT JOIN Laptop laptop ON p.model laptop.model WHERE p.maker D;这条语句在工程里偶尔能看到它的特点是“宽表化”处理先把Product同时LEFT JOIN到PC和Laptop两张表上形成一行包含两种价格其中一种为NULL的中间结果再用条件AVG分别计算。AVG会忽略NULL所以能正确算出两个数。但它有个隐藏风险如果用INNER JOIN而不是LEFT JOINPC和Laptop表匹配上的行会产生笛卡尔积同一台PC会和多台笔记本配对结果直接翻车。用LEFT JOIN能把这种风险压到最低但中间结果里会出现PC价格那一列有值、Laptop价格那一列是NULL的情况逻辑清晰的人写这玩意儿没问题新手容易看得云里雾里所以我更推荐前面那两种UNION ALL写法。3.4 三种方案对比到底选哪种把三种方案放到一起对比一下就清楚了方案写法特点适用场景结果形式UNION ALL合并后AVG先纵向合并明细再统一聚合需求要求“总平均价”不区分产品类型单行单列分别AVG再UNION ALL每类产品各自聚合再合并结果需求要求“分别看PC和笔记本均价”一行一类产品JOIN后用CASE WHEN一把梭把两种价格放同一行结果是宽表方便直接导出报表单行多列从工程实用性的角度我首选方案二其次方案一。因为方案二的信息量最大它既能看出PC均价也能看出笔记本均价你如果还想看两者加起来的均价在结果集外面再套一层AVG反而更灵活。方案三在结果展示上最直观但SQL的理解成本高而且LEFT JOIN双表在数据量大的时候容易让中间结果膨胀性能上不占优。4. 验证与排查结果对不上怎么办4.1 常见错误与排查方法我在带新人做这道题的时候见到最多的错误不是语法错误而是“逻辑错误”——SQL能跑结果就是不对。汇总一下最常见的几类问题第一类忘了多一层JOIN。有新人直接写SELECT AVG(price) FROM PC WHERE maker DPC表里根本没有maker字段直接报错这还算好的。更隐蔽的是PC表里恰好有个maker字段现实库表经常这么设计但压根没连Product表这时候结果可能对也可能是错的你根本分辨不出来。第二类用IN子查询把方向搞反。比如先查厂商D的型号再查价格写成SELECT AVG(price) FROM PC WHERE model IN (SELECT model FROM Product WHERE maker D)。这个写法本身没问题但如果你把IN里查出来的结果反过来用比如变成WHERE maker IN (SELECT ...)那结果多半是错的。第三类UNION ALL写成了UNION。前面说过UNION会去重。在求平均的场景里去重就是灾难。比如厂商D有一台PC价格是800一台笔记本价格也是800你用UNION合并800只保留一条最后结果分母少了一个数平均价自然偏差。排查这类问题我的办法很笨但很有效把每步中间结果先单独跑出来再逐步验证。别指望一次写出最终SQL先跑JOIN确认行数对不对再跑UNION确认是否故意重复合并最后才是AVG。这个“逐步拆解”的习惯比任何调优技巧都重要。4.2 一个很容易踩的坑字段类型不一致这道题本身不涉及复杂类型转换但我在工程版本里踩过类似的坑一并说一下。UNION合并两个查询时要求两侧的列数量和类型兼容。如果PC表的price是DECIMAL(8,2)Laptop表的price是DECIMAL(6,1)MySQL会自动做隐式转换合并成高精度类型问题不大。但如果你把price拼了字符串进去比如想显示单位“美元”一边是数值一边是文本UNION直接报错。真实业务里更常见的是PC表的价格单位是美元Laptop表的价格单位是人民币两张表没有统一币种你直接UNION算平均值出来的数字没有任何意义。做多表合并前必须确认度量单位一致。这道练习题的数据是齐的但实际工作里这种“表结构字段看着一样语义完全不同”的情况太多了。字段类型、精度、单位都要先对齐再谈查询。4.3 性能问题IN子查询 vs JOIN这道题数据量小IN子查询和JOIN的执行时间都接近0毫秒感觉不出区别。但放到生产环境面对几十万行的PC表和几十万行的Product表写法不同执行计划可能天差地别。经验法则能用JOIN尽量用JOIN少用IN子查询。尤其是子查询里的表数据量很大的时候MySQL的优化器要把子查询结果全部物化出来再用哈希或嵌套循环去匹配外层查询而JOIN的方式可以让优化器在两表之间选择更合适的连接算法。具体到这个题JOIN的写法对索引的使用也更友好因为PC表和Laptop表的model是主键JOIN能直接走主键索引。另外注意一个细节写JOIN时给表起别名查询里尽量写全限定列名比如pc.price不要只写一个price。否则两表JOIN时如果都有price字段数据库会报Column price in field list is ambiguous这个错误就算不报错阅读SQL的人也容易混淆到底取的哪张表的price。5. 从这道题延伸出去工程里的统计查询怎么写5.1 场景扩展一按厂商分组汇总题目只让你查厂商D但实际业务往往是“每个厂商的均价是多少”。把WHERE条件换成GROUP BY一条SQL搞定SELECT p.maker, AVG(CASE WHEN p.type pc THEN pc.price END) AS avg_pc_price, AVG(CASE WHEN p.type laptop THEN laptop.price END) AS avg_laptop_price FROM Product p LEFT JOIN PC pc ON p.model pc.model LEFT JOIN Laptop laptop ON p.model laptop.model WHERE p.type IN (pc, laptop) GROUP BY p.maker;执行后能直观看到A、B、C、D每个厂商的台式机和笔记本平均价对比。这种写法报表需求里很常见比如老板想看“哪些厂商的笔记本均价高哪些厂商走的是低价路线”一张宽表就能讲清楚。这里有个小坑要提醒GROUP BY的分组字段是p.maker而SELECT里出现的非聚合字段只有p.maker这在SQL标准里是允许的。如果在SELECT里写了别名比如AVG(...) AS avg_price排序或者外层引用时可以直接用这个别名但WHERE里不能直接用别名得写完整的表达式或重查一层。5.2 场景扩展二按时间段统计价格变化原题没有时间字段但实际业务里的价格是动态的。加一个日期字段比如sale_date就可以统计某段时间内厂商D产品的平均成交价SELECT DATE_FORMAT(sale_date, %Y-%m) AS sale_month, AVG(price) AS avg_price FROM ( SELECT model, price, sale_date FROM PC UNION ALL SELECT model, price, sale_date FROM Laptop ) t WHERE model IN (SELECT model FROM Product WHERE maker D) GROUP BY DATE_FORMAT(sale_date, %Y-%m) ORDER BY sale_month;这个例子展示了一个核心思路先用UNION ALL把PC和Laptop合并成一张逻辑上的“全部产品价格流水表”再统一做筛选和聚合。这道练习题其实就是这个思路的简化版。真实数据里两张表的字段往往更多合并时只挑需要的列不要让无关字段干扰计算。5.3 场景扩展三结合窗口函数看明细和均值如果你的数据库版本支持窗口函数MySQL 8.0、SQL Server 2012都支持还能在保留每条明细的同时把平均价格放到每一行上SELECT p.maker, p.model, p.type, t.price, AVG(t.price) OVER (PARTITION BY p.type) AS type_avg_price FROM ( SELECT model, price, pc AS type FROM PC UNION ALL SELECT model, price, laptop AS type FROM Laptop ) t JOIN Product p ON t.model p.model WHERE p.maker D ORDER BY t.type, p.model;执行结果里每一行都会带着自己所属类型PC或Laptop的平均价格。这样你既能看到每一款产品的具体价格又能立刻对比出它比同类平均水平高还是低。窗口函数不减少行数和GROUP BY是两种思路GROUP BY把多行压成一行窗口函数却保留每行明细适合做“明细汇总”同时展示的场景。我实际在做价格分析报表时非常依赖这种写法。比如发现某款笔记本价格是1200它所在类型的平均价格是1000那它明显高于平均水平可能是高配机型也可能是定价策略有问题。这个信息在普通GROUP BY里是拿不到的。写在最后这道题练完你的SQL会上一个台阶题目本身很小但每次给新人讲这道题我都会让他们把三种方案全部写一遍再做一遍手算验证。为什么因为这道题覆盖的是SQL查询里最核心的三块能力多表JOIN、结果集合并、聚合计算。这三种能力在真实开发中几乎天天都会用到但它们组合在一起的场景并不多这道题恰好把它们串起来了。我个人在实际操作中的一个体会是写多表统计题先画数据流再写SQL。你先说清楚“厂商D的产品型号怎么来、价格怎么来、要按什么聚合”再落到代码上出错的概率会小很多。很多人一上来就写SQL写一半发现表关系搞错了又推倒重来反而更慢。再分享一个小技巧验证SQL结果时不要只盯着“跑出来了”就完事。拿出笔把少量数据手算一遍跟SQL结果对一下。这道题的数据量故意设计得很少就是为了方便你手算。你把这5个数字PC两台的789和849笔记本三台的1099、949和679手算清楚再去看SQL结果正确与否一目了然。这个习惯带到工作上能帮你少交很多“看起来没问题、实际上算错”的报表。
