看到这里了吗
我的table和你的名字不一样，但这证明了一点：

select sets_x_consumers.consumer_id, sets_x_consumers.set_id, 
  set_summary.items_in_set = consumer_summary.items_per_set_per_consumer as set_is_complete
from (
  -- build a cross-product of sets and consumers
  select distinct set_id, consumer_id
  from sets join consumers -- no join condition -> cross product
) sets_x_consumers 
inner join
( -- the total number of items in each set per set_id
  select set_id, count(*) items_in_set
  from sets 
  group by set_id
) set_summary on sets_x_consumers.set_id = set_summary.set_id
inner join
( -- the total number of items per set and customer 
  select set_id, consumer_id, count(*) items_per_set_per_consumer
  from sets 
  inner join consumers on sets.prize_id = consumers.prize_id
  group by consumer_id, set_id
) consumer_summary on sets_x_consumers.set_id = consumer_summary.set_id and sets_x_consumers.consumer_id = consumer_summary.set_id

我的基本想法是把每套商品的数量和每一位消费者要求的每套商品的数量加起来。只要消费者和奖品对没有重复条目，这应该可以工作（如果允许重复，我将使用 count distinct(prize_id) 对于 consumer_summary ).
以上查询的输出为：

| consumer_id | set_id | set_is_complete |
|-------------|--------|-----------------|
|           1 |      1 |               1 |
|           2 |      2 |               0 |

这将列出每对消费者和消费者至少有一个奖品的集合(要将其更改为列出每个使用者集组合，请使用 outer join )
在此基础上，只列出完整集或汇总完整集的数量应该很容易；-）

无法真正理解上一列“count”的含义，但这里有一个解决方案列出了用户及其完成的集合。
演示链接
整个想法是计算每套所需的奖品数量，并计算每套每一位顾客所获得的奖品，这样你就可以加入这两个行列。
我知道这是mssql，但我没有设法让mysql CTE在sqfiddle中工作。cte-s基本上只不过是一个子查询。如果您的服务器不支持cte-s，您可以改用普通子查询或临时表。

值得一提的是，我在SQLServer中为此提供了一个很好的例程。即使在每个集合中都有重叠的prize\u id值（如果不明确，则默认为更高的setid），这种方法仍然有效。假设所有临时表都是原始数据：

declare @awarded_prize table (rowid int identity, consumer_id int, prize_id int )
insert @awarded_prize
select * from #awarded_prizes

declare @collections table ( set_id int, prize_id int, rownumber int , filled int)
insert @collections
select *, row_number() over(partition by set_id order by set_id, prize_id) , null
from #collections

declare @todelete table (rowid int)
declare @scorecard table (consumer_id int, set_id int)

declare @iterator int=1
declare @prize_id int
declare @set_id int = (Select min(set_id) from @collections)
declare @consumer_id int = (Select min(consumer_id) from @awarded_prize)
while @consumer_id<=(select max(consumer_id) from @awarded_prize)
    begin
    while @set_id<=(select max(set_id) from @collections)
      begin
        while 1=1
        begin
        select @prize_id=prize_id 
        from @collections 
        where set_id=@set_id and rownumber=@iterator

        if (select max(rowid) from @awarded_prize where prize_id=@prize_id and consumer_id=@consumer_id and rowid not in (select rowid from @todelete)) is null break
        insert @todelete
        select max(rowid) from @awarded_prize where prize_id=@prize_id and consumer_id=@consumer_id and rowid not in (select rowid from @todelete)

        update @collections set filled=1 
        where rownumber=@iterator and set_id=@set_id

        if not exists(select 1 from @collections where set_id=@set_id and filled is null)
        begin
            insert @scorecard
            select @consumer_id, @set_id
            delete @awarded_prize where rowid in (Select rowid from @todelete) 
            delete @todelete
            update @collections set filled=null where filled=1
        end
        set @iterator=case when @iterator=(Select max(rownumber) from @collections where set_id=@set_id) then 
        (select min(rownumber) from @collections where set_id=@set_id) else @iterator+1 end 
      end
      delete @todelete
      set @iterator=1
      set @set_id=@set_id+1
    end
    set @iterator=1
    select @set_id=min(set_id) from @collections
    select @consumer_id=min(consumer_id) from @awarded_prize where consumer_id>@consumer_id
    end

    select consumer_id, set_id, count(*) complete_sets 
    from @scorecard
    group by consumer_id, set_id
    order by consumer_id, set_id