制造业和质检这行的SQL跟互联网那套用户行为分析完全是两个世界。互联网SQL天天琢磨UV、PV、留存制造业SQL绕不开的是工单、批次、检验批、不良代码、量具校准、追溯链。干这行久了你会发现SQL能不能写出来只是第一关能不能写对、写快、写清楚才是真的值钱。这篇文章我想把制造业/质检类业务场景里最常见的20类SQL写法挨个捋一遍每一类都给出可直接套用的思路和代码片段不是为了炫技是为了让你在MES、QMS、SPC、ERP这些系统里取数、出报表、做追溯时少踩坑。1. 先搞清制造业质检数据的底层逻辑1.1 工单、批次、检验批次的模型关系在制造业数据库里最核心的几个实体就是工单Work Order、生产批次Batch和检验批次Inspection Lot。很多SQL写错不是语法问题而是没搞清这三个东西的关系。工单是生产指令一个工单可以拆成多个生产批次一个生产批次在关键工序或完工时会被拆成多个检验批次一个检验批次又会带出若干检验项目和若干条不良记录。举个实际例子某机加工车间一天生产了工单WO20240101下的5000个零件分成了Batch A和Batch B两个批次。Batch A在车削工序抽检时送检了10个生成了检验批次IL240101-01完工后又送检了15个生成了IL240101-02。如果统计“检验批次合格率”你是按检验批次算还是按生产批次算这直接决定了SQL里COUNT到底要COUNT哪个表、要不要DISTINCT。写SQL之前先画清这个关系链物料凭证可能关联批次批次可能关联检验批检验批关联检验结果检验结果关联不良明细。宁可先在纸上画两分钟也别在SQL里翻车一小时。1.2 质检表里那些容易踩坑的字段制造业质检表的字段名看着好像都懂实际用起来全是坑。就拿结果字段来说有的表叫result有的叫qc_status取值有PASS/FAIL也有OK/NG还有用数字1/2的。最坑的是同一种表里不同产品线各有各的取值习惯写SQL前必须先统一口径。还有一个经典坑抽样数和不良数。检验记录里通常会有sample_qty抽样数量、ng_qty不良数量、ok_qty合格数量。有些不良数量填的是“不良件数”有些填的是“不良点数”比如一个零件上划痕、毛刺、尺寸超差各算一个一件有3个不良点ng_qty直接记3。后面做Pareto汇总时不良率口径就会差出一大截。日期字段同样要注意。检验日期inspection_date一般到天但很多追溯查询需要精确到秒的create_time。如果直接按天GROUP BY可能把前一天23点59分的数据算到后一天或者漏掉某天的全部夜班数据。这种细节没人提醒的话排查起来特别耗时间。1.3 口径不统一一半SQL问题出在数据定义我见过太多“同一个指标两套SQL跑出两个数字”的争论。真正原因往往不是SQL写得烂而是业务口径没有锁死。比如“批次合格率”到底是连续批次合格率还是离散批次合格率是一次检验合格率First Time PassFTP还是最终合格率Final Pass这两个指标在返工频繁的产线上差距非常大。实际业务中常见的口径有一次检验合格率报检批数中一次检验合格的批数占比返工后复检合格不算。最终合格率经过返工、重检后最终判定合格的批数占比。抽样合格率合格抽样数量占抽样总数的比例。产品合格率检验合格的产品数量占报检产品总数的比例。SQL只是工具口径才是灵魂。写任何统计SQL之前先跟质量主管确认口径再动手。否则你代码写得再漂亮口径不对输出也没人敢信。2. 高频取数场景20个业务场景精写技巧上2.1 检验批次合格率先去重再统计制造业里最基础也是最高频的统计就是检验批次合格率。看似简单但“同一批次被复检”是常态尤其首件检验、过程巡检和完工检会针对同一个批次产生多条记录。如果直接COUNT(*)复检批次会被重复计算合格率瞬间虚高或虚低。做法是先按检验批次取最新一条记录再统计。SQL Server里用ROW_NUMBER()MySQL可以用子查询按inspection_batch_id分组后取最大检验时间对应的记录。WITH latest_inspection AS ( SELECT inspection_batch_id, result, ROW_NUMBER() OVER (PARTITION BY inspection_batch_id ORDER BY inspection_time DESC) AS rn FROM inspection_record WHERE inspection_date 2024-01-01 AND inspection_date 2024-02-01 ) SELECT COUNT(*) AS total_lots, SUM(CASE WHEN result PASS THEN 1 ELSE 0 END) AS pass_lots, ROUND(100.0 * SUM(CASE WHEN result PASS THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS pass_rate FROM latest_inspection WHERE rn 1;这里有两个细节值得记。一是日期区间用和而不是BETWEEN避免把2月1日当天零点之后的数据也算进1月。二是NULLIF(COUNT(*), 0)防止除数为零一旦某天没有任何检验批次报表不至于直接报错。2.2 不良代码TopN用窗口函数代替自连接要做“本月不良排名前十大”这样的报表很多人第一反应是GROUP BY后再ORDER BY LIMIT或者TOP 10。但如果你还要保留每个不良代码后面跟着的判定标准、责任工位、产品类别或者要做动态的“前N名”最省事的是窗口函数。SELECT defect_code, defect_qty, rank_no FROM ( SELECT defect_code, SUM(defect_qty) AS defect_qty, ROW_NUMBER() OVER (ORDER BY SUM(defect_qty) DESC) AS rank_no FROM defect_record WHERE defect_date 2024-01-01 AND defect_date 2024-02-01 GROUP BY defect_code ) t WHERE rank_no 10;外层包一层子查询是因为WHERE不能直接使用窗口函数的结果。ROW_NUMBER()和RANK()的区别也要分清RANK()遇到并列时排名会跳跃比如两个并列第一下一条就是第三ROW_NUMBER()则一定会给出一串连续编号。做TopN展示时我一般用ROW_NUMBER()不会出现两个“第1名”导致条数超过N的问题。还有一个容易忽略的点defect_qty这个字段的单位。如果一条记录代表一个部件多缺陷点时SUM(defect_qty)累加的是点数还是件数在报表标题里一定要写清楚不然下游用数据的人会骂娘。2.3 量具校准逾期提醒日期运算的典型用法量具管理是所有制造业质检SQL里最容易被低估的场景。量具到期没校准整个检测数据都会失去公信力。实际需求很简单找出所有“下次校准日期早于今天”的量具最好是提前7天预警。SQL写出来也不复杂但很多人喜欢在WHERE里直接对日期做减法比如WHERE DATEDIFF(day, next_cal_date, GETDATE()) 7这在量具表数据量大的时候基本会走全表扫描因为DATEDIFF把索引列包进了函数里。更稳的写法是把阈值计算放到表达式右侧让数据库能用索引SELECT gauge_code, gauge_name, last_cal_date, DATEADD(day, calibration_cycle, last_cal_date) AS next_cal_date FROM gauge_master WHERE status IN_USE AND DATEADD(day, calibration_cycle, last_cal_date) DATEADD(day, 7, GETDATE());顺手多说一句calibration_cycle这个字段的粒度也得统一有的是天数有的是月份。如果存的是12表示12个月那DATEADD(month, ...)和DATEADD(day, ...)完全是两回事。这种元数据层面的坑比SQL本身更值得防。2.4 工序间流转时间用LEAD/LAG找时间差想分析一个批次在每个工序的等待时间、加工时间或者整个生产周期的瓶颈在哪就要在工序报工表或者流转记录表里把一个批次的所有工序按时间顺序排好然后计算相邻工序之间的时间差。这种场景最适合LEAD()和LAG()。假设process_log表里有batch_no、process_seq、process_code、start_time、end_time要算每个批次在工序完成后的等待时间就是下一道工序的开始时间减去当前工序的结束时间。SELECT batch_no, process_seq, process_code, end_time, LEAD(start_time, 1) OVER (PARTITION BY batch_no ORDER BY process_seq) AS next_start_time, DATEDIFF(MINUTE, end_time, LEAD(start_time, 1) OVER (PARTITION BY batch_no ORDER BY process_seq) ) AS waiting_minutes FROM process_log WHERE batch_no B2024010101;这里有个很实际的坑如果工序记录有并行或者有人先补录后工序、再补录前工序ORDER BY process_seq可能和真实时间顺序不一致。所以处理数据时最好同时按process_seq和start_time双条件排序甚至只看start_time。我遇到过条码漏扫导致LEAD()取到一条几天后的记录算出来的等待时间变成负两万分钟查了半天才发现是环节漏报。2.5 多批次同产品对比行转列质量分析里经常要把同一个产品不同批次的某个关键尺寸拉出来横向对比比如外径均值、CPK值。若直接用GROUP BY结果是一列批次号配一列均值领导想看的却是“每一列是一个批次每一行是一个参数”。SQL Server里可以写PIVOT但不同数据库写法不统一MySQL、PostgreSQL还要额外用聚合函数加CASE WHEN。为了让大多数环境都能直接用我习惯用CASE WHEN做行转列SELECT parameter_code, MAX(CASE WHEN batch_no B001 THEN avg_value END) AS avg_B001, MAX(CASE WHEN batch_no B002 THEN avg_value END) AS avg_B002, MAX(CASE WHEN batch_no B003 THEN avg_value END) AS avg_B003 FROM batch_measure_summary WHERE parameter_code IN (OD, ID, LENGTH) GROUP BY parameter_code;核心思路是以你要展示的行维度做GROUP BY把要转成列的字段用CASE WHEN过滤出来再用聚合函数通常用MAX或MIN把同一行的多列值憋出来。没有动态列需求时这种方式比PIVOT更可控SQL审起来也更直观。2.6 AQL抽样方案判定把标准表变成SQL条件AQL可接受质量水平在来料检验里用得非常多。根据GB/T 2828.1或ISO 2859-1标准不同批量范围、不同检验水平、不同AQL值会对应一个固定的抽样数和接收数Ac、拒收数Re。系统落地时方案通常已经存在一张aql_plan表里包含batch_size_range、inspection_level、aql_value、sample_size、ac、re。写SQL时要做的是把“本次检验发现的不良数和Ac比大小”翻译成查询条件。SELECT s.inspection_batch_id, s.inspected_qty, s.defect_qty, p.ac, p.re, CASE WHEN s.defect_qty p.ac THEN ACCEPT WHEN s.defect_qty p.re THEN REJECT ELSE PENDING END AS aql_decision FROM inspection_summary s JOIN aql_plan p ON s.sample_size p.sample_size WHERE s.inspection_date 2024-01-01 AND s.inspection_date 2024-02-01;这个场景最容易翻车的地方是批量范围。batch_size_range如果存的是2-8、9-15这种字符串SQL写起来会很痛苦。所以建表时最好拆出min_batch_size和max_batch_size两个整数列JOIN条件就是b.batch_size BETWEEN p.min_batch_size AND p.max_batch_size。如果改不动表结构就只能用CASE硬编码又丑又难维护。2.7 IQC来料合格率按供应商收货批统计IQC来料质量控制的统计维度通常是供应商、物料分类、月份。最常见指标是“供应商月度来料批次合格率”但来料检验经常因为供应商返工、特采确认产生多条记录。一个来料批次可能检验了两次第一次不合格第二次让步接收直接COUNT会把结果搞得很难看。我的建议是先按“来料批次”取最新一次检验记录再做统计。WITH iqc_latest AS ( SELECT material_code, supplier_code, inspection_batch_id, result, ROW_NUMBER() OVER (PARTITION BY supplier_code, inspection_batch_id ORDER BY inspection_time DESC) AS rn FROM iqc_inspection WHERE inspection_date 2024-01-01 AND inspection_date 2024-02-01 ) SELECT supplier_code, COUNT(*) AS total_lots, SUM(CASE WHEN result PASS THEN 1 ELSE 0 END) AS pass_lots, ROUND(100.0 * SUM(CASE WHEN result PASS THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS pass_rate FROM iqc_latest WHERE rn 1 GROUP BY supplier_code;要不要按物料代码再拆一层取决于你是想看“某供应商所有物料整体表现”还是“某供应商某类物料表现”。整体合格率很容易掩盖物料类别之间的差异比如这家供应商的钣金件合格率高但电子料一塌糊涂平均下来反而看不出问题。所以做供应商评价时至少要有按物料类别分组的版本。2.8 首检/末检/巡检合并查询UNION ALL是常规解法产线上的首件检验、末件检验、巡检记录往往散在不同表里或者同表但检验类型字段不同。要出一个“某条产线某天每两小时一次的首末巡检汇总”最直接的方法是UNION ALL把数据捏在一起。SELECT work_shift, line_code, FIRST AS check_type, check_time, result FROM first_piece_inspection WHERE check_date 2024-01-15 UNION ALL SELECT work_shift, line_code, LAST AS check_type, check_time, result FROM last_piece_inspection WHERE check_date 2024-01-15 UNION ALL SELECT work_shift, line_code, PATROL AS check_type, check_time, result FROM patrol_inspection WHERE check_date 2024-01-15;这里强调用UNION ALL而不是UNION是因为UNION会做去重而检验记录本身存在重复时间的可能性很低去重反而可能丢掉有效记录。再说个实际经验这三类检验的result字段如果在各自表里取值不同比如首检表里是OK/NG巡检表里是PASS/FAIL合并之前必须统一值域。要么在查询里用CASE转换要么在源头折腾数据清洗否则后面统计百分之百出问题。2.9 检验员工作量统计COUNT(DISTINCT)才能避免翻倍统计检验员工作量难点不在SQL而在如何不重复计数。一个检验员一天做了一批零件的5个尺寸检验如果直接连检验项目明细表然后COUNT(*)这批零件会被算成5条工作量批数统计也会失真。正确做法是区分“检验批次数”和“检验项目数”两个粒度SELECT inspector, COUNT(DISTINCT inspection_batch_id) AS batch_count, COUNT(DISTINCT inspection_item_id) AS item_count, COUNT(*) AS row_count FROM inspection_record WHERE inspection_date 2024-01-01 AND inspection_date 2024-02-01 GROUP BY inspector;COUNT(DISTINCT)不是银弹它也有坑。如果同一个检验批号在不同记录里有前后空格或大小写差异去重效果会大打折扣。另外如果一个检验员在1月31日23点录了单但系统时间到了2月1日那这笔工作量会被算到2月月底统计时经常对不上。要跟IT确认清楚到底以业务检验日期为准还是录入时间为准。2.10 不良率环比先算绝对值再算比例“这月不良率比上月上升了几个点”是质量月报里最常见的表达。写环比SQL时很多人喜欢用一个子查询同时算出本月和上月再相除逻辑也能通但代码很乱且容易因为日期边界算错。我习惯用两张汇总表各自算好再FULL OUTER JOINWITH current_month AS ( SELECT ROUND(100.0 * SUM(ng_qty) / NULLIF(SUM(sample_qty), 0), 2) AS bad_rate FROM quality_summary WHERE stat_month 2024-01 ), previous_month AS ( SELECT ROUND(100.0 * SUM(ng_qty) / NULLIF(SUM(sample_qty), 0), 2) AS bad_rate FROM quality_summary WHERE stat_month 2023-12 ) SELECT c.bad_rate AS current_rate, p.bad_rate AS previous_rate, ROUND(c.bad_rate - p.bad_rate, 2) AS rate_diff, ROUND((c.bad_rate - p.bad_rate) / NULLIF(p.bad_rate, 0) * 100, 2) AS rate_change_pct FROM current_month c CROSS JOIN previous_month p;需要特别注意“环比陷阱”2月天数少、1月天数多若直接把不良率相减得出的“改善”或“恶化”可能完全来自生产天数差异。更科学的做法是算“日均不良率”或先把工作日历加进来。这条经验在制造业月报里特别实用否则月底复盘会上你拿出的环比数字会被生产经理一眼看穿。3. 高频追因场景20个业务场景精写技巧下3.1 返工批次追踪递归CTE串起父子关系制造业经常出现一个批次因为部分不良需要返工返工后又产生新的批次号甚至继续返工形成一条返工链。要查“这一批次从最早源头到最终出货经历了哪些批次”最适合用递归查询。SQL Server里的写法是WITH ... AS (...) UNION ALL ...WITH rework_trace AS ( SELECT batch_no AS root_batch, batch_no, 0 AS level FROM batch_master WHERE batch_no B2024010101 UNION ALL SELECT rt.root_batch, b.batch_no, rt.level 1 FROM batch_master b JOIN rework_trace rt ON b.rework_from_batch_no rt.batch_no ) SELECT * FROM rework_trace;递归查询最怕的是环路比如数据录入错把A返工成B又把B返工成A递归就会死循环。SQL Server默认有MAXRECURSION限制一般设成100就够了但生产环境最好加个OPTION (MAXRECURSION 50)兜底。查询前先确认有没有唯一批次号递归列有没有建索引否则层数一深性能就很差。3.2 设备/工装点检记录取出每个设备最新一次点检结果设备点检表通常每天或每班次一条记录。想看每台设备“最近一次点检是否正常”经典写法是ROW_NUMBER()加PARTITION BY device_code然后只取rn1。SELECT device_code, check_time, result, checker FROM ( SELECT device_code, check_time, result, checker, ROW_NUMBER() OVER (PARTITION BY device_code ORDER BY check_time DESC) AS rn FROM equipment_check ) t WHERE rn 1;这里有个容易被忽略的点点检和保养是有区别的。点检是每天看一眼设备状态保养是定期更换油、清洗、紧固。如果表里混了保养记录必须在子查询里先WHERE过滤出真正的点检记录再排最新。否则某台设备刚做过大保养点检记录没更新取出来的“最新点检”还是三天前的系统还误以为一切正常。3.3 质量成本COPQ内外损失成本怎么汇总COPQ质量成本Cost of Poor Quality一般分为内部损失成本和外部损失成本。内部损失包括报废、返工、停工损失外部损失包括退货、索赔、运费等。很多公司把COPQ明细放在财务或者质量损失模块里SQL的难点不在技巧而在金额字段的口径。实际统计时我常用两张表关联一张是损失明细一张是成本类型字典。查询一个月的COPQ总额再按成本类型拆分SELECT cost_type_code, SUM(cost_amount) AS total_cost, COUNT(*) AS record_count FROM copq_detail WHERE lost_month 2024-01 GROUP BY cost_type_code ORDER BY total_cost DESC;如果还要看趋势就按月、按产品线分组。但注意COPQ金额很大报表里最好保留到小数点后两位并且附上币种。很多时候业务部门要的不只是“总金额”而是“报废成本占比最高的是哪个工位”所以明细查询里GROUP BY建议加上工序代码和责任部门。3.4 客户投诉与8D报告关联LEFT JOIN查关闭率客户投诉之后通常要启动8D报告八项纪律问题解决法。业务上经常要看到“本月投诉总数、已关闭8D数量、关闭率”。投诉表和8D表是一对多的关系直接JOIN可能会导致投诉记录翻倍。稳妥的做法是先按complaint_id分组取8D状态的最新值再跟投诉表LEFT JOINSELECT c.complaint_month, COUNT(DISTINCT c.complaint_id) AS complaint_count, COUNT(DISTINCT CASE WHEN d.closed_flag Y THEN c.complaint_id END) AS closed_count, ROUND(100.0 * COUNT(DISTINCT CASE WHEN d.closed_flag Y THEN c.complaint_id END) / NULLIF(COUNT(DISTINCT c.complaint_id), 0), 2) AS close_rate FROM customer_complaint c LEFT JOIN ( SELECT complaint_id, MAX(CASE WHEN status CLOSED THEN 1 ELSE 0 END) AS closed_flag FROM eightd_report GROUP BY complaint_id ) d ON c.complaint_id d.complaint_id WHERE c.complaint_month 2024-01 GROUP BY c.complaint_month;需要注意这里用CASE WHEN d.closed_flag Y THEN c.complaint_id END然后再套COUNT(DISTINCT ...)是为了避免一对多JOIN后计数翻倍。很多新手直接用COUNT(d.complaint_id)结果一条投诉挂着三份8D报告关闭率直接算成300%。3.5 内审不合格项按部门、严重程度统计体系审核里常要统计“各部门开具的不符合项数量及严重程度分布”。审核发现表通常有department_code、finding_levelMajor/Minor/Observation、finding_type等字段。统计一下是最典型的GROUP BY加条件聚合SELECT department_code, COUNT(*) AS total_findings, SUM(CASE WHEN finding_level MAJOR THEN 1 ELSE 0 END) AS major_cnt, SUM(CASE WHEN finding_level MINOR THEN 1 ELSE 0 END) AS minor_cnt, SUM(CASE WHEN finding_level OBSERVATION THEN 1 ELSE 0 END) AS observation_cnt FROM audit_finding WHERE audit_date 2024-01-01 AND audit_date 2024-04-01 GROUP BY department_code ORDER BY major_cnt DESC;这个统计本身不难真正要留意的是审核条款编号比如ISO 9001条款是8.5.1、8.6之类。如果把条款当字符串直接GROUP BY相近条款就可能被分开。更好的做法是把条款字典表单独拎出来用条款ID关联。3.6 计量器具MSAGRR分析前的SQL预处理MSA测量系统分析里的GRR量具重复性和再现性分析严格来说要用统计软件但SQL可以在前面做很大一部分数据准备。通常要把三个操作者、十个零件、每个零件测三次的数据按“操作者、零件、测量值”的格式准备好先算出每零件每位操作者的均值和极差。SELECT part_no, operator, COUNT(*) AS trial_count, AVG(measure_value) AS avg_value, MAX(measure_value) - MIN(measure_value) AS range_value FROM msa_raw_data WHERE study_id GRR-202401 GROUP BY part_no, operator ORDER BY part_no, operator;如果要更进一步可以在SQL里算出各零件、各操作者的极差均值但完整的方差分量计算还是建议放到Minitab或Python里。SQL的定位是把数据清洗好、透视好别在数据库里硬算统计模型既吃力又不讨好。3.7 报废率与损耗率先统一投入和报废口径报废率是个容易扯皮的指标。分子是报废数量分母是投产数量看起来简单。但“投产数量”到底是工单下达数、工单接收数还是系统排产数“报废数量”是工人上报数还是质检确认数不统一生产部和质量部能吵到月底。SQL层面要做的是保证分子分母来自同一种口径。比如都以“工单完成数量”为分母以“质量确认报废单数量”为分子SELECT wo.work_order_no, wo.order_qty AS input_qty, COALESCE(SUM(scrap.scrap_qty), 0) AS scrap_qty, ROUND(100.0 * COALESCE(SUM(scrap.scrap_qty), 0) / NULLIF(wo.order_qty, 0), 2) AS scrap_rate FROM work_order wo LEFT JOIN scrap_record scrap ON wo.work_order_no scrap.work_order_no WHERE wo.order_date 2024-01-01 AND wo.order_date 2024-02-01 GROUP BY wo.work_order_no, wo.order_qty;用LEFT JOIN而不是INNER JOIN是为了保留没有报废记录的工单否则这些工单直接消失分母变小报废率又会虚高。这里我特别想提醒一句SQL里任何“没有记录”的业务也必须有记录可查否则统计条件一变整个报表就失真。3.8 生产直通率FPY一次就做对的比率FPYFirst Pass Yield直通率是指产品在第一道工序开始到最后一道工序结束中间没有任何返工、报废、重测一次全部合格的比例。它比单工序合格率更严格也更反映真实制造水平。数据建模如果有专门的first_pass_flag字段SQL很简单。但没有这个字段时就要通过“是否存在返工、重工记录”来判断SELECT wo.work_order_no, wo.order_qty, CASE WHEN EXISTS ( SELECT 1 FROM rework_record r WHERE r.work_order_no wo.work_order_no ) THEN NON_FPY ELSE FPY END AS fpy_flag FROM work_order wo WHERE wo.order_date 2024-01-01 AND wo.order_date 2024-02-01;算出每个工单的fpy_flag之后再汇总每天或每产线的直通率就是顺手的事。这里需要注意有些返工不会产生新的返工单只是在原工单里增加一个返工工序记录所以EXISTS判断要覆盖返工表和工序报工表两张表否则直通率会被高估。3.9 Pareto排列图用窗口函数算累计百分比质量改善里的Pareto图排列图核心思想是“少数关键缺陷造成大部分影响”。SQL要做的就是把所有缺陷类型按不良数量降序排列同时算出累计不良数量和累计百分比。SQL Server和PostgreSQL都支持窗口函数MySQL 8.0也支持写法如下SELECT defect_code, defect_qty, ROUND(100.0 * defect_qty / SUM(defect_qty) OVER (), 2) AS pct, ROUND(100.0 * SUM(defect_qty) OVER ( ORDER BY defect_qty DESC ROWS UNBOUNDED PRECEDING ) / SUM(defect_qty) OVER (), 2) AS cum_pct FROM ( SELECT defect_code, SUM(defect_qty) AS defect_qty FROM defect_record WHERE defect_date 2024-01-01 AND defect_date 2024-02-01 GROUP BY defect_code ) t ORDER BY defect_qty DESC;这个SQL巧妙在SUM(defect_qty) OVER (ORDER BY defect_qty DESC ROWS UNBOUNDED PRECEDING)这个东西。它让累计值沿着排序后的数据逐行累加。注意里面那层子查询必不可少要先把各缺陷代码的总量算出来再做窗口计算否则每个原始记录都会被当成一行累计逻辑完全错乱。3.10 追溯查询与召回模拟从成品一路查回原料制造业最紧张的时刻之一就是客户投诉需要追溯。你要能从“出货批次”查到“生产工单”从“生产工单”查到“原料批次”再从“原料批次”查到“供应商”甚至继续往下查“供应商原料的出厂批号”。这种链路式查询在数据仓库里有时会有专门的追溯表但多数时候还是靠多表JOIN或者递归CTE。如果业务系统已经维护了批次关系表batch_relation(parent_batch_no, child_batch_no)向上追溯用递归CTE很方便WITH trace_up AS ( SELECT child_batch_no, parent_batch_no, 1 AS level FROM batch_relation WHERE child_batch_no SHIP-20240115-001 UNION ALL SELECT br.child_batch_no, br.parent_batch_no, t.level 1 FROM batch_relation br JOIN trace_up t ON br.child_batch_no t.parent_batch_no ) SELECT * FROM trace_up;向下追溯只要把parent_batch_no和child_batch_no的条件反过来。实际项目里追溯系统最大的障碍不是SQL而是数据录入缺漏。只要有一个批次没扫码链条就会断。所以做追溯报表前先做“断链检查”——找出关系表里有头无尾的数据。这个动作能帮你提前发现很多生产执行层面的漏洞。4. 让SQL跑得快制造业数据量级下的优化实战4.1 别在WHERE里写函数索引会失效制造业的检验记录表动辄上百万行如果查询经常跑得很慢第一反应要去看WHERE条件里的列是否被函数包住了。比如WHERE DATEDIFF(day, inspection_date, GETDATE()) 7虽然逻辑上是“最近7天”但数据库没法直接用inspection_date上的索引只能一行行算完再过滤。正确写法是把日期区间直接算出来让inspection_date裸奔WHERE inspection_date DATEADD(day, -7, CAST(GETDATE() AS date)) AND inspection_date CAST(GETDATE() AS date)这个优化往往能让查询从几秒降到几十毫秒。类似的坑还有在WHERE里用LEFT(字段, 3) ABC。这种写法无论如何都会全表扫最好改成字段 LIKE ABC%或在建表时就多设计一个冗余前缀列。4.2 索引不是越多越好联合索引要懂“最左前缀”很多同行一说到优化就想着哪里慢加哪里索引。但索引加多了写入会变慢存储会膨胀而且查询还不一定走索引。真正实用的是理解“联合索引的最左前缀原则”。拿最常见的查询来说SELECT * FROM inspection_record WHERE inspection_date 2024-01-01 AND inspection_date 2024-02-01 AND inspector 张工;如果同时存在(inspection_date, inspector)和(inspector, inspection_date)两个索引数据库会选哪个多数优化器会根据统计信息选。但如果只建了一个(inspector, inspection_date)索引而你查询的条件是先用日期范围过滤再用人员过滤前面那列用不到效果就大打折扣。设计联合索引一定要把等值条件放前面、范围条件放后面并且优先参考实际SQL里的条件顺序。4.3 大表JOIN先缩小再关联质检数据库里JOIN是家常便饭最怕的是两个几百万行的表直接JOIN。比如一份报表要按供应商分组统计不合格率需要关联inspection_record、material_master、supplier_master三张表。很多人的第一版SQL是这样的SELECT s.supplier_name, COUNT(*) FROM inspection_record i JOIN material_master m ON i.material_code m.material_code JOIN supplier_master s ON m.supplier_code s.supplier_code WHERE i.inspection_date 2024-01-01 AND i.inspection_date 2024-02-01 GROUP BY s.supplier_name;这句SQL在数据量大时inspection_record先和material_master全部JOIN完再过滤日期会白白消耗大量内存。更好的习惯是先按日期把inspection_record缩小甚至先按物料代码做一次汇总再去关联字典表WITH filtered AS ( SELECT material_code, COUNT(*) AS total_lots, SUM(CASE WHEN result FAIL THEN 1 ELSE 0 END) AS fail_lots FROM inspection_record WHERE inspection_date 2024-01-01 AND inspection_date 2024-02-01 GROUP BY material_code ) SELECT s.supplier_name, SUM(f.total_lots) AS total_lots, SUM(f.fail_lots) AS fail_lots FROM filtered f JOIN material_master m ON f.material_code m.material_code JOIN supplier_master s ON m.supplier_code s.supplier_code GROUP BY s.supplier_name;先聚合再关联不仅结果正确而且中间结果集小得多查询计划也好看。这条经验尤其在MES系统库上管用因为MES表结构通常不按报表需求建模字典表小而散事实表大而杂。4.4 慢SQL排查思路执行计划是第一步遇到慢SQL不要急着猜。先看执行计划里哪一步消耗最大。SQL Server里可以按CtrlM显示实际执行计划MySQL里用EXPLAINPostgreSQL里用EXPLAIN ANALYZE。最常看到的几种“危险信号”Table Scan或者Clustered Index Scan大概率索引没建或没走对。Key Lookup太多可能是覆盖索引没覆盖全导致每行都要回到主表取列。Hash Match连接两个超大结果集意味着JOIN前的结果集没做足够过滤。Sort操作出现在大结果集上ORDER BY列没有索引支撑。排查时先看哪个操作符的“预估行数”和“实际行数”差异巨大往往那个地方就是问题所在。实测下来制造业报表里最常出现的就是“为了查一个不良明细硬把整年的检验记录全部读出来”。先加月份参数再建索引基本能解决80%的慢查询。5. 常见问题与避坑清单5.1 日期与时间精度问题质检数据一旦跨班次、跨日夜日期处理的坑就特别明显。许多检验记录的inspection_date是datetime类型值可能是2024-01-31 23:59:59。如果用WHERE inspection_date 2024-01-31这条记录大概率查不出来。所以日期过滤永远用区间而且用“含头不含尾”WHERE inspection_date 2024-01-01 AND inspection_date 2024-02-01这样做还有个好处只要日期精度变到毫秒边界也不会出问题。我见过太多人因为BETWEEN 2024-01-31 AND 2024-02-01把2月1日凌晨的数据也算进了1月。这类错误往往在月度对账时被放大而且特别难查。5.2 NULL不等于0更不等于空质检库里NULL的语义很含糊它可能是“未检验”“未记录”“不适用”也可能就是录入漏了。SUM(NULL)结果是NULLCOUNT(NULL)结果是0NULLIF(expr, 0)和COALESCE(expr, 0)是两个完全不同的函数用错一个报表数字就不对。比较典型的是不良率统计-- 错误示范ng_qty 为 NULL 时整个 SUM 为 NULL SELECT SUM(ng_qty) / SUM(sample_qty) FROM inspection_record; -- 正确示范 SELECT COALESCE(SUM(ng_qty), 0) / NULLIF(SUM(sample_qty), 0) FROM inspection_record;前者一旦某天检验全部合格ng_qty的SUM可能是NULL分母除以NULL直接变NULL报表显示空值。后者用COALESCE把分子空值归零用NULLIF把分母零变成NULL从而避免除零错误。5.3 盲目用DISTINCT导致数据失真DISTINCT是最容易被滥用的关键词之一。很多人发现查询结果有重复顺手加个DISTINCT结果重复是消了数据也错了。比如统计不合格批次时你只想取每个批次最新一条检验记录正确写法是窗口函数去重SELECT batch_no, result FROM ( SELECT batch_no, result, ROW_NUMBER() OVER (PARTITION BY batch_no ORDER BY inspection_time DESC) AS rn FROM inspection_record ) t WHERE rn 1;而SELECT DISTINCT batch_no, result会把同一批次“合格后又复检不合格”的记录都保留你根本看不出这个批次当前真实状态。数据量小的时候看不出问题数据量大到做月报时所有数字都经不起推敲。记住先查清楚为什么重复再决定怎么去重。5.4 参数化查询别把SQL拼成字符串制造业系统经常要做动态查询比如筛选日期范围、选择供应商、选择产品线。如果直接把用户输入拼进SQL字符串就存在SQL注入风险。而且从性能角度看每次拼出来的SQL文本不同数据库很难复用执行计划慢SQL也就跟着来了。基本做法是使用参数化查询。无论是Java的PreparedStatement、C#的SqlParameter、Python的%s占位符还是直接在报表工具里用参数都比拼字符串安全得多。这不是危言耸听质量系统一旦被注入比互联网网站被注入更麻烦因为涉及到生产数据、追溯链、甚至出货放行数据。5.5 收藏一份常用SQL片段速查表最后把我日常用的最多的SQL片段整理成一个速查表适合贴在手边备查业务需求SQL关键写法去重取最新ROW_NUMBER() OVER (PARTITION BY 业务ID ORDER BY 时间 DESC) rn再取rn 1行转列MAX(CASE WHEN 列A 值 THEN 列B END) GROUP BY 行维度累计占比SUM(值) OVER (ORDER BY 值 DESC ROWS UNBOUNDED PRECEDING) / SUM(值) OVER ()相邻时间差LEAD(时间, 1) OVER (PARTITION BY 批次 ORDER BY 序号)递归父子关系CTE UNION ALL注意加MAXRECURSION限制防止除零NULLIF(分母, 0)空值归一COALESCE(字段, 0)日期区间 开始日期 AND 结束日期一对多去重计数COUNT(DISTINCT CASE WHEN ... THEN 主表ID END)大表先聚合再连接WITH子查询先GROUP BY再JOIN字典表这个表看着简单但每个都是从实际项目中“摔”出来的。我最真实的体会是制造业SQL没有太多花哨技巧最值钱的是对业务口径的理解和对数据质量的警惕。同一个查询需求换一个业务部门来问SQL可能就要改一版。写之前多问一句“这个指标的口径是什么”比写完再返工高效得多。