仅显示最新可用事件类型的值

一尘不染

仅显示最新可用事件类型的值

sql

CREATE TABLE Purchasing (
Event_Type VARCHAR(255),
Campaign VARCHAR(255),
Quantity_Offer VARCHAR(255),
Quantity_Order VARCHAR(255),
Quantity_Received VARCHAR(255)
);

INSERT INTO Purchasing
(Event_Type, Campaign, Quantity_Offer, Quantity_Order, Quantity_Received)
VALUES 
("Offer", "C001", "300", NULL, NULL),
("Offer", "C002", "200", NULL, NULL),
("Offer", "C003", "500", NULL, NULL),
("Offer", "C004", "600", NULL, NULL),
("Offer", "C005", "400", NULL, NULL),
("Offer", "C006", "300", NULL, NULL),
("Order", "C001", NULL, "320", NULL),
("Order", "C002", NULL, "180", NULL),
("Order", "C003", NULL, "450", NULL),
("Order", "C004", NULL, "630", NULL),
("Received", "C001", NULL, NULL, "310"),
("Received", "C002", NULL, NULL, "190");

在上方的表格中，使用Event_Type和来显示Quantity适用于的不同广告系列的购买过程Event_Type。每个广告系列只能有Event_Type一次。

现在，我想加时赛得到Quantity的 最新的可用状态 的每个采购过程中Campaign。
总结逻辑如下：

Received kills Order
Order kills Offer

因此，结果应如下所示：

Campaign        Event_Type        Quantity
C001              Received          310
C002              Received          190
C003              Order             450
C004              Order             630
C005              Offer             400
C006              Offer             300

为了实现这一点，我尝试使用该CASE功能，但无法使其正常工作：

SELECT
Campaign,
Event_Type,
(CASE 
 WHEN Event_Type = "Order"  THEN SUM(Quantity_Order) 
 WHEN Event_Type = "Offer"  THEN SUM(Quantity_Offer) 
 ELSE SUM(Quantity_Received) 
 END) AS Quantity
FROM Purchasing
GROUP BY 1;

我需要进行哪些更改才能使其正常工作？

阅读 137

2021-03-17

共1个答案

一尘不染

这是一个优先级查询。

在MySQL 8+中，使用窗口函数：

select campaign, event_type,
       coalesce(Quantity_Offer, Quantity_Order, Quantity_Received) as quantity
from (select p.*,
             row_number() over (partition by campaign order by field(event_type, 'Received', 'Order', 'Offer')) as seqnum
      from Purchasing p
     ) p
where seqnum = 1;

在早期版本中，一个简单的方法是相关子查询：

select campaign, event_type,
       coalesce(Quantity_Offer, Quantity_Order, Quantity_Received) as quantity
from Purchasing p
where event_type = (select p2.event_type
                    from Purchasing p2
                    where p2.campaign = p.campaign
                    order by field(p2.event_type, 'Received', 'Order', 'Offer')
                    limit 1
                   );

如果您有大量数据，则需要Purcahsing(campaign, event_type)此查询的索引。

2021-03-17