按x分组，其中y = A，B和C

本文介绍了按x分组，其中y = A，B和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