日常开发中常用的SQL语句小记

       在Spring Boot项目的日常开发中，我们通常使用Mybatis plus中一些简单的数据层处理，但是其功能相对简单，对于复杂的多表连接和一些特定的业务逻辑可能不够灵活。

       当涉及到多表的复杂连接，如多个表之间的左连接、右连接、全连接，且连接条件和筛选条件比较复杂时，MyBatis-Plus 的内置方法可能无法满足需求。还需要去xml中写sql

<!-- UserMapper.xml -->
<select id="findUsersWhoBoughtProduct" resultMap="ComplexResultMap">
    SELECT 
        u.*,
        o.*,
        p.*
    FROM 
        user u
    JOIN 
        order o ON u.id = o.user_id
    JOIN 
        product p ON o.product_id = p.id
    WHERE 
        p.product_name LIKE CONCAT('%', #{productName}, '%')
    AND 
        o.amount * p.price > #{minTotalCost}
</select>

目录

一、按生产线统计产量和良品率

二、查找原材料库存不足的产品及所需补充量

三、按班次和设备统计设备故障时间和平均维修时长

四、找出生产周期最长的产品订单及涉及的生产工序

五、根据产品序列号追溯原材料批次和供应商

---

下面是一些常见的业务的SQL语句

一、按生产线统计产量和良品率

业务场景：
       统计每条生产线在一定时间范围内的产量和良品率，以便评估生产线的生产效率和质量控制。

SQL 语句：

SELECT 
    l.line_name,
    COUNT(p.product_id) AS total_products,
    SUM(CASE WHEN p.quality_status = '合格' THEN 1 ELSE 0 END) AS good_products,
    (SUM(CASE WHEN p.quality_status = '合格' THEN 1 ELSE 0 END) / COUNT(p.product_id)) * 100 AS yield_rate
FROM 
    production_line l
JOIN 
    production_record p ON l.line_id = p.line_id
WHERE 
    p.production_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 
    l.line_name;

解释：

• production_line 表存储生产线信息，包含 line_id 和 line_name 等列。
• production_record 表存储生产记录信息，包含 product_id、line_id、quality_status 和 production_date 等列。
• COUNT(p.product_id) 统计该生产线生产的产品总数。
• SUM(CASE WHEN p.quality_status = '合格' THEN 1 ELSE 0 END) 统计该生产线生产的合格产品数。
• (SUM(CASE WHEN p.quality_status = '合格' THEN 1 ELSE 0 END) / COUNT(p.product_id)) * 100 计算良品率。

二、查找原材料库存不足的产品及所需补充量

业务场景：
       监控原材料库存，当某种产品的原材料库存低于安全库存时，需要找出该产品以及需要补充的原材料数量。

SQL 语句：

SELECT 
    p.product_name,
    r.material_name,
    p.required_quantity - i.stock_quantity AS shortage_quantity
FROM 
    product p
JOIN 
    raw_material_requirement r ON p.product_id = r.product_id
JOIN 
    inventory i ON r.material_id = i.material_id
WHERE 
    i.stock_quantity < p.required_quantity;

解释：

• product 表存储产品信息，包含 product_id 和 product_name 等列。
• raw_material_requirement 表存储产品所需的原材料信息，包含 product_id、material_id 和 required_quantity 等列。
• inventory 表存储原材料的库存信息，包含 material_id 和 stock_quantity 等列。
• p.required_quantity - i.stock_quantity 计算出原材料的短缺数量。

三、按班次和设备统计设备故障时间和平均维修时长

业务场景：
       为了对设备维护进行管理，需要统计每个班次和设备的故障时间及平均维修时长。

SQL 语句：

SELECT 
    s.shift_name,
    e.equipment_name,
    SUM(f.failure_duration) AS total_failure_duration,
    AVG(f.repair_duration) AS average_repair_duration
FROM 
    shift s
JOIN 
    equipment_usage e ON s.shift_id = e.shift_id
JOIN 
    equipment_failure f ON e.equipment_id = f.equipment_id
GROUP BY 
    s.shift_name, e.equipment_name;

解释：

• shift 表存储班次信息，包含 shift_id 和 shift_name 等列。
• equipment_usage 表存储设备使用信息，包含 shift_id 和 equipment_id 等列。
• equipment_failure 表存储设备故障信息，包含 equipment_id、failure_duration 和 repair_duration 等列。
• SUM(f.failure_duration) 计算设备的总故障时长。
• AVG(f.repair_duration) 计算设备的平均维修时长。

四、找出生产周期最长的产品订单及涉及的生产工序

业务场景：
       需要找出生产周期最长的产品订单，并查看该订单所涉及的生产工序，以便优化生产流程。

SQL 语句：

SELECT 
    o.order_id,
    o.product_name,
    o.start_date,
    o.end_date,
    p.process_name
FROM 
    production_order o
JOIN 
    production_process p ON o.order_id = p.order_id
WHERE 
    (o.end_date - o.start_date) = (
        SELECT MAX(end_date - start_date)
        FROM production_order
    );

解释：

• production_order 表存储生产订单信息，包含 order_id、product_name、start_date 和 end_date 等列。
• production_process 表存储生产工序信息，包含 order_id 和 process_name 等列。
• 子查询 (SELECT MAX(end_date - start_date) FROM production_order) 找出生产订单中的最大生产周期。
• 外部查询找出生产周期等于最大生产周期的订单及其涉及的生产工序。

五、根据产品序列号追溯原材料批次和供应商

业务场景：
       当产品出现质量问题时，需要根据产品序列号追溯该产品使用的原材料批次和供应商信息。

SQL 语句：

SELECT 
    pr.product_serial_number,
    rm.material_batch_number,
    s.supplier_name
FROM 
    product_record pr
JOIN 
    raw_material_usage ru ON pr.product_id = ru.product_id
JOIN 
    raw_material rm ON ru.material_id = rm.material_id
JOIN 
    supplier s ON rm.supplier_id = s.supplier_id
WHERE 
    pr.product_serial_number = 'your_serial_number';

解释：

• product_record 表存储产品的记录信息，包含 product_id 和 product_serial_number 等列。
• raw_material_usage 表存储产品使用的原材料信息，包含 product_id 和 material_id 等列。
• raw_material 表存储原材料信息，包含 material_id、material_batch_number 和 supplier_id 等列。
• supplier 表存储供应商信息，包含 supplier_id 和 supplier_name 等列。
• 这里将 'your_serial_number' 替换为你要追溯的产品序列号。