Исключить самую последнюю дату при группировке по атрибуту через JOIN
Я хочу исключить самую последнюю дату из результатов моего запроса SQL. Я провел небольшое исследование по этому вопросу и могу найти только примеры, в которых они GROUP BY являются атрибутами в исходной таблице. Я GROUP BY атрибут в объединенной таблице.
Это пара решений, которые я пробовал, но мне трудно адаптировать их решение к моему запросу из-за моего JOIN.
Вот где я сейчас нахожусь (псевдозапрос ниже). Я предполагал, что могу просто изменить IN на NOT IN и тем самым исключить дату MAX, но это не кажется правильной логикой:
SELECT t2.sid
,t1.date
FROM dbo.table1 t1
LEFT JOIN dbo.table2 t2 ON t1.id = t2.id
WHERE t1.num = 000
AND t2.sid IS NOT NULL
AND t2.sid NOT LIKE '%#########%'
AND t1.date NOT IN (
SELECT MAX(t1a.date)
FROM dbo.table1 t1a
WHERE t1a.id = t1.id
)
GROUP BY t2.sid,t1.date
Любое понимание очень ценится. Пожалуйста, дайте мне знать, если я могу предоставить дополнительную информацию. Спасибо!
Думаю, как альтернатива DISTINCT
Я не знаю, можно ли использовать NOT IN в таком коррелированном подзапросе. Может быть? Для иллюстрации: NOT EXISTS — это то, что вы видите чаще всего. бывший. WHERE NOT EXISTS (SELECT 1 FROM dbo.table1 t1a WHERE t1.id = t1a.id GROUP BY t1a.id HAVING t1.date = max(t2.date)) Хотя на практике я бы использовал RANK().
NOT IN можно использовать для подзапроса @Error_2646 . Хотя IN в этом примере немного бессмысленен, так как подзапрос вернет набор данных из одного столбца и строки, поэтому с таким же успехом можно использовать !=.
@Error_2646: разрешено иметь коррелированный подзапрос с [NOT] IN, но считается хорошим стилем использовать коррелированные подзапросы с [NOT] IEXISTS и некоррелированные подзапросы с [NOT] IN.
@ThomA, ты прав. Если не использовать GROUP BY, результат будет тот же. Однако я хотел бы включить date в свои результаты. Единственный способ, которым я могу это сделать, — это включить MAX в мое SELECT заявление. Но, конечно, я хочу исключить MAX. Так что, если есть способ сделать это без использования GROUP BY, то это здорово. Мне просто нужны даты, сгруппированные по t2.sid
Разве вы не можете просто использовать Получить 1 верхнюю строку каждой группы? Сменить = на != не составляет большого труда.
Я не слишком знаком с WITH. Когда я трансформирую это решение в соответствии со своими целями, я получаю сообщение об ошибке: «Столбец «id» был указан несколько раз из «cte». Может быть, вы знаете, что это значит? Я поставил JOIN в том месте, где обозначен PARTITION.
— Может, ты знаешь, что это значит? Буквально это означает то, что написано: у вас есть более двух столбцов, которые вы назвали id в своем CTE.
Ответы 1
Я считаю, что для этого вы можете использовать оконную функцию RANK(), тогда вы, вероятно, сможете удалить предложение GROUP BY/DISTINCT.
SELECT sid, date FROM (
SELECT t2.sid
, t1.date
, RANK() OVER(ORDER BY t1.date DESC) AS row_num
FROM dbo.table1 t1
LEFT JOIN dbo.table2 t2 ON t1.id = t2.id
WHERE t1.num = 000
AND t2.sid NOT LIKE '%#########%'
) as date_ranked_by_id
WHERE date_ranked_by_id.row_num != 1
Я использовал это и получаю некоторые ошибки в SELECT sid, date FROM и дополнительно в WHERE row_num != 1. Я получаю довольно двусмысленную ошибку: «Неправильный синтаксис рядом с ключевым словом «ГДЕ». хотя у меня красный цвет начинается с sid и date.
@Crimp Попробуйте использовать псевдоним для подзапроса, так что ) as date_ranked_by_id WHERE date_ranked_by_id.row_num != 1
Кажется, это удалось! Спасибо @Error_2646
@Crimp Возможно, ты захочешь ROW_NUMBER не RANK
Ну, оказывается, у меня проблемы RANK и ROW_NUMBER. Когда я первоначально тестировал это решение, я дополнительно квалифицировался t2.sid по конкретному id. При такой квалификации будет исключена самая последняя дата. Но когда я убираю эту квалификацию (беру ВСЕ sid), включается самая последняя дата. Понимание того, почему?
Вам нужно удалить самую последнюю дату для каждого sid? Допустим, у sid = 1 самая последняя дата — 1 июля 2024 г., а у sid = 2 — 02 июля 2024 г.; в этом случае вы хотите удалить обе даты?
Ты прав!
Тогда это легко :) Просто добавьте пункт PARTITION BY в РАНГ. RANK() OVER(PARTITION BY t2.sid ORDER BY t1.date DESC) AS row_num
Ты мужчина! Ценю вашу помощь!
Зачем ты вообще используешь
GROUP BY? Здесь нет никакой агрегации.