选择不同的拥有点数独特条件

Question

我一直在寻找答案，但是找不到完全根据条件获取此不同记录集的方法。我有一个包含以下示例数据的表：

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的唯一位置的计数，例如

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

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

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

当唯一查询的唯一位置计数大于1时Type, Color，如何创建另一个查询，该查询列出，并且Location针对每个不同查询？结果记录集如下所示：Type,Color``Type,Color

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

请注意，Apple, Red, Chicago由于红苹果只有1个位置，因此未出现。谢谢！

Answer 1

使用COUNT(DISTINCT Location)并加入针对一个子查询上Type，并Color 在GROUP BY和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

这是一个示范