在一列中计算重复的SQL，但是重复的条件不仅限于一个固定值。

Question

我尝试搜索其他文章，但只能找到关于查找一个固定值的重复项的内容。

现在想象一下以下表格：

 ╔══════════╦═══════╗
 ║ customer ║ color ║
 ╠══════════╬═══════╣
 ║        1 ║ black ║
 ║        1 ║ black ║
 ║        2 ║ red   ║
 ║        2 ║ black ║
 ║        3 ║ red   ║
 ║        3 ║ red   ║
 ║        3 ║ red   ║
 ║        4 ║ black ║
 ║        5 ║ black ║
 ║        5 ║ green ║
 ║        6 ║ purple║
 ╚══════════╩═══════╝

我想选择“重复项”，即以下客户：

• 有多个黑色
• 一个黑色和其他红色也是重复的
• 无重复项：客户可以拥有任意数量的红色

目前的情况

目前我只能选择关于黑色重复项的内容，但我无法将其与“一个黑色，没有更多红色”的条件结合起来。

SELECT customer FROM events WHERE
    color = 'black'
    group by customer
    having count(*) > 1

也许我可以先数一下黑色，然后再加入现有表格，计算额外的黑色和红色数量?

期望输出

我希望得到以下客户结果: 1,2。 更好的输出方式是，我知道客户是双倍黑还是黑+一些红色:

╔══════════╦═══════════╦══════════════╗
║ customer ║ blackOnly ║ blackPlusRed ║
╠══════════╬═══════════╬══════════════╣
║        1 ║ yes       ║ no           ║
║        2 ║ no        ║ yes          ║
╚══════════╩═══════════╩══════════════╝

很抱歉，我修改了我的帖子

• 我在示例表中添加了客户5和6以及更多颜色。因此，可能有些建议不再适用 :-(。 （只是想快速编辑，如果我没有遵循某些修改规则，请告诉我）
• 非常感谢迄今为止已经给出的非常快速的答案。

- timguy

---

如果客户1也有一个红色的，他还会是黑色吗？ - pablomatico

从以下三个响应中选择一个吧 :-) - Tim Biegeleisen

@pablomatico：没错，我没有明确指定这一点。最好的办法是同时考虑两个方面，然后将“blackOnly”的标签改为“blackDouble”。但更重要的是正确选择客户。谢谢。 - timguy

关于新颜色，您没有提及您期望的结果。 - Neria Nachum

@NeriaNachum：结果不应因新颜色而改变。 "重复"的含义应遵循相同的条件。我不得不添加它，因为有些解决方案正确地假设示例是问题的整个集合，因此他们构建了自己的解决方案以满足简化条件。 - timguy

Answer 1

这个查询首先创建一个临时表，其中包含每个客户的黑色和红色计数，然后查询此表以获取每个客户的blackOnly和blackPlusRed列值。

SELECT t.customer,
    CASE WHEN t.black > 1 AND t.red = 0 THEN 'yes' ELSE 'no' END AS blackOnly,
    CASE WHEN t.black > 0 AND t.red > 0 THEN 'yes' ELSE 'no' END AS blackPlusRed
FROM
(
    SELECT *,
        SUM(CASE WHEN color='black' THEN 1 ELSE 0 END) AS black,
        SUM(CASE WHEN color='red' THEN 1 ELSE 0 END) AS red
    FROM events
    GROUP BY customer
) t

如果你想添加一个新的颜色条件，例如只有红色，那么你可以在外部查询中添加一个新的 CASE 语句：

CASE WHEN t.red > 1 AND t.black = 0 THEN 'yes' ELSE 'no' END AS redOnly

这是一个演示: SQLFiddle

- Tim Biegeleisen

---

不应该鼓励使用非标准的双引号来表示字符串字面量，而应该使用单引号。 - Thorsten Kettner

MySQL容忍许多不符合ANSI标准的事情，例如允许在GROUP BY中使用非聚合列。这是非常糟糕的做法，但欢迎来到开源世界。 - Tim Biegeleisen

Answer 2

您想要筛选出所有至少有两条记录且颜色为“黑色”的顾客。您可以通过条件聚合来实现此功能：

select 
  customer,  
  case when count(distinct color) = 1 then 'yes' else 'no' end as blackOnly,
  case when count(distinct color) > 1 then 'yes' else 'no' end as blackPlusRed
from events 
group by customer
having count(*) > 1
and count(case when color = 'black' then 1 end) > 0;

更新：如果您允许使用其他颜色，则查询略有变化：

select 
  customer,  
  case when count(case when color = 'red' then 1 end) = 0 then 'yes' else 'no' end as blackOnly,
  case when count(case when color = 'red' then 1 end) > 0 then 'yes' else 'no' end as blackPlusRed
from events 
group by customer
having count(case when color = 'black' then 1 end) > 1
or
(
  count(case when color = 'black' then 1 end) > 0
  and 
  count(case when color = 'red' then 1 end) > 0
);

- Thorsten Kettner

---

抱歉，我编辑了我的帖子并添加了更多的颜色。因此，我不能再将条件分解为“您想要所有具有'黑色'和至少两条记录的客户”了。如果仍然有一种不需要子查询的解决方案，我会非常感激。 - timguy

@timguy，如果子查询是必要的话，那也没有什么问题。顺便说一下，你的名字很不错 ^ ^ - Tim Biegeleisen

@timguy：那就直接改变条件吧。我已经更新了我的答案。 - Thorsten Kettner

@TimBiegeleisen: 您是正确的，子查询没有问题，我通常喜欢这种方式来提高可读性。但是就我的情况而言，我使用Java中的SQL更容易适应。确实是个好名字;-)。谢谢 - timguy

我也喜欢使用EXISTS子句，并且一直想知道为什么有那么多人在连接后还要使用DISTINCT来去除生成的重复项 :-) 然而，在这种情况下，你只需要从一个表中获取关于客户的信息，聚合是直接而且通常更快的方式。 - Thorsten Kettner

谢谢您接受我的答案。不过我刚刚注意到，我们可以通过在第一个查询中简单地添加“where color in ('black','red')”来使查询更简单 :-) - Thorsten Kettner