SQL Group вопросом - выбор, какая строка сгруппирована
У меня есть таблица, которая отслеживает порядковые номера и количество лицензий, связанных с упомянутым порядковым номером. Клиент может повторно лицензировать поле, увеличиваясь или сокращая число пользователей, и они только тарифицированы за измененную сумму.
Структура следующей таблицы существует:
id - Auto Incrementing ID
serial - The serial number
numusers - the number of licenses
date - The date that the request was submitted, with the highest date being the number of users currently licensed
У меня есть следующий запрос для выбора лицензий, которые были обновлены, и он работает, если лицензия была только повторно отправлена однажды. Если это было повторно отправлено несколько раз, это возвращает ссылки на предыдущее обновление и все обновления перед этим.
SELECT p.id as id, c.id as oldid, p.numusers-c.numusers AS dif, p.date, c.date
FROM `licenses` AS p
JOIN `licenses` AS c ON p.serial = c.serial
AND p.date > c.date
AND p.id <> c.id
WHERE p.id = 156
#GROUP BY p.id
Вот набор данных:
id serial numusers date
26 1234 500 2010-07-14
34 1234 600 2010-07-15
156 1234 500 2010-07-21
Когда я выполняю запрос, я получаю следующее:
id oldid dif date date
156 26 0 2010-07-21 2010-07-14
156 34 -100 2010-07-21 2010-07-15
Если я некомментирую пункт GROUP BY в запросе, я получаю строку с oldid 26. Как я только выбрал бы строку с новой датой (строка с oldid 34)? Я мог использовать ORDER BY и ОГРАНИЧИТЬ 1, но я также хочу смочь выбрать из целой таблицы без оператора Where.
Я использую MySQL 5.1.
2 ответа
Возможно, вам нужно следующее:
select p.id, c.id as priorid, p.numusers-c.numusers AS dif, p.date, c.date as priordate
from licenses p
join licenses c on c.serial=p.serial
and c.date=(select max(date) from licenses ref where ref.serial=p.serial
and ref.date<p.date)
order by p.serial
Я всегда считал довольно раздражающим ограничением SQL, что для того, чтобы сказать "найдите мне поле X из записи с максимальным значением для поля Y", мне нужно один раз прочитать таблицу, чтобы найти максимальное значение Y с помощью встроенного запроса, а затем прочитать ее снова, чтобы найти запись с этим значением и получить другие значения, которые мне нужны.
Если есть три записи для одной и той же серии, то вышеописанное должно дать две строки "разницы" на выходе. Я думаю, это то, что вы хотите получить. Если есть только одна запись для данного Серийного номера, то вышеприведенное не даст никакого вывода для этого Серийного номера, что может быть тем, что вы хотите, а может и нет.
Можно ли использовать производную таблицу; что-то вроде ...
SELECT *
FROM (SELECT p.id as id,
c.id as oldid,
p.numusers-c.numusers AS dif,
p.date,
c.date
FROM `licenses` AS p JOIN `licenses` AS c ON p.serial = c.serial
AND p.date > c.date
AND p.id <> c.id
ORDER BY oldid DESC) AS inner_table
GROUP BY id;