sql - 奖励符合条件的产品

Question

products purchased
--------------------------
bana
bana
bana
stra
kiwi

reward requirements table (related to a rewards table)
reward id, products
----------------------
1,bana
1,bana
1,bana
2,stra
2,bana
3,stra
4,cart
5,bana
5,bana
5,oliv

能帮我用sql获取奖励吗购买的产品是否符合条件？

在上述情况下，奖励 ID 为:

1
2
3

如果有更好的设计可以使解决方案更容易，我也欢迎。我希望使用产品名称是为了更容易解释。 (稍后我将替换为产品 ID)

Answer 1

此查询将解决您的问题。

select r.reward_id
from (
  select reward_id, product, count(*) needed
  from reward_requirements
  group by reward_id, product
) r
left join (
  select product, count(*) bought
  from products_purchased
  group by product 
) p on r.product=p.product and p.bought >= r.needed
group by r.reward_id
having count(reward_id) = count(distinct p.product)
order by r.reward_id

为了使您的设计更好，您可以重做 reward_requirements 以包含列(产品，需要)，而不必多次列出。它还将摆脱第一个子查询。