Мне нужно получить все строки из таблицы, в которой объединены два столбца. Поэтому я хочу, чтобы все продажи не имели других продаж, которые произошли в тот же день по той же цене. Продажи, которые уникальны в зависимости от дня и цены, будут обновлены до активного состояния.
Итак, я думаю:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
Но мой мозг больно идет дальше, чем это.
SELECT DISTINCT a,b,c FROM t
примерно эквивалентен:
SELECT a,b,c FROM t GROUP BY a,b,c
Это хорошая идея, чтобы привыкнуть к синтаксису GROUP BY, поскольку он более мощный.
По вашему запросу я бы сделал это следующим образом:
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
)
Если вы соберете ответы, очистите и улучшите их, вы получите этот превосходный запрос:
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
Который намного быстрее, чем любой из них. Снижает производительность принятого в настоящее время ответа в 10-15 раз (в моих тестах на PostgreSQL 8.4 и 9.1).
Но это все еще далеко от оптимального. Используйте полусоединение NOT EXISTS
(anti-) для еще лучшей производительности. EXISTS
- это стандартный SQL, существующий вечно (по крайней мере, с PostgreSQL 7.2, задолго до того, как был задан этот вопрос) и идеально соответствует представленным требованиям:
UPDATE sales s
SET status = 'ACTIVE'
WHERE NOT EXISTS (
SELECT FROM sales s1 -- SELECT list can be empty for EXISTS
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
Если у вас нет первичного или уникального ключа для таблицы (id
в примере), вы можете заменить системный столбец ctid
для целей этого запроса (но не для некоторых других целей):
AND s1.ctid <> s.ctid
Каждая таблица должна иметь первичный ключ.Добавьте еще один, если у вас его еще не было.Я предлагаю serial
или столбец IDENTITY
в Postgres 10+.
Связанные с:
Подзапрос в полусоединении EXISTS
anti- может прекратить оценку, как только будет найден первый дублик (нет смысла смотреть дальше). Для базовой таблицы с небольшим количеством дубликатов это лишь немного более эффективно. С большим количеством дубликатов это становится намного более эффективным.
Если некоторые или несколько строк уже имеют status = 'ACTIVE'
, ваше обновление ничего не изменит, но все равно вставит новую версию строки за полную стоимость (применяются незначительные исключения). Обычно вы этого не хотите. Добавьте еще одно условие WHERE
как показано выше, чтобы сделать это еще быстрее:
Если status
определен как NOT NULL
, вы можете упростить:
AND status <> 'ACTIVE';
Этот запрос (в отличие от принятого в настоящее время ответа Джоэла) не рассматривает значения NULL как равные. Эти две строки для (saleprice, saledate)
будут квалифицироваться как "отличные" (хотя и выглядят идентично человеческому глазу):
(123, NULL)
(123, NULL)
Также передает уникальный индекс и почти где-либо еще, поскольку значения NULL не сравниваются равными в соответствии со стандартом SQL. Увидеть:
OTOH, GROUP BY
или DISTINCT
или DISTINCT ON()
обрабатывают значения NULL как равные. Используйте соответствующий стиль запроса в зависимости от того, чего вы хотите достичь. Вы все еще можете использовать этот более быстрый стиль запроса, используя IS NOT DISTINCT FROM
вместо =
для любого или всех сравнений, чтобы сделать сравнение NULL равным. Больше:
Если все сравниваемые столбцы определены как NOT NULL
, нет места для разногласий.
Проблема с вашим запросом заключается в том, что при использовании предложения GROUP BY (которое вы по существу используете с помощью отдельных) вы можете использовать только столбцы, которые вы группируете или объединяете функции. Вы не можете использовать идентификатор столбца, поскольку существуют потенциально разные значения. В вашем случае всегда существует только одно значение из-за предложения 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, важно использовать только функцию, возвращающую значение столбца, если есть только одна соответствующая строка.
Я хочу выбрать отдельные значения из одного столбца "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)
Multi select в целом можно выполнить безопасно следующим образом:
select distinct * from (select col1, col2 from table ) as x
Как это может работать на большинстве СУБД