技术问询：如何查询集装箱内全部PO是否均达到指定状态

需求说明 #

需要从指定集装箱列表中，筛选出所有关联PO的状态均等于目标状态（如示例中的IN）的集装箱，或列出这些集装箱的全部PO记录。

示例数据 #

PO          COD_STC Container
DRYU9552662 R       003201511602
EGHU3582572 R       003201511602
TGBU6907376 A       003202788861
EITU1222407 IN      003202788861
EITU0309164 R       003202788861
SEKU6332900 IN      1059302779
CMAU1941417 IN      1059302779

解法1：仅获取符合条件的集装箱编号 #

通过分组聚合，对比集装箱的总PO数与目标状态PO数，判断是否全部达标：

SELECT Container
FROM sat_cont
WHERE Container IN ('003202788861','1059302779','003201511602')
GROUP BY Container
-- 总PO数 = 状态为IN的PO数，说明全PO达标
HAVING COUNT(*) = COUNT(CASE WHEN COD_STC = 'IN' THEN 1 END)

执行结果：仅返回1059302779（该集装箱的两个PO均为IN状态）。

解法2：获取符合条件的集装箱的全部PO记录 #

通过子查询先筛选出达标集装箱，再关联原表获取PO详情：

SELECT sc.PO, sc.COD_STC, sc.Container
FROM sat_cont sc
JOIN (
    SELECT Container
    FROM sat_cont
    WHERE Container IN ('003202788861','1059302779','003201511602')
    GROUP BY Container
    HAVING COUNT(*) = COUNT(CASE WHEN COD_STC = 'IN' THEN 1 END)
) valid_cont ON sc.Container = valid_cont.Container
ORDER BY sc.Container

执行结果：返回1059302779对应的两条PO记录。

解法3：用窗口函数实现（保留中间计算逻辑） #

适合需要查看每条PO对应集装箱达标情况的场景：

SELECT PO, COD_STC, Container
FROM (
    SELECT 
        PO, COD_STC, Container,
        -- 统计当前集装箱的总PO数
        COUNT(*) OVER (PARTITION BY Container) AS total_po,
        -- 统计当前集装箱中状态为IN的PO数
        COUNT(CASE WHEN COD_STC = 'IN' THEN 1 END) OVER (PARTITION BY Container) AS valid_po
    FROM sat_cont
    WHERE Container IN ('003202788861','1059302779','003201511602')
) t
-- 总PO数等于达标PO数则筛选
WHERE total_po = valid_po
ORDER BY Container

内容的提问来源于stack exchange，提问作者laoda