SELECT DISTINCT HAVING 计算唯一条件

我已经搜索过这个问题的答案，但找不到如何根据条件获取这个不同的记录集。我有一个包含以下示例数据的表：

Type    Color   Location    Supplier
----    -----   --------    --------
Apple   Green   New York    ABC
Apple   Green   New York    XYZ
Apple   Green   Los Angeles ABC
Apple   Red     Chicago     ABC
Apple   Red     Chicago     XYZ
Apple   Red     Chicago     DEF
Banana  Yellow  Miami       ABC
Banana  Yellow  Miami       DEF
Banana  Yellow  Miami       XYZ
Banana  Yellow  Atlanta     ABC

我想创建一个查询，显示每个不同类型+颜色的唯一位置的数量，其中唯一位置的数量大于 1，例如

Type    Color   UniqueLocations
----    -----   --------
Apple   Green   2
Banana  Yellow  2

请注意，{Apple，Red，1} 不会出现，因为红苹果只有 1 个位置（芝加哥）。我想我已经有了这个（但也许有一种更简单的方法）。我在用着：

SELECT Type, Color, Count(Location) FROM
(SELECT DISTINCT Type, Color, Location FROM MyTable)
GROUP BY Type, Color HAVING Count(Location)>1;

如何创建另一个列出的查询Type, Color, and Location对于每个不同的Type,Color当那个的唯一位置的数量Type,Color大于1？生成的记录集将如下所示：

Type    Color   Location
----    -----   --------
Apple   Green   New York
Apple   Green   Los Angeles
Banana  Yellow  Miami
Banana  Yellow  Atlanta

注意Apple, Red, Chicago没有出现，因为红苹果只有 1 个位置。谢谢！

Use a COUNT(DISTINCT Location)并针对子查询加入Type and Color The GROUP BY and HAVING您尝试使用的子句将完成这项工作。

/* Be sure to use DISTINCT in the outer query to de-dup */
SELECT DISTINCT
   MyTable.Type,
   MyTable.Color,
   Location
FROM 
  MyTable
  INNER JOIN (
    /* Joined subquery returns type,color pairs having COUNT(DISTINCT Location) > 1 */
    SELECT
      Type,
      Color,
      /* Don't actually need to select this value - it could just be in the HAVING */
      COUNT(DISTINCT Location) AS UniqueLocations
    FROM
      MyTable
    GROUP BY Type, Color
    /* Note: Some RDBMS won't allow the alias here and you 
       would have to use the expanded form
       HAVING COUNT(DISTINCT Location) > 1
     */
    HAVING UniqueLocations > 1
  /* JOIN back against the main table on Type, Color */
  ) subq ON MyTable.Type = subq.Type AND MyTable.Color = subq.Color

这是一个演示 http://sqlfiddle.com/#!2/0618f/4