SQL - запрос на удаление по условию

← →
Ольга (2005-07-06 16:46) [0]

Что-то меня сегодня клинит, не могу правильно написать простой запрос на удаление. Прошу помощи.
Есть таблица T1: (ID int,
Р int,
S int,
VAL float)
Сочетание ID,P,S - уникальное.
Нужно в таблице оставить записи, где значение S максимальное для каждого сочетания ID,P, но не больше заданного значения (допустим 4).

Вот мой неработающий вариант (не ругается, но и не удаляет ненужное из таблицы):
DELETE FROM T1 WHERE NOT EXISTS
(SELECT d.ID,d.P,d.S,d.VAL
FROM T1 d,
(select ID,P,max(S) as S
from T1
where S<=4
group by ID,P) d1
WHERE d.ID=d1.ID AND d.P=d1.P AND d.S=d1.S)

← →
Virgo_Style © (2005-07-06 16:59) [1]

Может, так?

DELETE * FROM T1 AS A WHERE ( A.S<( SELECT MAX(S) FROM T1 AS B WHERE (A.ID=B.ID) and (A.P=B.P) ) ) OR (A.S>4)

← →
Johnmen © (2005-07-06 17:00) [2]

DELETE FROM Table T1 WHERE (T1.S< (SELECT MAX(T2.S) FROM Table T2 WHERE T2.ID=T1.ID AND T2.P=T1.P)) AND T1.S<=4

← →
Johnmen © (2005-07-06 17:01) [3]

конечно же T1.S>4

← →
Ольга (2005-07-06 17:12) [4]

Нет, так не получится.
1. Нельзя использовать псевдоним в конструкции DELETE FROM Table Т1
2. SELECT MAX(T2.S) выберет 1 значение, а мне нужно для каждого сочетания ID,P

← →
Johnmen © (2005-07-06 17:17) [5]

1. Ну да. Указывать явно. Но тут может быть засада. Всё зависит от принципов работы парсера, а они мне для MSSQL неизвестны.
Для парсера IB будет засада, для MySQL - нет.
2. Так выберет же для текущего сочетания ID,P.

← →
Ольга (2005-07-06 17:28) [6]

Явно указала таблицу - теперь претензий к синтаксису у сервера нет, но данные удаляются неправильно - только те, где S>4 (т.к. это max S из всех сочетаний ID,P)

← →
Johnmen © (2005-07-06 17:33) [7]

>но данные удаляются неправильно - только те, где S>4

Ну да, что не так ?
М.б. более чётко сфjрмулировать
>Нужно в таблице оставить записи, где значение S максимальное
>для каждого сочетания ID,P, но не больше заданного значения
>(допустим 4).
а то можно и по-другому сынтерпретировать...:)

← →
Seg (2005-07-06 17:34) [8]

Наверное забыли
group by ID,P

← →
Johnmen © (2005-07-06 17:39) [9]

>Seg (06.07.05 17:34) [8]
>Наверное забыли
>group by ID,P

Не забыли. Оно (она?) здесь ни к чему.

← →
Ольга (2005-07-06 17:41) [10]

Поле S - это порядковый номер корректировки поля VAL для ID и P.
Нужно в таблице оставить только последнюю корректировку (для каких-то сочетаний ID и P это будет 1, для других 5).
Так вот, удаляются только те, для которых S>4, т.к.
SELECT MAX(T2.S)
FROM Table T2
WHERE T2.ID=T1.ID AND T2.P=T1.P
выдает 1 запись, естественно.

← →
Ольга (2005-07-06 17:46) [11]

Запрос:
SELECT d.ID,d.P,d.S,d.VAL
FROM T1 d,
(select ID,P,max(S) as S
from T1
where S<=4
group by ID,P) d1
WHERE d.ID=d1.ID AND d.P=d1.P AND d.S=d1.S
выдает то, что надо оставить в таблице.
Как бы прикрутить к этому DELETE?

← →
Johnmen © (2005-07-06 17:49) [12]

т.к.
SELECT MAX(T2.S)
FROM Table T2
WHERE T2.ID=T1.ID AND T2.P=T1.P
выдает 1 запись, естественно.

Естественно, что одну.
Другой вопрос, на каждую ли запись таблицы ?
Если нет, то это проблемы парсера, про него я уже упоминал.
Кстати, просто интересно, если это действительно так, то что возвращает этот запрос в отношении таблицы?

← →
Ольга (2005-07-06 17:53) [13]

Что-то я не врубаюсь.
Агрегатные функции в конструкциях типа
SELECT AgrFun(X) FROM ...
всегда выдают 1 значение - это SQL стандарт

← →
Johnmen © (2005-07-06 17:54) [14]

А кто спорит ? Никто.
Всё дело СКОЛЬКО РАЗ будет выполнен вложенный запрос.

← →
Ольга (2005-07-06 17:56) [15]

> что возвращает этот запрос в отношении таблицы

4 - максимальное значение S из всех сочетаний ID,P

