代码之家 › 专栏 › 技术社区 › Michi

在两列上使用相关子查询

sql mysql

Michi · 技术社区 · 5 年前

CREATE TABLE Purchasing (
    campaign VARCHAR(255),
    main_event VARCHAR(255),
    sub_event VARCHAR(255),
    quantity VARCHAR(255)
);

INSERT INTO Purchasing
(campaign, main_event, sub_event, quantity)
VALUES 
("C001", "Offered", NULL, "500"),
("C001", "Ordered", NULL, "450"),
("C001", "Storing", "Delivered", "465"),
("C001", "Storing", "Recorded", "440"),
("C001", "Storing", "Completed", "445"),

("C002", "Offered", NULL, "600"),
("C002", "Ordered", NULL, "700"),
("C002", "Storing", "Delivered", "690"),
("C002", "Storing", "Recorded", "692"),

("C003", "Offered", NULL, "300"),
("C003", "Ordered", NULL, "250"),
("C003", "Storing", "Delivered", "320"),

("C004", "Offered", NULL, "800"),
("C004", "Ordered", NULL, "870"),
("C004", "Storing", "Delivered", "740"),

("C005", "Offered", NULL, "240"),
("C005", "Ordered", NULL, "250"),
("C005", "Storing", "Delivered", "226"),

("C006", "Offered", NULL, "100"),
("C006", "Ordered", NULL, "105"),

("C007", "Offered", NULl, "900"),
("C008", "Offered", NULl, "400");

上表显示了不同 campaigns 使用 main_events sub_events .
这些事件的层次结构如下:

main_event = Storing > Ordered > Offered 
sub_event = Completed > Recorded > Delivered

main_event Storing 子事件 申请。

现在,我想根据他们的 -或者万一他们已经有了 sub_event -基于他们的最高 子事件

Campaign       main_event     sub_event         Quantity
C001           Storing        Completed          445
C002           Storing        Recorded           692
C003           Storing        Delivered          320
C004           Storing        Delivered          740
C005           Storing        Delivered          226
C006           Ordered        NULL               105
C007           Offered        NULL               900
C008           Offered        NULL               400

关于这个 question correlated subquery :

如果sub_事件为空,则使用

SELECT
campaign, 
main_event,
quantity
FROM Purchasing p
WHERE main_event = (SELECT p2.main_event
                    FROM Purchasing p2
                    WHERE p2.campaign = p.campaign
                    ORDER BY field(p2.main_event, 'Storing', 'Ordered', 'Offered')
                    LIMIT 1
                   );

SELECT
campaign, 
sub_event,
quantity
FROM Purchasing p
WHERE sub_event = (SELECT p3.sub_event
                    FROM Purchasing p3
                    WHERE p3.campaign = p.campaign
                    AND p3.sub_event IS NOT NULL
                    ORDER BY field(p3.sub_event, 'Completed', 'Recorded', 'Delivered')
                    LIMIT 1
                   );

我如何连接这些 correlated subqueries 为了得到预期的结果?

1 回复 | 直到 5 年前

Gordon Linoff 5 年前

如果您有一个主键,这将简单得多:

CREATE TABLE Purchasing (
    purchasing_id int auto_increment primary key,
    campaign VARCHAR(255),
    main_event VARCHAR(255),
    sub_event VARCHAR(255),
    quantity VARCHAR(255)
);

简单地说:

SELECT p.*
FROM Purchasing p
WHERE p.purchasing_id = (SELECT p2.purchasing_id
                         FROM Purchasing p2
                         WHERE p2.campaign = p.campaign
                         ORDER BY field(p2.main_event, 'Storing', 'Ordered', 'Offered'),
                                  field(p2.sub_event, 'Completed', 'Recorded', 'Delivered')
                         LIMIT 1
                        );

Here