优先级查询如何工作(如果满足条件，一个值优先于另一个值) [英] How does a prioritization query work (one value is preferred over another if a criteria is met)

问题描述

DB-Fiddle

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

INSERT INTO Purchasing
(Campaign, Event_Type, Quantity_Offer, Quantity_Order, Quantity_Received, Quantity_Stored)
VALUES 
("C001", "Offered", "300", NULL, NULL, NULL),
("C001", "Ordered", NULL, "350", NULL, NULL),
("C001", "Received", NULL, NULL, "348", NULL),
("C001", "Stored", NULL, NULL, NULL, "345"),

("C002", "Offered", "800", NULL, NULL, NULL),
("C002", "Ordered", NULL, "720", NULL, NULL),
("C002", "Received", NULL, NULL, "730", NULL),
("C002", "Stored", NULL, NULL, NULL, "735"),

("C003", "Offered", "600", NULL, NULL, NULL),
("C003", "Ordered", NULL, "400", NULL, NULL),
("C003", "Received", NULL, NULL, "410", NULL),

("C004", "Offered", "150", NULL, NULL, NULL),
("C004", "Ordered", NULL, "200", NULL, NULL),

("C005", "Offered", "430", NULL, NULL, NULL),
("C005", "Ordered", NULL, "440", NULL, NULL),

("C006", "Offered", "520", NULL, NULL, NULL),
("C006", "Ordered", NULL, "670", NULL, NULL),

("C007", "Offered", "330", NULL, NULL, NULL),
("C008", "Offered", "780", NULL, NULL, NULL);

该表格使用event_types 显示了不同的campaigns 及其在购买过程中的状态.
流程如下:

The table displays different campaigns and their status within the purchasing process using event_types.
The process is as the following:

Stored > Received > Ordered > Offered

<小时>

基于此结构，结果应如下所示:

---

Based on this structure the result should look like this:

Campaign        Event_Type         Quantity
C001              Stored              345
C002              Stored              735
C003              Received            410
C004              Ordered             200
C005              Ordered             440
C006              Ordered             670
C007              Offered             330
C008              Offered             780

为了实现这一点，我使用问题 此处:

In order to achieve this I go with the prioritization query described in the question here:

select 
campaign, 
event_type,
coalesce(Quantity_Offered, Quantity_Ordered, Quantity_Received, Quantity_Stored) 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, 'Stored', 'Received', 'Ordered', 'Offered')
                    limit 1
                   );

这个查询给了我我需要的结果.
但是，我想知道这个查询实际上是如何工作的，因为当我只运行这部分时:

This query gives me exactly the result I need.
However, I am wondering how this query actually works because when I run only this part:

select p2.event_type
from Purchasing p2
order by field(p2.event_type, 'Stored', 'Received', 'Ordered', 'Offered')
limit 1;

结果就是:

event_type
Stored

查询如何知道 event_type 中的哪个在 stored 之后?

How does the query know which of the event_type comes after stored?

推荐答案

数据库小提琴

第 1 步:
相关子查询 - 在本例中 WHERE p2.campaign = p.campaign - 遍历所有 campaigns 并识别所有可用的 event_types 每个活动.
基本上，每个campaign

Step 1:
The correlated subquery - in this case WHERE p2.campaign = p.campaign - goes through all campaigns and identifies all available event_types per campaign.
Basically, the inner query is re-executed for each campaign

SELECT 
p2.event_type
FROM Purchasing p2
WHERE Campaign ="C003";

https://en.wikipedia.org/wiki/Correlated_subquery

第 2 步:
之后，每个 campaign 的所有可用结果都按定义的顺序排序field 函数中 event_type 的层次结构.

Step 2:
Afterwards all available results per campaign are ordered by the defined hierarchy of the event_type in the field function.

SELECT p2.event_type, 
field(event_type, 'Stored', 'Received', 'Ordered', 'Offered') as Position_in_Field
FROM Purchasing p2
ORDER BY 2;

https://www.w3resource.com/mysql/string-functions/mysql-field-function.php

第 3 步:
最后，LIMIT 1 只留下最高可用的 event_type在结果中，因此外部查询的WHERE子句得到每个 campaign 的最高 event_type.

Step 3:
Finally, the LIMIT 1 only leaves the highest available event_type in the result and thus the WHERE clause of the outer query gets the highest event_type per campaign.

SELECT 
p2.event_type
FROM Purchasing p2
WHERE Campaign ="C003"
ORDER BY field(p2.event_type, 'Stored', 'Received', 'Ordered', 'Offered')
LIMIT 1;

这篇关于优先级查询如何工作(如果满足条件，一个值优先于另一个值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！