← →
Johnmen © (2005-07-06 17:57) [16]

Т.е. условие WHERE ему как бы побарабану ?

← →
Ольга (2005-07-06 17:58) [17]

> СКОЛЬКО РАЗ будет выполнен вложенный запрос

Один. Сначала выполнится внутренний запрос, а потом внешний:
DELETE FROM Table T1
WHERE T1.S< 4 AND T1.S<=4

← →
Johnmen © (2005-07-06 18:00) [18]

>> СКОЛЬКО РАЗ будет выполнен вложенный запрос
>Один. Сначала выполнится внутренний запрос, а потом внешний:

Если ты в этом уверена, то см.[5]

← →
Johnmen © (2005-07-06 18:02) [19]

PS
Я бы сам проверил, да только сервер MSSQL в перманентном дауне, да и домой пора...:)

← →
Ольга (2005-07-06 18:04) [20]

> да и домой пора

Пожалуй. Утро вечера мудренее.
Спасибо.

← →
ЮЮ © (2005-07-07 05:01) [21]

Запрос:
SELECT d.ID,d.P,d.S,d.VAL
FROM T1 d,
(select ID,P,max(S) as S
from T1
where S<=4
group by ID,P) d1
WHERE d.ID=d1.ID AND d.P=d1.P AND d.S=d1.S
выдает то, что надо оставить в таблице.
Как бы прикрутить к этому DELETE?

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

DELETE T
FROM
(
SELECT ID, P, max(S) S
FROM T
WHERE S<=4
это условие лишнее, т.к. если есть запись с S = 4, 5 и 6
то c S = 4 останется, а с большими - удалятся !!!
GROUP BY ID, P
) Rest
LEFT JOIN T ON (T.Id = Rest.Id) AND (T.P = Rest.P) AND (T.S = Rest.S)
WHERE
Rest.Id IS NULL

← →
Ольга (2005-07-07 08:00) [22]

ЮЮ ©
Ваш скрипт работает неправильно. Rest.Id никогда не будет IS NULL. Может я невнятно изложила, что мне надо. Давайте на примере. В таблице есть данные:

ID P S Val

10 1 0 5.2
10 1 1 5.3
10 1 2 5.31
10 2 0 8
10 2 5 7.8
11 1 0 12
11 1 3 12.6
11 2 0 18

После удаления должны остаться "жирные" записи.

Почему
10 2 0 8
а не
10 2 5 7.8 ?

>Rest.Id никогда не будет IS NULL

Sorry, там надо RIGHT JOIN
сначала я написал
T LEFT JOIN () Rest,
но потом подумал, что тогда не будет работать индекс по ID,P,S
и поменял местами, не исправмл LEFT на RIGHT

ЮЮ © (07.07.05 8:28) [23]
Почему

Наверное, потому что

> где значение S максимальное для каждого сочетания
> ID,P, но не больше заданного значения

← →
Ольга (2005-07-07 08:35) [25]

> Почему
> 10 2 0 8
> а не
> 10 2 5 7.8 ?

А это как раз должно сработать дополнительное условие: S<=4

Выполняю скрипт:
DELETE T1
FROM
( SELECT ID, P, max(S) S
FROM T1
WHERE S<=4
GROUP BY ID, P
) Rest
RIGHT OUTER JOIN T1 ON (T1.Id = Rest.Id) AND (T1.P = Rest.P) AND (T1.S = Rest.S)
WHERE T1.Id IS NULL

Результат:
(0 row(s) affected)

а select *
from <ДАЛЕЕ ПО ТЕКСТУ> ?

А КАК ЖЕ
Ольга (06.07.05 17:41) [10]
Поле S - это порядковый номер корректировки поля VAL для ID и P.
Нужно в таблице оставить только последнюю корректировку (для каких-то сочетаний ID и P это будет 1, для других 5).

DELETE T1
FROM
( SELECT ID, P, max(S) S
FROM T1
WHERE S<=4
GROUP BY ID, P
) Rest
RIGHT OUTER JOIN T1 ON (T1.Id = Rest.Id) AND (T1.P = Rest.P) AND (T1.S = Rest.S)
WHERE T1.Id IS NULL

В предложенном (и удаляющем) вырианте было

Rest.Id IS NULL

А спасибо где? Что я зря таблицу создавал и данные вколачмвал, чтобы убедиться, что на практике мои теоретические познания подтаерждаются? :)

← →
Ольга (2005-07-07 09:35) [30]

Я здесь отвлеклась на производственные проблемы...
Да, конечно, Rest.Id IS NULL. Это я пока меняла лево на право... в общем сено-солома получилось, sorry.
Все заработало!
СПАСИБО!
БОЛЬШОЕ СПАСИБО!
ОГРОМНОЕ СПАСИБО! (и "КУ" с приседанием 3 раза).

> и "КУ" с приседанием 3 раза

А желтые штаны дарствовать?

SQL - запрос на удаление по условию Найти похожие ветки