Выбор max() нескольких столбцов
Хорошо, вот моя таблица:
product_id version_id update_id patch_id
1 1 0 0
1 1 1 0
1 1 1 1
1 1 2 0
1 1 2 1
2 1 0 0
2 2 0 0
2 3 0 0
2 3 0 1
3 1 0 0
3 1 0 1
Теперь я хочу выбрать последнюю версию продукта, поэтому версия с наивысшим значением update_id и patch_id.
Например, последняя версия
- продукт 1 должен возвращать 1, 2, 1
- продукт 2 должен возвращать 3, 0, 1
- продукт 3 должен возвращать 1, 0, 1
Я пробовал всевозможные вещи с GROUP BY и HAVING, пытался подзапросы, но я до сих пор не могу понять, как это сделать.
Может кто-нибудь помочь мне найти правильный запрос, или мне следует написать для этого функцию php?
Изменить
Дополнительная информация:
- Столбцы вместе являются первичным ключом (есть больше колонок, но для этой проблемы они не имеют значения)
- Ни один из столбцов не является автоматическим приращением
Это таблица:
CREATE TABLE IF NOT EXISTS `db`.`patch` (
`product_id` INT NOT NULL ,
`version_id` INT NOT NULL ,
`update_id` INT NOT NULL ,
`patch_id` INT NOT NULL
PRIMARY KEY (`product_id`, `version_id`, `update_id`, `patch_id`) ,
INDEX `fk_patch_update1` (`product_id` ASC, `version_id` ASC, `update_id` ASC) )
Изменить 2
Помечено как дубликат, это не так: другой вопрос ищет записи, превышающие значение для любого из трех разных столбцов.
В этом вопросе мы ищем наивысший номер версии, сгруппированный по product_id.
Изменить 3
Ответ rgz снова говорит мне, что это дубликат. Прежде всего: этот вопрос старше. Во-вторых, я не думаю, что ответ тот же.
rgz предлагает использовать следующий запрос:
SELECT product_id, GREATEST(version_id, update_id, patch_id) AS latest_version FROM patch.
БОЛЬШЕ (1,2,3) возвращает 3, правильно? Ват, если у нас есть эти значения:
product_id version_id update_id patch_id
1 1 0 0
1 1 2 8
1 3 0 0
Как я понимаю, этот запрос wil возвращает:
product_id latest_version
1 1
1 8
1 3
Но он должен вернуться:
product_id version_id update_id patch_id
1 3 0 0
Я не думаю, что БОЛЬШОЙ может помочь. Если вы считаете, что это произойдет, пожалуйста, подтвердите, что я ошибаюсь.
Ответы
Ответ 1
Это один из примеров того, когда уникальные идентификаторы приходят полезными.
Представьте, что у вас есть поле autoincrememnting ID, вы можете найти идентификатор, который вы хотите для каждого продукта, используя коррелированный подзапрос...
SELECT
*
FROM
yourTable
WHERE
id = (
SELECT id
FROM yourTable AS lookup
WHERE lookup.product_id = yourTable.product_id
ORDER BY version_id DESC, update_id DESC, patch_id DESC
LIMIT 1
)
Эквивалент без уникального идентификатора требует множественных коррелированных подзапросов...
SELECT
*
FROM
yourTable
WHERE
version_id = (
SELECT MAX(version_id)
FROM yourTable AS lookup
WHERE lookup.product_id = yourTable.product_id
)
AND update_id = (
SELECT MAX(update_id)
FROM yourTable AS lookup
WHERE lookup.product_id = yourTable.product_id
AND lookup.version_id = yourTable.version_id
)
AND patch_id = (
SELECT MAX(patch_id)
FROM yourTable AS lookup
WHERE lookup.product_id = yourTable.product_id
AND lookup.version_id = yourTable.version_id
AND lookup.update_id = yourTable.update_id
)
Это будет значительно медленнее, чем в таблице с уникальным столбцом идентификатора.
Другой альтернативой (без уникального идентификатора) является самосоединение на разных уровнях агрегации.
SELECT
yourTable.*
FROM
(SELECT product_id, MAX(version_id) AS max_version_id FROM yourTable GROUP BY product_id) AS version
INNER JOIN
(SELECT product_id, version_id, MAX(update_id) AS max_update_id FROM yourTable GROUP BY product_id, version_id) AS update
ON update.product_id = version.product_id
AND update.version_id = version.max_version_id
INNER JOIN
(SELECT product_id, version_id, updatE_id, MAX(patch_id) AS max_patch_id FROM yourTable GROUP BY product_id, version_id) AS patch
ON patch.product_id = update.product_id
AND patch.version_id = update.version_id
AND patch.update_id = update.max_update_id
INNER JOIN
yourTable
ON yourTable.product_id = patch.product_id
AND yourTable.version_id = patch.version_id
AND yourTable.update_id = patch.update_id
AND yourTable.patch_id = patch.max_patch_id
Ответ 2
Try:
Select product_id, version_id, update_id, patch_id
from (select * from MyTable
order by product_id, version_id desc, update_id desc, patch_id desc) as v
group by product_id
Извините, это @RolandoMySQLDBA. Простите вторжение, но я отправляю ваш запрос и его вывод по образцу данных:
mysql> Select product_id, version_id, update_id, patch_id
-> from (select * from prod
-> order by product_id, version_id desc, update_id desc, patch_id desc) as v
-> group by product_id ;
+------------+------------+-----------+----------+
| product_id | version_id | update_id | patch_id |
+------------+------------+-----------+----------+
| 1 | 1 | 2 | 1 |
| 2 | 3 | 0 | 1 |
| 3 | 1 | 0 | 1 |
+------------+------------+-----------+----------+
3 rows in set (0.00 sec)
Ваш запрос работает, как показано. Это также проще, чем мой ответ. Хорошее шоу!!!
Ответ 3
Я не тестировал его, но я уверен, что вы можете сделать что-то вроде этого
SELECT val.product_id, MAX(val.myVersion)
FROM (SELECT product_id, ((version_id * 10 + update_id) * 10 + patch_id) as myVersion
FROM myTable) as val
GROUP by product_id;
Извините, это @RolandoMySQLDBA. Простите вторжение, но я отправляю ваш запрос и его вывод по образцу данных:
mysql> SELECT val.product_id, MAX(val.myVersion)
-> FROM (SELECT product_id, ((version_id * 10 + update_id) * 10 + patch_id) as myVersion
-> FROM prod) as val
-> GROUP by product_id;
+------------+--------------------+
| product_id | MAX(val.myVersion) |
+------------+--------------------+
| 1 | 121 |
| 2 | 301 |
| 3 | 101 |
+------------+--------------------+
3 rows in set (0.01 sec)
Хотя отображение отличается, ваш запрос логически работает для исходного вопроса. +1 от меня!!!
Ответ 4
Вы можете группировать max(update_id * maxpatchidvalue + patch_id), чтобы получить то, что вы хотите.
Ответ 5
Попробуйте это.
SELECT
version_id,
max_update.update_id,
max_patch.patch_id
FROM
TABLE
OUTER APPLY
(
SELECT TOP 1
update_id
FROM
TABLE t1
WHERE
TABLE.version_id = t1.version_id
AND TABLE.product_id = t1.product_id
ORDER BY update_id DESC
) max_update
OUTER APPLY
(
SELECT TOP 1
patch_id
FROM
TABLE t1
WHERE
TABLE.version_id = t1.version_id
AND TABLE.product_id = t1.product_id
AND max_update.update_id = t1.update_id
ORDER BY patch_id DESC
) max_patch
Ответ 6
Используя ваши входные данные из вопроса и таблицу без ключей, это довольно больной подход, который работает:
Прежде всего, вам нужен следующий запрос:
select A.* from prod A LEFT JOIN
(select product_id,max(tagnum) maxtag from
(select *,(version_id*10000+update_id*100+patch_id) tagnum from prod) AA
group by product_id) B
USING (product_id)
WHERE (version_id*10000+update_id*100+patch_id) = B.maxtag;
Вот пример кода, использующий все целые числа, индексы и данные вашего образца:
DROP TABLE IF EXISTS prod;
CREATE TABLE prod
(
product_id INT,
version_id INT,
update_id INT,
patch_id INT
) ENGINE=MyISAM;
INSERT INTO prod VALUES
( 1, 1, 0, 0 ),
( 1, 1, 1, 0 ),
( 1, 1, 1, 1 ),
( 1, 1, 2, 0 ),
( 1, 1, 2, 1 ),
( 2, 1, 0, 0 ),
( 2, 2, 0, 0 ),
( 2, 3, 0, 0 ),
( 2, 3, 0, 1 ),
( 3, 1, 0, 0 ),
( 3, 1, 0, 1 );
select * from prod;
select *,(version_id*10000+update_id*100+patch_id) tagnum from prod;
select A.* from prod A LEFT JOIN
(select product_id,max(tagnum) maxtag from
(select *,(version_id*10000+update_id*100+patch_id) tagnum from prod) AA
group by product_id) B
USING (product_id)
WHERE (version_id*10000+update_id*100+patch_id) = B.maxtag;
Вот результат:
mysql> DROP TABLE IF EXISTS prod;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE prod
-> (
-> product_id INT,
-> version_id INT,
-> update_id INT,
-> patch_id INT
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO prod VALUES
-> ( 1, 1, 0, 0 ),
-> ( 1, 1, 1, 0 ),
-> ( 1, 1, 1, 1 ),
-> ( 1, 1, 2, 0 ),
-> ( 1, 1, 2, 1 ),
-> ( 2, 1, 0, 0 ),
-> ( 2, 2, 0, 0 ),
-> ( 2, 3, 0, 0 ),
-> ( 2, 3, 0, 1 ),
-> ( 3, 1, 0, 0 ),
-> ( 3, 1, 0, 1 );
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from prod;
+------------+------------+-----------+----------+
| product_id | version_id | update_id | patch_id |
+------------+------------+-----------+----------+
| 1 | 1 | 0 | 0 |
| 1 | 1 | 1 | 0 |
| 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 0 |
| 1 | 1 | 2 | 1 |
| 2 | 1 | 0 | 0 |
| 2 | 2 | 0 | 0 |
| 2 | 3 | 0 | 0 |
| 2 | 3 | 0 | 1 |
| 3 | 1 | 0 | 0 |
| 3 | 1 | 0 | 1 |
+------------+------------+-----------+----------+
11 rows in set (0.00 sec)
mysql> select *,(version_id*10000+update_id*100+patch_id) tagnum from prod;
+------------+------------+-----------+----------+--------+
| product_id | version_id | update_id | patch_id | tagnum |
+------------+------------+-----------+----------+--------+
| 1 | 1 | 0 | 0 | 10000 |
| 1 | 1 | 1 | 0 | 10100 |
| 1 | 1 | 1 | 1 | 10101 |
| 1 | 1 | 2 | 0 | 10200 |
| 1 | 1 | 2 | 1 | 10201 |
| 2 | 1 | 0 | 0 | 10000 |
| 2 | 2 | 0 | 0 | 20000 |
| 2 | 3 | 0 | 0 | 30000 |
| 2 | 3 | 0 | 1 | 30001 |
| 3 | 1 | 0 | 0 | 10000 |
| 3 | 1 | 0 | 1 | 10001 |
+------------+------------+-----------+----------+--------+
11 rows in set (0.01 sec)
mysql> select A.* from prod A LEFT JOIN
-> (select product_id,max(tagnum) maxtag from
-> (select *,(version_id*10000+update_id*100+patch_id) tagnum from prod) AA
-> group by product_id) B
-> USING (product_id)
-> WHERE (version_id*10000+update_id*100+patch_id) = B.maxtag;
+------------+------------+-----------+----------+
| product_id | version_id | update_id | patch_id |
+------------+------------+-----------+----------+
| 1 | 1 | 2 | 1 |
| 2 | 3 | 0 | 1 |
| 3 | 1 | 0 | 1 |
+------------+------------+-----------+----------+
3 rows in set (0.00 sec)
mysql>
Дайте ему попробовать!!!
РИСКОВАННАЯ
Мой ответ будет поддерживать каждый продукт
- до 99 версий
- до 99 обновлений
- до 99 патчей
Ответ 7
MySQL имеет функцию GREATEST, поэтому вы должны использовать ее следующим образом:
SELECT product_id, GREATEST(version_id, update_id, patch_id) AS latest_version FROM patch.
Я обозначил этот вопрос как дубликат, потому что оба вопроса в основном задают одно и то же, выбирая максимальное значение нескольких столбцов. Здесь вы используете его в предложении SELECT, в другом вопросе, который вы используете в предложении WHERE. В любом случае GREATEST делает трюк.
