<select id="selectPipeOutAndIntoStorageStatistics" resultType="com.mom.pipe.query.dispatch.view.PipeOutAndIntoStorageStatisticsView"> select a.pallet_code, count(1) as total, sum(if(b.pipe_classify = '钢管', 1, 0)) as steel_pipe, sum(if(b.pipe_classify = '有色管', 1, 0)) as colored_pipe, sum(if(b.pipe_classify = '钢管' and a.into_storage = 1, 1, 0)) as steel_pipe_into, sum(if(b.pipe_classify = '钢管' and a.out_storage = 1, 1, 0)) as steel_pipe_out, sum(if(b.pipe_classify = '有色管' and a.into_storage = 1, 1, 0)) as colored_pipe_into, sum(if(b.pipe_classify = '有色管' and a.out_storage = 1, 1, 0)) as colored_pipe_out, sum(if(b.pipe_classify = '钢管' and a.into_storage = 1, 1, 0)) + sum(if(b.pipe_classify = '有色管' and a.into_storage = 1, 1, 0)) as total_pipe_into, sum(if(b.pipe_classify = '钢管' and a.out_storage = 1, 1, 0)) + sum(if(b.pipe_classify = '有色管' and a.out_storage = 1, 1, 0)) as total_pipe_out from pipe_info a left join pipe_material b on b.plant_area_code = a.plant_area_code and b.material_level = a.pipe_level and b.material_name = a.material where a.plant_area_code = #{query.plantAreaCode} and a.project_code = #{query.projectCode} <if test="query.palletCodeList != null and query.palletCodeList.size > 0"> and a.pallet_code in <foreach collection="query.palletCodeList" item="item" open="(" close=")" separator=","> #{item} </foreach> </if> <if test="query.processingBatchList != null and query.processingBatchList.size > 0"> and a.tech_batch_code in <foreach collection="query.processingBatchList" item="item" open="(" close=")" separator=","> #{item} </foreach> </if> <if test="query.startIntoStorageDate != null"> and a.into_storage_date >= #{query.startIntoStorageDate} </if> <if test="q

