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 и 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 место. Спасибо!

Ответы

Ответ 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

Вот демонстрация

Ответ 2

Вы можете написать свой первый запрос следующим образом:

Select Type, Color, Count(Distinct Location) As UniqueLocations
From Table
Group By Type, Color
Having Count(Distinct Location) > 1

(если вы используете MySQL, вы можете использовать псевдоним UniqueLocations в своем предложении having, но во многих других системах псевдонимы еще не доступны, поскольку предложение having оценивается до предложения select, в этом случае вам нужно повторить подсчет по обоим статьям).

И для второго есть много разных способов написать это, это может быть одно:

Select Distinct Type, Color, Location
From Table
Where
  Exists (
    Select
      *
    From
      Table Table_1
    Where
      Table_1.Type = Table.Type
      and Table_1.Color = Table.Color
    Group By
      Type, Color
    Having
      Count(Distinct Location) > 1
  )