sql - 查询以匹配外键关系

我在一个this is Postgres数据库中有两个表，表示来自市场的简单订单。一个包含订单信息的主表，一个包含采购细节的明细表，一个外键返回主表。很简单。我想从市场上订购上千份，根据所购买的商品和数量，找到一些具体的订单。我还有两张桌子，风格相似，一张是主人，一张是小孩，在那里我从市场上制作了一个“包”和一些细节。例如：Pack A包含2个苹果和3个桔子。我在表格里定义的。现在我想知道有多少订单，以及市场上的哪一个订单与具体的组合完全匹配。很重要的一点是，这是一个精确的匹配。包含附加产品或数量不同的订单不匹配。在SQL Fiddle中，我用数据设置了一个简单的示例。原始DDL如下。表中的两个订单应与A包相匹配。http://sqlfiddle.com/#!17/b4f55CREATE TABLE customer_order( order_id serial PRIMARY KEY NOT NULL, customer_name VARCHAR(100) NOT NULL);CREATE TABLE order_detail( id serial PRIMARY KEY, order_id INTEGER, item_sku VARCHAR(50), item_quantity INTEGER, FOREIGN KEY(order_id) REFERENCES customer_order(order_id));INSERT INTO customer_order (customer_name) VALUES ('John');INSERT INTO customer_order (customer_name) VALUES ('Mary');INSERT INTO customer_order (customer_name) VALUES ('Bill');INSERT INTO order_detail (order_id, item_sku, item_quantity) VALUES (1, 'APPLE', 2);INSERT INTO order_detail (order_id, item_sku, item_quantity) VALUES (1, 'ORANGE', 3);INSERT INTO order_detail (order_id, item_sku, item_quantity) VALUES (2, 'ORANGE', 5);INSERT INTO order_detail (order_id, item_sku, item_quantity) VALUES (3, 'APPLE', 2);INSERT INTO order_detail (order_id, item_sku, item_quantity) VALUES (3, 'ORANGE', 3);CREATE TABLE pack_master( pack_id serial PRIMARY KEY NOT NULL, name VARCHAR(100) NOT NULL);CREATE TABLE pack_child( id serial PRIMARY KEY, pack_id INTEGER, item_sku VARCHAR(50), item_quantity INTEGER, FOREIGN KEY(pack_id) REFERENCES pack_master(pack_id));INSERT INTO pack_master (name) VALUES ('Pack A');INSERT INTO pack_master (name) VALUES ('Pack B');INSERT INTO pack_child (pack_id, item_sku, item_quantity) VALUES (1, 'APPLE', 2);INSERT INTO pack_child (pack_id, item_sku, item_quantity) VALUES (1, 'ORANGE', 3);INSERT INTO pack_child (pack_id, item_sku, item_quantity) VALUES (2, 'GRAPES', 5); 最佳答案 假设pack_child (pack_id, item_sku)和order_detail (order_id, item_sku)被定义为UNIQUE，这将起作用：SELECT pc.pack_id, od.order_idFROM pack_child pcLEFT JOIN order_detail od USING (item_sku, item_quantity)GROUP BY 1, 2HAVING count(*) = count(od.id) -- every item of the pack has a matchAND NOT EXISTS ( SELECT FROM order_detail od1 LEFT JOIN pack_child pc1 ON pc1.item_sku = od1.item_sku AND pc1.item_quantity = od1.item_quantity AND pc1.pack_id = pc.pack_id WHERE od1.order_id = od.order_id AND pc1.id IS NULL -- and order has no additional item );返回完全匹配的所有pack_id和order_id对。分贝小提琴here有一百零一种可供选择的方法来编写查询。最快的速度取决于基数、数据分布、约束以及最重要的可用索引。这是relational-division的一个特殊应用。以下是一系列技巧：How to filter SQL results in a has-many-through relation另一种方法可能更快：创建视图或materialized views父表，包括项计数：CREATE MATERIALIZED VIEW v_pack_master ASSELECT *FROM pack_masterJOIN ( SELECT pack_id, count(*) AS items FROM pack_child GROUP BY 1 ) c USING (pack_id);CREATE MATERIALIZED VIEW v_customer_order ASSELECT *FROM customer_orderJOIN ( SELECT order_id, count(*) AS items FROM order_detail GROUP BY 1 ) c USING (order_id);（订单通常不会在以后更改，因此可能是物化视图的可行候选项。）只有在可以有多个订单项的情况下，索引才能付款（按此顺序排列的索引表达式）：CREATE INDEX foo ON v_customer_order (items, order_id);现在，查询只考虑以匹配项计数开头的订单：SELECT * -- pack_id, order_idFROM v_pack_master pmLEFT JOIN v_customer_order co USING (items)JOIN LATERAL ( SELECT count(*) AS items FROM pack_child pc JOIN order_detail od USING (item_sku, item_quantity) WHERE pc.pack_id = pm.pack_id AND od.order_id = co.order_id ) x USING (items);.. 然后，如果所有项目都匹配，我们就不必再排除其他项目了。我们可以立即使用父表中的所有列，以返回您想要返回的任何内容。。。关于sql - 查询以匹配外键关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53879487/