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", "mid", "200"),
("C002", "Supplier_A", "high", "180"),
("C002", "Supplier_D", "high", "612"),
("C002", "Supplier_F", "low", "367"),
("C003", "Supplier_L", "low", "763"),
("C004", "Supplier_G", "low", "478"),
("C005", "Supplier_B", "mid", "125"),
("C006", "Supplier_Z", "mid", "136"),
("C006", "Supplier_M", "low", "247"),
("C006", "Supplier_C", "high", "496");
预期成绩:
campaign | supplier | supplier_rating
-----------------|-------------------|-----------------------------
C002 | Supplier_A | high
C002 | Supplier_D | high
C002 | Supplier_F | low
-----------------|-------------------|-----------------------------
C006 | Supplier_Z | mid
C006 | Supplier_M | low
C006 | Supplier_C | high
在上面的例子中,我想查询活动是
一个)有多个供应商和
B),如果这些供应商中的至少一个具有supplier_rating
low
的运动应与所有供应商的相应上市。
我试图去与htis查询:
SELECT
campaign,
supplier,
supplier_rating
FROM operations
WHERE campaign IN
(SELECT
campaign
FROM operations
WHERE supplier_rating = 'low');
它几乎给了我预期的结果,但不排除C003
和C004
。
基本上,a)
不考虑条件。
我如何修改查询,以便在结果中显示条件a)
和条件b)
?
假设供应商在广告活动中是唯一的,那么窗口功能是一个更简单的解决方案:
SELECT o.*
FROM (SELECT o.*,
COUNT(*) OVER (PARTITION BY campaign) as num_supplier,
SUM(supplier_rating = 'low') OVER (PARTITION BY campaign) as num_low
FROM operations o
) o
WHERE num_supplier > 1 AND num_low > 0;
或者你也可以使用聚合来定义活动,然后使用IN
,EXISTS
或JOIN
得到原始行:
SELECT o.*
FROM operations o
WHERE o.campaign IN (SELECT o2.campaign
FROM operations o2
GROUP BY o2.campaign
HAVING COUNT(*) > 1 AND SUM(supplier_rating = 'low') > 0
);
这是db <>小提琴。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句