在两列上使用相关子查询

本文介绍了在两列上使用相关子查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

数据库小提琴

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");

上表显示了使用main_events 和sub_events 的不同campaigns 的购买过程.这些事件的层次结构是这样的:

The table above displays the purchasing process of different campaigns using main_events and sub_events. The hierarchy of those events is like this:

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

一旦活动在 main_event Storing 中，sub_events 适用.

Once a campaign is in the main_event Storing the sub_events apply.

现在，我想根据最高的 main_event 提取广告系列 - 或者如果他们已经有 sub_event - 基于最高的 sub_event.结果应该是这样的:

Now, I want to extract the campaigns based on their highest main_event - or in case they already have a sub_event - based on their highest sub_event. The result should look like this:

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

参考这个问题我想我有组合两个相关子查询:

IF sub_event IS NULL 则使用

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 );

如何连接那些相关子查询以获得预期结果?

How can I connect those correlated subqueries to get the expected result?

推荐答案

如果你有主键，这会简单得多:

This would be much simpler if you had a primary key:

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 );

这里是db<>小提琴.

Here is a db<>fiddle.