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

小柳说说

我一直在寻找答案，但是找不到完全根据条件获取此不同记录集的方法。我有一个包含以下示例数据的表：
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个位置，因此未出现。谢谢！

解决方案:


            使用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
这是一个示范

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

小柳说说 LV1