如何查询同一sample_id下同时存在pcode 94和95的记录

问题修正方案 #

你原SQL的统计粒度和判断逻辑不符合需求，可按以下两种方案调整：

方案1：分组去重统计（全数据库兼容）

如果仅需要获取符合条件的sample_id列表：

SELECT sample_id
FROM wq.wq_site_sample_result
WHERE pcode IN (94, 95)
GROUP BY sample_id
HAVING COUNT(DISTINCT pcode) = 2;

如果需要返回这些sample_id下对应94、95的全量记录：

SELECT t.sample_id, t.pcode, t.param_name
FROM wq.wq_site_sample_result t
JOIN (
    SELECT sample_id
    FROM wq.wq_site_sample_result
    WHERE pcode IN (94, 95)
    GROUP BY sample_id
    HAVING COUNT(DISTINCT pcode) = 2
) valid_ids ON t.sample_id = valid_ids.sample_id
WHERE t.pcode IN (94, 95);

方案2：交集查询（适配支持INTERSECT语法的数据库，如PostgreSQL、Oracle、SQL Server）

SELECT sample_id FROM wq.wq_site_sample_result WHERE pcode = 94
INTERSECT
SELECT sample_id FROM wq.wq_site_sample_result WHERE pcode = 95;

原SQL错误原因 #

1. GROUP BY字段包含param_name，统计粒度变为「同一样本下的单个参数」，HAVING条件只能匹配单个参数出现多次的样本，无法判断两个不同参数同时存在
2. 未对pcode去重统计，就算移除GROUP BY中的param_name，如果同一样本下单个pcode出现2次也会被误筛，必须通过COUNT(DISTINCT pcode) = 2确保两个pcode都存在。

内容的提问来源于stack exchange，提问作者Mr. Geologist