方式一关系除法Relational Division将实时值也作为一组键值对index_code, value通过JOIN GROUP BY HAVING直接判断每个sample_no是否所有指标都满足区间条件。核心思路把实时值表real_values与阈值表threshold_table按index_code关联。对每个sample_no进行分组检查组内匹配的记录数是否等于实时值的总指标数。如果相等说明该sample_no对所有指标都在区间内。WITH realtime(index_code, value) AS ( VALUES (A_CMMJ038, 20.0), (A_CMMJ039, 30.0) ) SELECT lt.sample_no FROM wis_model_work lt JOIN realtime rt ON lt.index_code rt.index_code WHERE rt.value BETWEEN lt.lower_limit AND lt.upper_limit GROUP BY lt.sample_no HAVING COUNT(DISTINCT lt.index_code) (SELECT COUNT(*) FROM realtime);方式二将总数提取为变量/独立 CTE推荐避免在HAVING中反复计算总数。WITH realtime(index_code, value) AS ( VALUES (A_CMMJ038, 20.0), (A_CMMJ039, 30.0) -- ... ), -- 提前计算好需要的指标总数 total_cnt AS ( SELECT COUNT(*) AS cnt FROM realtime ) SELECT lt.sample_no FROM wis_model_work lt JOIN realtime rt ON lt.index_code rt.index_code CROSS JOIN total_cnt tc -- 关联总数避免子查询 WHERE rt.value BETWEEN lt.lower_limit AND lt.upper_limit GROUP BY lt.sample_no, tc.cnt HAVING COUNT(DISTINCT lt.index_code) tc.cnt;方式三使用窗口函数替代 HAVING适用于大数据量如果wis_model_work表数据量极大GROUP BYHAVING的聚合成本很高。可以先过滤再用窗口函数统计WITH realtime(index_code, value) AS ( VALUES (A_CMMJ038, 20.0), (A_CMMJ039, 30.0) -- ... ), total_cnt AS (SELECT COUNT(*) AS cnt FROM realtime), filtered AS ( SELECT lt.sample_no, lt.index_code FROM wis_model_work lt JOIN realtime rt ON lt.index_code rt.index_code WHERE rt.value BETWEEN lt.lower_limit AND lt.upper_limit ) SELECT DISTINCT sample_no FROM filtered, total_cnt WHERE COUNT(DISTINCT index_code) OVER (PARTITION BY sample_no) total_cnt.cnt;用springboot处理实体类MetricValue.javapublic class MetricValue { private String indexCode; private Double value; // 构造器、getter、setter 省略 }Mapper 接口ThresholdMapper.javaMapper public interface ThresholdMapper { /** * 根据动态指标列表查询匹配的 sample_no * param metrics 指标列表至少包含一个元素 * return 匹配的样品编号列表 */ ListLong findSampleNos(Param(metrics) ListMetricValue metrics); }Mapper XMLThresholdMapper.xml?xml version1.0 encodingUTF-8 ? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.mapper.ThresholdMapper select idfindSampleNos resultTypelong WITH realtime(index_code, value) AS ( foreach collectionmetrics itemitem separatorUNION ALL SELECT #{item.indexCode}, #{item.value} /foreach ) SELECT lt.sample_no FROM wis_model_work lt JOIN realtime rt ON lt.index_code rt.index_code WHERE rt.value BETWEEN lt.lower_limit AND lt.upper_limit GROUP BY lt.sample_no HAVING COUNT(DISTINCT lt.index_code) (SELECT COUNT(*) FROM realtime) /select /mapper