如何根据子选择将表连接在一起？

Question

我一直想弄清楚如何编写查询。基本上我有三个表（订单、产品、订单_产品），我想将它们连接在一起并应用一些过滤。

Orders table:
  ORDER_ID   CUSTOMER_ID
  1          1
  2          2

Products table:
  PRODUCT_ID   PRODUCT_NAME    PRODUCT_TITLE
  1            'P1'            'T1'
  2            'P1'            'T2'
  3            'P2'            'T3'
  4            'P2'            'T4'
  5            'P2'            'T5'
  6            'P3'            'T6'

Orders_Products table:
  ORDER_ID   PRODUCT_ID
  1          1
  1          3
  2          1
  2          3
  2          6

例如，我想获取（完全）由产品 P1/T1 和 P2/T3 组成的所有订单。我尝试过类似的方法，但这不起作用：

SELECT * FROM Orders
LEFT JOIN Orders_Products ON Orders_Products.ORDER_ID = Orders.ORDER_ID
LEFT JOIN Products ON Orders_Products.PRODUCT_ID = Products.PRODUCT_ID
WHERE EXISTS (SELECT * FROM Product WHERE PRODUCT_NAME = 'P1' AND PRODUCT_TITLE = 'T1')
AND EXISTS (SELECT * FROM Product WHERE PRODUCT_NAME = 'P2' AND PRODUCT_TITLE = 'T3');

编辑：为了澄清我真正需要实现的目标。用户应该能够搜索与给定产品匹配的订单。用户输入一个或多个产品名称/产品标题组合，并获取与该产品完全关联的所有订单。我（从 Web 应用程序）得到的只是名称/标题组合，我必须在查询中使用这些组合来获取 ORDER_ID。

Answer 1

SELECT OrderID, COUNT(*) AS ProductsCount
FROM Orders_Products
WHERE (PRODUCT_ID = 1 OR PRODUCT_ID = 3)
GROUP BY OrderID
HAVING COUNT(*) = 2

编辑：请忽略上述声明。看看下面的方法是否有效。

SELECT OrderID, 
SUM(CASE PRODUCT_ID WHEN 1 THEN 1 WHEN 3 THEN 1 ELSE 3 END)
AS ProductsCount
FROM Orders_Products
GROUP BY OrderID
HAVING SUM(CASE PRODUCT_ID WHEN 1 THEN 1 WHEN 3 THEN 1 ELSE 3 END) = 2

我想这应该会让您的订单仅这两种产品。

Answer 2

您可能无法在 MySQL 中编写简单的查询来实现此目的。但 ANSI SQL 支持表值构造函数，这简化了此类查询。

此基本查询返回订单的完整列表（5 行）：

SELECT * FROM Products 
JOIN Orders_Products ON Orders_Products.PRODUCT_ID = Products.PRODUCT_ID
JOIN Orders ON Orders_Products.ORDER_ID = Orders.ORDER_ID

此带有表值构造函数的查询返回您需要的订单：

SELECT * FROM Products 
JOIN Orders_Products ON Orders_Products.PRODUCT_ID = Products.PRODUCT_ID
JOIN Orders ON Orders_Products.ORDER_ID = Orders.ORDER_ID 
LEFT JOIN (VALUES('P1', 'T1'), ('P2', 'T3')) V(P_NAME, P_TITLE) ON PRODUCT_NAME = P_NAME AND PRODUCT_TITLE=P_TITLE

此查询对以上内容进行分组以返回 ORDER_ID，其中没有订单之外的订单必填列表（剔除含有null行的订单）：

SELECT ORDER_ID FROM Products 
JOIN Orders_Products ON Orders_Products.PRODUCT_ID = Products.PRODUCT_ID
JOIN Orders ON Orders_Products.ORDER_ID = Orders.ORDER_ID 
LEFT JOIN (VALUES('P1', 'T1'), ('P2', 'T3')) V(P_NAME, P_TITLE) ON PRODUCT_NAME = P_NAME AND PRODUCT_TITLE=P_TITLE
GROUP BY ORDER_ID HAVING COUNT(*) = 2

开源数据库中，HSQLDB是支持表值构造函数的数据库以及 ANSI SQL:2008 的其他用户友好功能