如何选择同一订单明细中的两个产品 [英] How to select two products where it is in same OrderDetails

问题描述

如何在OrderDetails中选择包含两个产品的订单?

How do I select an order with two products in the OrderDetails?

例如，如果我有 4 个订单:

For example, if I have 4 orders:

order id: 11000 contains: p1, p3, p5, p9
order id: 12000 contains: p1, p4, p5, p8
order id: 13000 contains: p2, p3, p5, p7
order id: 14000 contains: p1, p3, p5, p8
order id: 15000 contains: p2, p3, p6, p9

我想选择包含 p1 和 p9 的订单 ID

I want to select order ids where they contain p1 and p9

预期结果:11000、12000、14000

The expected result: 11000, 12000, 14000

如何在 SQL Server 中执行此操作?

How do I do this in SQL Server?

推荐答案

您可以使用以下内容:

SELECT id
FROM OrderDetails
WHERE Product IN ('p1','p9')
GROUP BY id
HAVING COUNT(DISTINCT Product) = 2

获取满足条件集的OrderDetails PK值.

to get the OrderDetails PK value that satisfies the condition set.

要获取 PK 值的计数，您可以将上述查询包装在子查询和 COUNT 中:

To get the count of the PK values, you can wrap the above query in a subquery and COUNT:

SELECT COUNT(*)
FROM (
  SELECT id
  FROM OrderDetails
  WHERE Product IN ('p1','p9')
  GROUP BY id
  HAVING COUNT(DISTINCT Product) = 2) AS t

这篇关于如何选择同一订单明细中的两个产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！