如果满足每个条件一个条件，则列出所有值

DB-Fiddle：

CREATE TABLE operations (
    id int auto_increment primary key,
    campaign VARCHAR(255),
    supplier VARCHAR(255),
    supplier_rating VARCHAR(255),
    quantity INT
);

INSERT INTO operations
(campaign, supplier, supplier_rating, quantity)
VALUES 
("C001", "Supplier_A", "high", "300"),
("C001", "Supplier_D", "high", "420"),
("C001", "Supplier_F", "low", "200"),

("C002", "Supplier_L", "high", "910"),
("C002", "Supplier_B", "mid", "370"),

("C003", "Supplier_Z", "mid", "136"),
("C003", "Supplier_M", "low", "247"),
("C003", "Supplier_C", "high", "496");

预期结果：

     campaign    |     supplier      |     supplier_rating
-----------------|-------------------|-----------------------------
    C001         |    Supplier_A     |     high
    C001         |    Supplier_D     |     high
    C001         |    Supplier_F     |     low
-----------------|-------------------|-----------------------------
    C003         |    Supplier_Z     |     mid
    C003         |    Supplier_M     |     low
    C003         |    Supplier_C     |     high

在上面的示例中，我要查询所有campaigns带有supplierwith的supplier_ratingwith low。
所有campaigns这fullfill这个条件应与它们对应的上市supplier，并supplier_rating在结果中。

我尝试使用此查询：

SELECT 
campaign,
supplier,
supplier_rating,
(CASE WHEN MAX(supplier_rating = 'low') OVER (PARTITION BY campaign) = 1
THEN 'low' ELSE supplier_rating END) AS supplier_evaluation2
FROM operations;

它接近的结果，而是显示了每个的supplier_rating每supplier它会显示low所有的suppliers每campaign它也并不排斥C002。

我如何修改此查询以达到预期的结果？