按x分组，其中y = A，B和C [英] Group by x where y = A and B and C

问题描述

我有一个存储产品价格信息的表.它具有产品ID，数量和折扣的列. (用户购买2折1英镑，购买3折2英镑，等等).

I have a table that stores product price information. It has a column for the product ID, a quantity and a discount. (Users buys 2 gets £1 off, buys 3 gets £2 off etc).

我想拉出所有具有特定数量折扣的产品.例如，只有分别购买1,2或3可以分别减价5英镑，10英镑和20英镑的产品.

I'd like to pull all products that have specific quantity discounts associated with them. For example only products that have £5, £10 and £20 off buying 1,2 or 3 respectively.

我该怎么做?

我想按产品ID分组，其中有三行符合我的条件，而没有其他产品.

I want to group by product ID where there is are three rows that match my criteria - and no other products.

推荐答案

未经测试(取决于您的架构)，但是这里是这样的想法:

Not tested (depends on your schema) but here's the idea:

SELECT P.id, count(P.id)
FROM products P
  LEFT JOIN discounts D ON P.id = D.product_id
WHERE
  (D.quantity = 1 AND D.amount = 5)
  OR (D.quantity = 2 AND D.amount = 10)
  OR (D.quantity = 3 AND D.amount = 20)
GROUP BY P.id
HAVING count(P.id) = 3

这篇关于按x分组，其中y = A，B和C的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！