<think>嗯，用户问的是如何构建带有动态条件过滤的SQL查询，用于管道出入库的分类汇总。首先，我需要理解用户的具体需求。他们提到的动态条件包括pallet_code列表、tech_batch_code列表和日期范围。这些都是常见的过滤条件，可能用于筛选特定的托盘、批次和一定时间范围内的出入库记录。 用户需要的是分类汇总，也就是按照某些字段分组统计，比如出入库类型、日期、托盘和批次等。所以，核心的SQL结构应该是SELECT语句加上GROUP BY子句，以及SUM或COUNT等聚合函数。但是，动态条件如何处理呢？尤其是当某些过滤条件可能为空，即用户不指定时需要忽略该条件的情况。 这时候，我想到可以使用条件逻辑，比如在WHERE子句中使用CASE语句或者更常见的做法是用逻辑运算符来处理动态参数。例如，如果某个参数为空，则条件表达式为真，否则检查字段是否符合参数值。比如，对于pallet_code列表，如果用户传入了多个值，可能需要用IN操作符，但如果列表为空，则应该跳过这个条件。 不过，在SQL中，处理动态条件时，参数是否为空可能会影响查询的构建。如果使用应用程序代码来构建SQL，可能需要动态拼接WHERE子句的各个部分，但这容易导致SQL注入风险，尤其是如果直接拼接字符串的话。所以，最佳实践是使用参数化查询，同时处理动态条件。 比如，在WHERE子句中，可以这样写： WHERE (pallet_code IN (:pallet_codes) OR :pallet_codes IS NULL) AND (tech_batch_code IN (:tech_batch_codes) OR :tech_batch_codes IS NULL) AND (operation_date BETWEEN :start_date AND :end_date OR (:start_date IS NULL AND :end_date IS NULL)) 这里使用了命名参数，比如:pallet_codes，当这个参数为空时，条件变为真，从而忽略该过滤条件。不过，需要注意的是，这里可能需要处理参数传递的方式，尤其是在使用IN操作符时，如何传递列表参数。不同的数据库和编程语言有不同的处理方式，比如在Python中使用元组，或者在Java中使用数组，并确保参数化查询正确处理列表。 另外，日期范围的处理也需要考虑，如果用户没有提供开始日期或结束日期，可能需要默认查询所有日期，或者根据需求调整条件。比如，如果只提供了开始日期，则查询该日期之后的记录；如果只提供了结束日期，则查询该日期之前的记录。但用户的需求是日期范围，所以可能需要同时有开始和结束日期，或者允许其中一个为空。 接下来是分类汇总的部分，用户需要按出入库类型、日期、托盘和批次进行汇总，所以GROUP BY子句需要包含这些字段。比如： GROUP BY operation_type, operation_date, pallet_code, tech_batch_code 然后，使用SUM(quantity)来统计数量，可能还需要COUNT(*)来统计记录数，具体取决于用户的需求。 在性能优化方面，如果数据量大，可能需要在相关字段上建立索引，比如operation_date、pallet_code、tech_batch_code等。此外，分区表也是一个选项，尤其是按日期分区，可以加快时间范围的查询速度。 关于参数化查询和防止SQL注入，必须强调不要使用字符串拼接，而是使用预编译的语句，绑定参数。例如，在Python中使用cursor.execute(sql, params)的方式，确保参数被正确转义。 可能还需要考虑分页，如果结果集很大，用户可能需要分页显示，这时可以添加LIMIT和OFFSET子句，但用户的问题中没有提到分页，所以可能不需要。 总结一下，构建这样的SQL查询需要： 1. 使用参数化查询来处理动态条件，确保安全性和效率。 2. 在WHERE子句中使用逻辑条件来处理可能为空的参数。 3. 使用GROUP BY进行分组汇总。 4. 考虑索引和分区优化查询性能。 5. 示例代码展示如何在不同编程语言中实现，比如Python的SQLAlchemy或者Java的JDBC。 需要检查是否存在可能的错误，比如当列表参数为空时，IN子句可能会导致语法错误，例如IN (NULL)可能不符合预期，所以需要确保当参数为空时，条件被正确忽略。这可能需要应用层在构建查询时处理，或者在SQL中使用更复杂的条件判断。 另外，关于日期范围，如果用户只提供一个日期，可能需要调整BETWEEN的条件，比如如果start_date为空，end_date不为空，则查询operation_date <= end_date；反之亦然。但在示例中，用户可能希望同时提供开始和结束日期，或者都不提供，所以使用BETWEEN并结合OR条件来处理两者都为空的情况。 最后，给出示例代码，展示如何在实际中使用参数化查询，并处理动态条件，特别是在处理列表参数时的正确方法，比如在Python中使用WHERE...IN的方式，可能需要将列表转换为元组，并确保参数化查询正确处理。</think>### 管道出入库统计SQL查询构建方法 #### 一、核心逻辑分析 1. **动态条件过滤** 使用`WHERE`子句结合`CASE`或`COALESCE`实现动态参数控制，例如： ```sql WHERE (pallet_code IN (:pallet_codes) OR :pallet_codes IS NULL) AND (tech_batch_code IN (:tech_batch_codes) OR :tech_batch_codes IS NULL) AND (operation_date BETWEEN :start_date AND :end_date) ``` 2. **分类汇总** 通过`GROUP BY`对字段分层聚合，配合`SUM`/`COUNT`统计数量： ```sql SELECT operation_type, DATE(operation_date) AS date, pallet_code, tech_batch_code, SUM(quantity) AS total_quantity FROM pipeline_inventory GROUP BY operation_type, date, pallet_code, tech_batch_code ``` #### 二、完整查询示例 ```sql /* 统计出入库数量，支持动态过滤条件 */ SELECT operation_type, DATE(operation_date) AS operation_day, pallet_code, tech_batch_code, COUNT(*) AS record_count, SUM(CASE WHEN direction = 'IN' THEN quantity ELSE -quantity END) AS net_quantity FROM pipeline_operations WHERE (pallet_code IN (:pallet_codes) OR :pallet_codes IS NULL) AND (tech_batch_code IN (:tech_batch_codes) OR :tech_batch_codes IS NULL) AND (operation_date >= COALESCE(:start_date, '1970-01-01')) AND (operation_date <= COALESCE(:end_date, CURRENT_DATE)) GROUP BY operation_type, operation_day, pallet_code, tech_batch_code ORDER BY operation_day DESC; ``` #### 三、关键优化策略 1. **索引优化** 在`operation_date`、`pallet_code`、`tech_batch_code`字段建立组合索引，提升过滤效率[^1]。 2. **参数化查询** 使用预编译语句防止SQL注入（示例为Python）： ```python params = { 'pallet_codes': ('A1001', 'A1002'), 'tech_batch_codes': ('TB2023X',), 'start_date': '2023-01-01', 'end_date': '2023-12-31' } cursor.execute(sql, params) ``` 3. **分区表** 按`operation_date`做范围分区，减少全表扫描： ```sql CREATE TABLE pipeline_operations ( ... ) PARTITION BY RANGE (operation_date); ``` #### 四、扩展场景 - **空值处理**：使用`COALESCE`设置默认值避免条件失效 - **性能监控**：通过`EXPLAIN ANALYZE`分析执行计划 - **分页查询**：添加`LIMIT :page_size OFFSET :page_num`