查找重复行中缺失的条目

Question

mySQL查询-我有一个表'数据库'。'table_product_options'包含大约1000行。该表应为每个'product_id'包含4x行—分配给每个'customer_group'的相同产品(如下所示)。这应该等于每个customer_group约250行。

然而，我对每个'customer_group'运行计数，发现组a+ c有少量缺失条目(组b+ d各250行，但组a+ c只有245行)。

+----------------+----------------+
| product_id     | customer_group |
+----------------+----------------+
|       1        |        a       | }
|       1        |        b       | }
|       1        |        c       | }-each 'product_id' has 4x 'customer_group' attached
|       1        |        d       | }
|       2        |        a       |
|       2        |        b       |
|       2        |        c       | << 1 missing entry (customer_group = d)
|       3        |        a       | << 3 missing entries (customer_group = b, c, d)
+----------------+----------------+

我如何运行一个SELECT查询(或替代)来显示哪个'product_id'有少于4行分配?或者，换句话说，我如何找到缺失的项?

我已经尝试了许多不同的查询，包括select与WHERE NOT EXIST等相结合，但我已经没有想法了。我已经修改了一打stackoverflow的答案，但没有一个是非常合适的，还没有能够修改语法的工作。

任何帮助将是非常感激的(因为我预计同样的问题在未来再次出现…)我最后可能会用眼睛擦桌子!

Answer 1

select product_id, count(customer_group)
from table_product_options
group by product_id
having count(customer_group) < 4

您可能需要切换目录和按行分组。我把顺序搞混了

Answer 2

组成查找缺失组的可能组合。

select product_id, indicator, case when indicator = 1 
                                   case when availe = 'a' then 'b,c,d' 
                                   when availe = 'b' then 'a,c,d' 
                                   when availe = 'c' then 'a,b,d'
                                   else 'a,b,c' end
                               case when indicator = 2
                                     case when availe = 'a,b' then 'c,d'
                 //Like wise Give the possible combination for finding missing group
                                end as missing_group 
 from
(select product_id, count(customer_group) as indicator, group_concat(customer_group) as availe
from table_product_options
group by product_id) a where indicator < 4