初学SQL的时候大家十有八九都碰到过这样一道题“查询厂商D生产的PC和便携式电脑的平均价格”。题目看着简短真正写起来才发现它把多表关联、子查询、聚合函数、UNION这些核心知识点全串到了一起。我在带新人做数据查询的时候也经常拿它当试金石——能一口气把这道题完整写对的人SQL基础基本都不会差。说白了这道题表面上是在算平均价格实际上是在考你能不能把“厂商-产品型号-具体配置表”这种经典的多表关系理清楚。PC是一张表便携式电脑又是一张表价格字段分布在不同表里怎么把厂商D的全部设备捞出来求平均值中间有好几个容易踩的细节。这篇文章我会把这道题从表结构、SQL写法到执行计划完整拆一遍并且把我在实际写查询时踩过的坑一并分享出来。不管你是正在学SQL的学生还是做报表开发的新人这几个知识点都会用得上。1. 先把题目读懂它到底在问什么1.1 经典产品模型Product、PC、Laptop三张表这道题出自经典的数据库习题集通常默认存在下面这几张表。看懂了它们之间的关联SQL怎么写自然就清楚了。表名字段作用Product产品maker、model、type记录厂商、型号、产品类型相当于“主数据”PC台式电脑model、speed、ram、hd、price记录台式机的配置与价格Laptop便携式电脑model、speed、ram、hd、screen、price记录笔记本的配置与价格其中关键关联字段是model型号它在Product表中是主键同时也出现在PC表和Laptop表中。你在PC表里看到某个model去Product表里查就能知道它是哪家厂商生产的属于PC还是Laptop还是Printer打印机。这里要注意一个设计上的细节PC和Laptop有很多共同属性比如速度、内存、硬盘、价格但Laptop多了一个“屏幕尺寸”字段。为什么不全塞到一张表里因为如果强行合并成一张“电脑表”很多PC的screen字段会是NULL会平白多出一堆空缺值浪费存储也容易让查询逻辑变复杂。这种“一个产品主表 多个子类明细表”的结构在真实业务系统里非常常见比如电商的“商品主表 手机详情表 家电详情表”就是这么设计的。用生活化的比喻来理解这三张表的关系Product表就像“车型名录”只登记了车型代号、品牌、车型类别PC表和Laptop表则是“具体配置单和报价单”同一款车汽油版和混动版配置不同得分开记录。题目说“查询厂商D生产的PC和便携式电脑”意思是先去“车型名录”里找到厂商D名下属于“PC”和“Laptop”两个类别的车型然后去对应的“报价单”里查价格。1.2 “平均价格”到底让人怎么理解题目里的“平均价格”其实有一个语言上的歧义是厂商D的PC平均价格和便携式电脑平均价格分别计算各返回一行结果还是把他生产的PC和便携式电脑的所有价格混在一起算一个总平均价格这两种理解对应着完全不同的SQL写法。我翻过不少教材和题库这道题的标准答案大多采用第一种理解分别求出PC的平均价格、便携式电脑的平均价格返回两行结果。原因也很简单题目特意用了“和”字把两类产品并列出来而且PC和Laptop本来就在不同表中按类型分开统计更符合出题人的本意。在企业实际业务里这种“指标口径”问题特别重要。业务方说“帮我算一下A品类的平均售价”你得追问一句是每个单品求平均还是用总销售额除以总销量是剔除异常订单还是包含退款题目的字面歧义在真实世界里往往就是需求评审时要确认的关键细节。所以在本文里我会把两种语义对应的写法都给你你根据场景选。2. 三种SQL解法逐一手写2.1 解法一子查询 UNION ALL分开统计两类平均价格如果按“分别统计”的语义标准写法是这样的以MySQL方言为例SELECT PC AS product_type, AVG(price) AS avg_price FROM PC WHERE model IN ( SELECT model FROM Product WHERE maker D AND type PC ) UNION ALL SELECT Laptop AS product_type, AVG(price) AS avg_price FROM Laptop WHERE model IN ( SELECT model FROM Product WHERE maker D AND type Laptop );这段SQL拆开看其实不复杂。第一条SELECT先在Product表里找出厂商为D、类型为PC的所有model得到厂商D的台式机型号集合然后用IN把这个集合交给PC表过滤出这些型号的价格最后用AVG(price)求平均值。第二条SELECT对Laptop表做同样的事只是type条件换成了Laptop。两条SELECT各返回一行结果用UNION ALL拼在一起。这里有第一个大坑为什么是UNION ALL而不是UNIONUNION会自动去重UNION ALL则把结果原样合并。假如厂商D生产的某款PC价格碰巧和某款Laptop价格相同比如都是7999元用UNION会把这两行同价的结果合并成一行最终结果只有一行丢失一个类别。而UNION ALL不管重复与否永远保留所有行逻辑上更安全。这个题里根本没有去重的需求所以别用UNION。2.2 解法二合并价格集合后统一求平均如果你跟需求方确认过他确实想要“所有PC和便携式电脑合起来的总平均价格”那解法二是更合适的SELECT AVG(price) AS avg_price FROM ( SELECT price FROM PC WHERE model IN ( SELECT model FROM Product WHERE maker D AND type PC ) UNION ALL SELECT price FROM Laptop WHERE model IN ( SELECT model FROM Product WHERE maker D AND type Laptop ) ) AS all_products;这个写法的思路是先把厂商D的PC价格和Laptop价格分别查出来用UNION ALL拼成一个“只有一列price”的临时结果集然后在这个结果集上直接AVG。它得到的是一个数值而不是两行。我在这类SQL里遇到新手最常犯的错是内层UNION ALL时不愿意把PC和Laptop都映射成同样的列结构。比如PC表有priceLaptop表也有price但一个新手可能只SELECT model忘记SELECT price或者在内层查询里带上了speed这种PC表有、Laptop表不一定需要的字段。记住一个原则UNION的左右两侧查询出来的列数必须一致且对应列语义一致。在这个场景下两侧最核心的公共列就是price。2.3 解法三用JOIN替代IN的子查询写法子查询写起来直观但有些同学更习惯用JOIN。用JOIN实现“分别统计”的版本是这样的SELECT PC AS product_type, AVG(pc.price) AS avg_price FROM Product p JOIN PC pc ON p.model pc.model WHERE p.maker D AND p.type PC UNION ALL SELECT Laptop AS product_type, AVG(lp.price) AS avg_price FROM Product p JOIN Laptop lp ON p.model lp.model WHERE p.maker D AND p.type Laptop;这段SQL从Product出发通过model关联PC表先把厂商D的所有台式机配置行取出来再按类型聚合求平均。逻辑上和子查询版本等价但写法上更显式你可以清楚看到“先JOIN、再过滤、后聚合”的处理流程。那什么时候用IN什么时候用JOIN在小数据量的习题环境里两者性能差异基本可以忽略。但在生产环境数据量大时JOIN通常更容易让优化器做出好的执行计划尤其是能在model字段上命中索引时效率会明显更优。不过IN在语义表达上更贴近“找出满足条件的集合”这一思维模式初学者更容易理解。我的建议是逻辑优先性能靠执行计划验证不要为了“炫技”选复杂写法。2.4 三种解法怎么选解法语义返回结果适用场景解法一子查询 UNION ALL分别统计两行教材标准答案、按类别报表解法二合并价格后AVG整体统计一个数值总平均口径的指标解法三JOIN UNION ALL分别统计两行生产环境数据量大时更推荐你可以看到解法一和解法三本质上是在实现同一种语义区别只是关联方式。解法二则是另一种语义。写任何SQL之前先把口径确定好比动手敲代码更重要。3. 实操记录建表、造数、跑查询、看执行计划3.1 准备一套完整的测试数据和建表脚本纸上谈兵没意思我直接给你一套完整的MySQL 8.0测试脚本你复制到自己环境里就能跑。表结构按经典习题模型来。CREATE TABLE Product ( maker CHAR(1), model CHAR(4) PRIMARY KEY, type VARCHAR(10) ); CREATE TABLE PC ( model CHAR(4) PRIMARY KEY, speed DECIMAL(6,2), ram INT, hd INT, price DECIMAL(8,2) ); CREATE TABLE Laptop ( model CHAR(4) PRIMARY KEY, speed DECIMAL(6,2), ram INT, hd INT, screen DECIMAL(4,1), price DECIMAL(8,2) );接着插入一批样本数据。为了让结果好验证我特意让厂商D拥有两台PC和两台Laptop价格分别为INSERT INTO Product VALUES (A, 1001, PC), (D, 1002, PC), (D, 1003, PC), (D, 2001, Laptop), (D, 2002, Laptop), (B, 2003, Laptop), (C, 3001, Printer); INSERT INTO PC VALUES (1001, 2.66, 1024, 250, 6999), (1002, 2.10, 512, 160, 5499), (1003, 2.80, 2048, 320, 7999); INSERT INTO Laptop VALUES (2001, 1.73, 1024, 80, 8999), (2002, 1.60, 512, 60, 7999), (2003, 1.80, 2048, 160, 7499);样本数据里厂商D的PC是1002和1003价格分别是5499、7999平均是6749厂商D的Laptop是2001和2002价格分别是8999、7999平均是8499。如果按整体平均四个价格加起来30496除以4是7624。这三个数先记住后面验证结果用。3.2 实际执行三种查询观察结果把解法一扔到命令行里执行结果应该是product_type | avg_price PC | 6749.0000 Laptop | 8499.0000解法二的结果avg_price 7624.0000解法三的结果和解法一完全一致。这三个数字和我们手算的完全对得上说明查询逻辑正确。我建议你自己动手验算一遍而不仅仅是看SQL跑通了就完事——很多初学SQL的人查询跑出来不知道对不对就是因为没有在样本数据上做过人工预期。如果这是面试题面试官大概率会追问你“这个结果你怎么验证”你能脱口说出“因为厂商D有两台PC价格是5499和7999所以平均是6749”这比背一百条SQL语法都管用。3.3 EXPLAIN怎么看数据量小别纠结数据量大才看差异写完查询我顺手执行了一下EXPLAIN观察MySQL是怎么执行这三条SQL的。在小数据量的测试环境里三种写法的执行计划几乎没有区别都是“先扫描Product表过滤maker和type条件然后去PC或Laptop表里按主键取值”。优化器很聪明它会自动把IN子查询改写成类似半连接semi join的执行方式。但在生产环境里数据量一旦上到千万级执行计划就会开始出现分化。这时候有几个点值得关注Product表的maker、type字段上有没有联合索引如果没有每次查询都要全表扫Product代价很高。PC表和Laptop表的model字段是不是主键或唯一索引如果是JOIN的驱动顺序合理性能会很好。如果子查询结果集本身很大IN的列表会很长优化器可能倾向于改写为EXISTS或JOIN你可以通过EXPLAIN看到rows的估算值来判断。我通常的建议是先在语义上把SQL写对再用真实数据量在测试环境验证性能。不要把时间花在臆测“IN和JOIN哪个更快”上——数据量不一样结论很可能反过来。4. 新手最容易踩的5个坑4.1 直接把PC和Laptop同时JOIN搞出笛卡尔积有个常见的错误写法是这样的一个查询里同时JOIN PC表和Laptop表想在JOIN的结果里既看到PC价格又看到Laptop价格。比如SELECT p.maker, AVG(pc.price), AVG(lp.price) FROM Product p LEFT JOIN PC pc ON p.model pc.model LEFT JOIN Laptop lp ON p.model lp.model WHERE p.maker D GROUP BY p.maker;这种写法在数据上会出现严重的行数膨胀。因为同一个厂商名下有多台PC和多台Laptop时LEFT JOIN会把PC表和Laptop表的行做笛卡尔交叉产生许多根本不存在的关联组合最终AVG算出来的结果完全错误。除非你的数据恰好每个厂商只有一台PC和一台Laptop否则千万别这么干。这个坑的本质是“多表JOIN时关联粒度不一致”。一张主表同时关联两张子表如果子表与主表都是1对多关系那么两条关联路径会互相放大结果集。这是写复杂报表SQL最危险的一个问题我在工作中见过不止一次最后都得靠拆成多条SQL或用子查询解决。4.2 AVG遇到NULL结果比想象中“偏高”AVG聚合函数有一个容易忽略的行为它会忽略NULL值只对非NULL行求平均。假设厂商D有一台PC价格字段是NULL你执行AVG(price)计算时分母不会包含那台PC得到的是“已知价格PC”的平均值而不是“所有PC”的平均值。如果业务上要求把价格为空的产品按0元算你需要显式处理SELECT AVG(COALESCE(price, 0)) AS avg_price FROM PC WHERE model IN ( SELECT model FROM Product WHERE maker D AND type PC );COALESCE会把NULL转成0这样计算时价格为空的那台PC也会被计入分母。到底用哪种方式同样取决于指标口径。在报表口径中这是非常容易引发争议的点——“为什么平均价格比预想的高”往往就是因为在AVG时把NULL行排除了。4.3 忘了在子查询里加type条件我在解法一的SQL里特意写了两次type条件一次在PC对应的子查询里写typePC一次在Laptop对应的子查询里写typeLaptop。如果漏掉它逻辑上就会出问题。举个具体场景如果你的IN列表写的是“厂商D的所有model”没有限定type而在PC表查询时MySQL拿这个列表去匹配PC.model。正常情况下Laptop的model不会出现在PC表里所以结果可能碰巧是对的但这纯属数据“配合”得好。如果PC表里恰好有某个model和Product表中的Laptop型号一样真实PC和“伪Laptop型号”同时出现你的结果就错了。所以别偷懒该加的type条件必须加。这条规则在真实业务系统里同样成立关联表的过滤条件一定要写全否则你的查询依赖的是“恰好没有脏数据”的运气。4.4 用DISTINCT去重价格把平均算错了这种错误比较隐蔽。有的同学会在AVG里写DISTINCT用来去掉重复价格SELECT AVG(DISTINCT price) FROM PC WHERE model IN (...);看起来好像没什么但如果厂商D有两台PC价格恰好都是7999元这里就会先对价格去重把两个7999压成一个7999再计算平均值分母从2变成了1。这不就错了吗AVG(DISTINCT column)确实有它的用途比如求“平均有几档不同价格”之类但绝大多数业务场景下我们想算的是所有产品的真实平均价格同一价格出现多次就代表有多条记录不该被去重。记住只要不是在处理重复数据问题AVG里不要随便加DISTINCT。4.5 厂商字母的大小写、空格与排序规则最后一个坑看起来很小实际很致命。题目里的厂商“D”是一个字符串在SQL里必须用单引号包裹WHERE maker D但有时候你写WHERE maker d也会查出结果——这取决于数据库的排序规则。MySQL默认的utf8_general_ci是大小写不敏感的所以d能匹配DOracle的默认排序规则对CHAR类型的比较通常是大小写敏感的写错大小写就查不到数据。另外一个细节如果maker字段是CHAR类型存储时长度不足会自动用空格补齐比如CHAR(1)存的D就是一个单字符还好但如果字段是CHAR(10)存的D会自动变成D 带9个空格。如果上游ETL对数据做了TRIM可能没影响但如果没处理你直接比较makerD可能就匹配不上。遇到这种问题在字段上做TRIM可以临时解决但更根本的办法是让数据入库时就保证干净。5. 从习题到实战这类查询在企业报表里的三种变形5.1 按厂商分组求平均GROUP BY JOIN是最常用场景这道题再往前走一步就是“查询每个厂商生产的PC和便携式电脑的平均价格”。这时候就需要GROUP BY按厂商分组。整体思路是把所有价格集合和厂商信息关联在一起再加一个分组维度SELECT p.maker, t.product_type, AVG(t.price) AS avg_price FROM ( SELECT model, price, PC AS product_type FROM PC UNION ALL SELECT model, price, Laptop AS product_type FROM Laptop ) t JOIN Product p ON p.model t.model GROUP BY p.maker, t.product_type ORDER BY p.maker;这个写法把PC和Laptop先合并成一张“宽表”一样的临时集合再和Product关联之后分组聚合。它比前面几种写法更适合“按厂商、按产品类型”全覆盖的报表场景。实际工作中很多指标报表的真正难点并不是聚合本身而是如何把分散在不同来源的数据拼成一张可聚合的明细集合。5.2 空值处理COALESCE让报表更稳妥前面我提过AVG会忽略NULL。在生产环境的报表里空值问题无处不在。接口没取到数、源系统漏字段、清洗逻辑有bug都可能导致price为NULL。如果不做任何处理最终报表的平均价格就会有偏差。处理思路有几种一是用COALESCE(price, 0)把NULL转成0适用于“没有价格就是0元”的口径二是用过滤条件把NULL行排除适用于“只统计有效报价”的口径三是单独统计NULL行数让报表使用者自己判断数据质量。不管选哪种都要在SQL的注释或报表说明里写清楚不然三个月后你再看这张报表可能自己也忘了当时的口径。5.3 性能优化先过滤再JOIN少走弯路回到这道习题本身如果数据量真的很大有两条优化原则值得记住。第一条先过滤再JOIN。比如你在Product表里只需要厂商D的数据那就应该先把这部分过滤干净再和PC或Laptop表JOIN不要一开始就全量JOIN再去WHERE里过滤。第二条合理利用索引。Product表的maker和type如果经常出现在WHERE条件里可以考虑建联合索引PC表和Laptop表的model因为是主键JOIN时天然能走索引这通常是性能瓶颈的突破口。现代数据库的优化器越来越聪明很多手动“优化”它们自己会做。但对SQL编写者来说具备“先缩小数据范围再关联”的意识会让你写出的SQL在生产环境里更经得起考验。最后再分享一点我个人的体会。这道题我见过很多次也经常拿来给别人讲但每次讲我都会强调一句话先把这个查询的结果集长什么样子想清楚再动手写。你要的是两行结果PC平均价、Laptop平均价还是一行结果整体平均价把这个想清楚等于先把题目的口径定死了后面的SQL只是执行路径的问题。这个习惯在我做数据开发的这些年里帮了大忙。你下次遇到这种多表求平均的查询也先别急着敲键盘闭眼想一下“最终输出应该是几行几列”再低头写思路会顺很多。