mysql子查询

厂内MES系统上线前后，因为开发人员紧缺，作为关键用户之一，也跟着学了一些SQL，开发维护了一些报表。今日遇到一个棘手的连接，在此记录一下。

问题分析

系统中数据库里几个关键的表如下，其中数据收集结果与工单工序的出料通过前者的so_router_step_out_id与后者的id进行连接。

1. 数据收集结果(data_collection_result)
   1. 操作条件(dcr_operation_condition)
   2. 尺寸检验(dcr_operation_condition)
   3. 表面缺陷(dcr_surface_defect)
   4. 过程检验(dcr_process_inspect)
   5. 加工记录(dcr_process_record)
2. 工单(so)
   1. 工单工序(so_router_step)
      1. 进料(so_router_step_in)
      2. 出料(so_router_step_out)
      3. 退料(so_router_step_rp)
   2. 销售订单行(so_sale_order_item)
   3. 成品要求(so_final_req)

每次工序进行一次生产后，会记录以上数据，现在需要计算一个折合产量的指标，要求如下：

• 一次产出合宽($W_t$)：根据工单>工单工序>出料(多行)里的宽度（$W_i$）与限重次数($C_k$)计算：通过不同限重来划分一次产出，计算一次产出下宽度的合宽($\sum_{i=1}^k W_i$)
• 连接($Connect$)：将合宽返回到出料，作为一个属性加入到出料料行中

相关笔记

经过查阅资料，发现SQL中可以通过子查询(Subquery)来实现类似的需求。子查询就是在正常查询的基础上嵌套一层，从该层里面进行查询。子查询时会先执行内层括号里的语句而后才执行外层的语句。这种方法原则上没有上限，可以无限嵌套🪆，然而套娃层数越多，查询效率越低。

select a,
from (
select A1
from source
where a > 0) as table

根据子查询返回结果的行数，可以分为两种方式：标量子查询与关联子查询。

标量子查询

顾名思义，这个查询方式的特点就是返回一个唯一的结果，适用于返回诸如平均值、合格率、求和等数据集合的唯一值。因为是返回的唯一值，因而可以用到常数、列名等地方，来进一步的筛选。比如，用子查询查出来平均值，然后用Where语句去筛选出来大于小于平均值的数据，也可以用Group by、Havingf、Oeder by等语句里。

比如这是从14:00至今(19:00)，F产线出料的宽度值

SELECT 
	o.width
FROM so_router_step_out o
where o.start_date_time_lt > '2025-11-19 14:00'
GROUP BY o.id

output

633
600
482
612
612
612
612
612
598
482
605
617

取大于平均值的数据，就可以这样操作：

SELECT 
	o.width
FROM so_router_step_out o
where o.start_date_time_lt > '2025-11-19 14:00' 
and
 o.width > 
(
	select 
		avg(width) 
	from so_router_step_out 
	where start_date_time_lt > '2025-11-19 14:00' 
)
GROUP BY o.id

output

633
600
612
612
612
612
612
598
605
617

关联子查询

标量子查询只能范围唯一的值，如果我们想要返回多个值，比如不同钢种的宽度平均值。就必须用到关联子查询了，还以今天14:00至今(19:20)，F产线出料的宽度值以及钢种的信息为例：

SELECT 
	o.width,
	o.steel_grade
FROM so_router_step_out o
where o.start_date_time_lt > '2025-11-19 14:00' 
GROUP BY o.id
-- 输出结果
-- 638	301不锈钢
-- 603	301不锈钢
-- 633	301不锈钢
-- 600	301B不锈钢
-- 482	737复合性不锈钢
-- 612	304不锈钢
-- 612	304不锈钢
-- 612	304不锈钢
-- 612	304不锈钢
-- 612	304不锈钢
-- 598	301不锈钢
-- 482	737复合性不锈钢
-- 605	304不锈钢
-- 617	301不锈钢
-- 616	304不锈钢
-- 338	304不锈钢
-- 331	304不锈钢
-- 338	304不锈钢
-- 331	304不锈钢
-- 338	304不锈钢
-- 331	304不锈钢

各个钢种的平均宽度如下：

平均值计算

select
	avg(o1.width),
	o1.steel_grade
from
	so_router_step_out as o1
where
	start_date_time_lt > '2025-11-19 14:00'
group by
	o1.steel_grade
-- 600.0000	301B不锈钢
-- 617.8000	301不锈钢
-- 483.6923	304不锈钢
-- 482.0000	737复合性不锈钢

这是使用标量子查询是会报错的，我们就必须采用关联子查询，与前者不同的是，关联子查询通过一个类似于join语句写法的where语句，将内层与外层的信息连接在一起，比如下例中内外层的steel_grade就是一个连接条件，（注意连接条件一定要写在内层中，写在外层的话作用域就变了，不会起效果。）

关联子查询

SELECT 
	o.width,
	o.steel_grade
FROM so_router_step_out o
where o.start_date_time_lt > '2025-11-19 14:00' and o.width >= 
(
	select
		avg(o1.width)
	from
		so_router_step_out as o1
	where
		start_date_time_lt > '2025-11-19 14:00' 
	and
		o1.steel_grade = o.steel_grade
	group by
		o1.steel_grade
)
GROUP BY o.id

-- 638	301不锈钢
-- 633	301不锈钢
-- 600	301B不锈钢
-- 482	737复合性不锈钢
-- 612	304不锈钢
-- 612	304不锈钢
-- 612	304不锈钢
-- 612	304不锈钢
-- 612	304不锈钢
-- 482	737复合性不锈钢
-- 605	304不锈钢
-- 616	304不锈钢

从实际的作用来看，关联子查询与group by的作用是相近的，都是通过一些方法对数据进行了切分。

问题解决

select
	i.id,	
	o.width as widthtotal
from so_router_step m
left join so_router_step_in i on i.detail_table_id = m.id 
left join so_router_step_out o on o.detail_table_id = m.id
where m.resource = '740755479873064960' 
group by i.id 

通过限重次数(xz_times)与进料id进行分组计算，通内层关联子查询连接合宽，然后借由限重与id两者连接到外层。如果为NULL则返回原有宽度(CASE)实现。

SELECT 
    i.id AS 进料id,
    o.id AS 出料id,
    o.sfc AS 出料钢卷号,
    o.batch AS 出料批次号,
    o.xz_times AS 限重次数,
    o.width AS 出料宽度,
    CASE 
        WHEN o.xz_times IS NULL THEN o.width
        ELSE (
            SELECT SUM(o2.width) 
            FROM so_router_step_out o2
            JOIN so_router_step_in i2 ON i2.detail_table_id = o2.detail_table_id
            WHERE i2.id = i.id AND o2.xz_times = o.xz_times
        )
    END AS 一次产出合宽
FROM so_router_step m
LEFT JOIN so_router_step_in i ON i.detail_table_id = m.id 
LEFT JOIN so_router_step_out o ON o.detail_table_id = m.id
WHERE m.resource = '740755479873064960' 
GROUP BY o.id
ORDER BY i.id, o.xz_times;