Как мне (или я могу) выбрать DISTINCT на нескольких столбцах?

Мне нужно получить все строки из таблицы, где 2 столбца объединены все разные. Поэтому я хочу, чтобы все продажи, которые не имеют никаких других продаж, которые произошли в тот же день по той же цене. Продажи, которые уникальны в зависимости от дня и цены, будут обновлены до активного статуса.

поэтому я думаю:

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)

но мой мозг болит идти дальше, чем это.

4 339
sql postgresql distinct duplicates sql-update

4 ответа:

SELECT DISTINCT a,b,c FROM t

- это примерно эквивалентно: 

SELECT a,b,c FROM t GROUP BY a,b,c

это хорошая идея, чтобы привыкнуть к группе по синтаксису, так как это более мощный.

для вашего запроса, я бы сделал это так:

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
 )
379

если вы соберете ответы до сих пор, очистите и улучшите, вы придете к этому превосходному запросу:

UPDATE sales
SET    status = 'ACTIVE'
WHERE  (saleprice, saledate) IN (
    SELECT saleprice, saledate
    FROM   sales
    GROUP  BY saleprice, saledate
    HAVING count(*) = 1 
    );

что это много быстрее, чем любой из них. Nukes производительность принятого в настоящее время ответа по фактору 10 - 15 (в моих тестах на PostgreSQL 8.4 и 9.1).

но это все еще далеко от оптимального. Используйте NOT EXISTS (анти-)полу-соединение для еще лучшей производительности. EXISTS стандартный SQL, имеет был вокруг навсегда (по крайней мере, с PostgreSQL 7.2, задолго до того, как этот вопрос был задан) и идеально соответствует представленным требованиям:

UPDATE sales s
SET    status = 'ACTIVE'
WHERE  NOT EXISTS (
   SELECT 1
   FROM   sales s1
   WHERE  s.saleprice = s1.saleprice
   AND    s.saledate  = s1.saledate
   AND    s.id <> s1.id                     -- except for row itself
   );
AND    s.status IS DISTINCT FROM 'ACTIVE';  -- avoid empty updates. see below

Скрипка SQL.

уникальный ключ для идентификации строки

если у вас нет первичного или уникального ключа для таблицы (id в примере), вы можете заменить столбец системы ctid для целей этого запроса (но не для некоторых других цели):

   AND    s1.ctid <> s.ctid

каждая таблица должна иметь первичный ключ. Добавьте один, если у вас его еще не было. Я предлагаю serial или IDENTITY колонка в Postgres 10+.

по теме:

как это быстрее?

подзапрос в EXISTS (анти-)полу-соединение может перестать оцениваться как как только первый обман будет найден (нет смысла искать дальше). Для базовой таблицы с несколькими дубликатами это только слегка более эффективно. С большим количеством дубликатов это становится путь более эффективным.

исключить пустые обновления

если некоторые или многие строки уже есть status = 'ACTIVE' обновление ничего не изменит, но все же вставить новую строку Версия по полной стоимости (незначительные исключения). Как правило, вы этого не хотите. Добавьте еще WHERE состояние как показано выше, чтобы сделать это еще быстрее:

если status определен NOT NULL, вы можете упростить:

AND status <> 'ACTIVE';

тонкая разница в обработке NULL

этот запрос (в отличие от в настоящее время принят ответ Джоэла) не рассматривает нулевые значения как равные. Эти две строки (saleprice, saledate) будет квалифицироваться как "отличный" (хотя и выглядит идентично человеческому глазу):

(123, NULL)
(123, NULL)

также проходит в уникальный индекс и почти в любом месте иначе, поскольку значения NULL не сравниваются равными в соответствии со стандартом SQL. Смотрите:

ото GROUP BY или DISTINCT или DISTINCT ON () рассматривать нулевые значения как равные. Используйте соответствующий стиль запроса в зависимости от того, что вы хотите достичь. Вы все еще можете использовать этот более быстрый стиль запроса с помощью IS NOT DISTINCT FROM вместо = для любого или всех сравнений, чтобы сделать нулевое сравнение равным. Еще:

если все сравниваемые столбцы определяются NOT NULL нет места для разногласий.

295

проблема с вашим запросом заключается в том, что при использовании предложения GROUP BY (которое вы по существу делаете с помощью distinct) вы можете использовать только столбцы, которые вы группируете по или агрегатные функции. Вы не можете использовать идентификатор столбца, потому что есть потенциально разные значения. В вашем случае всегда есть только одно значение из-за предложения HAVING, но большинство СУБД недостаточно умны, чтобы распознать это.

Это должно работать, однако (и не требует соединения):

UPDATE sales
SET status='ACTIVE'
WHERE id IN (
  SELECT MIN(id) FROM sales
  GROUP BY saleprice, saledate
  HAVING COUNT(id) = 1
)

вы могли также используйте MAX или AVG вместо MIN, важно использовать только функцию, которая возвращает значение столбца, если есть только одна соответствующая строка.

22

Я хочу выбрать различные значения из одного столбца "GrondOfLucht", но они должны быть отсортированы в порядке, указанном в столбце "сортировка". Я не могу получить различные значения только одного столбца с помощью

Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering

Он также даст столбец "сортировка", и поскольку "GrondOfLucht" и "сортировка" не уникальны, результатом будут все строки.

используйте группу для выбора записей ' GrondOfLucht 'в порядке, указанном' сортировка

SELECT        GrondOfLucht
FROM            dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)
1