sql — ROW_NUMBER () в MySQL
Есть ли хороший способ в MySQL для репликации функции SQL Server ROW_NUMBER()?
Например:
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
Тогда я мог бы, например, добавить условие для ограничения intRow до 1, чтобы получить один ряд с самым высоким col3 для каждого (col1, col2) пара.
Решение
Я хочу строку с одним самым высоким col3 для каждой пары (col1, col2).
Это групповой максимум, один из наиболее часто задаваемых вопросов SQL (так как кажется, что это должно быть легко, но на самом деле это не так).
Я часто пухленький для нулевого самостоятельного соединения:
SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;
«Получите строки в таблице, для которых ни одна другая строка с совпадающими столбцами col1, col2 не имеет более высокое значение col3». (Вы заметите это, и большинство других решений с групповым максимумом будут возвращать несколько строк, если более одной строки имеют одинаковые col1, col2 , col3. Если это проблема, вам может потребоваться некоторая постобработка.)
Другие решения
В MySQL нет функции ранжирования. Самое близкое, что вы можете получить, это использовать переменную:
SELECT t.*,
@rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t,
(SELECT @rownum := 0) r
так как это будет работать в моем случае? Мне нужно две переменные, по одной для каждого из col1 и col2? Col2 нужно будет как-то переустанавливать, когда col1 меняется ..?
Да. Если бы это был Oracle, вы могли бы использовать функцию LEAD для достижения следующего значения. К счастью, Quassnoi покрывает логика для того, что вам нужно реализовать в MySQL.
Я всегда в конечном итоге следую этой схеме. Учитывая эту таблицу:
+------+------+
| i | j |
+------+------+
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 2 | 21 |
| 2 | 22 |
| 2 | 23 |
| 3 | 31 |
| 3 | 32 |
| 3 | 33 |
| 4 | 14 |
+------+------+
Вы можете получить этот результат:
+------+------+------------+
| i | j | row_number |
+------+------+------------+
| 1 | 11 | 1 |
| 1 | 12 | 2 |
| 1 | 13 | 3 |
| 2 | 21 | 1 |
| 2 | 22 | 2 |
| 2 | 23 | 3 |
| 3 | 31 | 1 |
| 3 | 32 | 2 |
| 3 | 33 | 3 |
| 4 | 14 | 1 |
+------+------+------------+
Запустив этот запрос, для которого не нужно определять переменную:
SELECT a.i, a.j, count(*) as row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j
Надеюсь, это поможет!
SELECT
@i:=@i+1 AS iterator,
t.*
FROM
tablename AS t,
(SELECT @i:=0) AS foo
Ознакомьтесь с этой статьей, в ней показано, как имитировать SQL ROW_NUMBER () с разделом в MySQL. Я столкнулся с тем же сценарием в реализации WordPress. Мне нужно было ROW_NUMBER (), и его там не было.
http://www.explodybits.com/2011/11/mysql-row-number/
Пример в статье использует один раздел по полю. Для разделения на дополнительные поля вы можете сделать что-то вроде этого:
SELECT @row_num := IF(@prev_value=concat_ws('',t.col1,t.col2),@row_num+1,1) AS RowNumber
,t.col1
,t.col2
,t.Col3
,t.col4
,@prev_value := concat_ws('',t.col1,t.col2)
FROM table1 t,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY t.col1,t.col2,t.col3,t.col4
Использование concat_ws обрабатывает нуль. Я проверил это на 3 полях, используя int, date и varchar. Надеюсь это поможет. Проверьте статью, поскольку это разбивает этот запрос и объясняет это.
От MySQL 8.0.0 и выше вы можете использовать оконные функции.
Оконные функции.
MySQL теперь поддерживает оконные функции, которые для каждой строки из запроса выполняют вычисления, используя строки, связанные с этой строкой. К ним относятся такие функции, как RANK (), LAG () и NTILE (). Кроме того, несколько существующих агрегатных функций теперь можно использовать в качестве оконных функций; например, SUM () и AVG ().
Возвращает номер текущей строки в ее разделе. Номера строк варьируются от 1 до количества строк раздела.
ORDER BY влияет на порядок, в котором строки нумеруются. Без ORDER BY нумерация строк является неопределенной.
Демо-версия:
CREATE TABLE Table1(
id INT AUTO_INCREMENT PRIMARY KEY, col1 INT,col2 INT, col3 TEXT);
INSERT INTO Table1(col1, col2, col3)
VALUES (1,1,'a'),(1,1,'b'),(1,1,'c'),
(2,1,'x'),(2,1,'y'),(2,2,'z');
SELECT
col1, col2,col3,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1;
Я также проголосовал бы за решение Мости Мостачо с незначительными изменениями в его коде запроса:
SELECT a.i, a.j, (
SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a
Который даст тот же результат:
+------+------+------------+
| i | j | row_number |
+------+------+------------+
| 1 | 11 | 1 |
| 1 | 12 | 2 |
| 1 | 13 | 3 |
| 2 | 21 | 1 |
| 2 | 22 | 2 |
| 2 | 23 | 3 |
| 3 | 31 | 1 |
| 3 | 32 | 2 |
| 3 | 33 | 3 |
| 4 | 14 | 1 |
+------+------+------------+
для стола:
+------+------+
| i | j |
+------+------+
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 2 | 21 |
| 2 | 22 |
| 2 | 23 |
| 3 | 31 |
| 3 | 32 |
| 3 | 33 |
| 4 | 14 |
+------+------+
С той лишь разницей, что запрос не использует JOIN и GROUP BY, вместо этого полагаясь на вложенный выбор.
Я бы определил функцию:
delimiter $$
DROP FUNCTION IF EXISTS `getFakeId`$$
CREATE FUNCTION `getFakeId`() RETURNS int(11)
DETERMINISTIC
begin
return if(@fakeId, @fakeId:=@fakeId+1, @fakeId:=1);
end$$
тогда я мог бы сделать:
select getFakeId() as id, t.* from table t, (select @fakeId:=0) as t2;
Теперь у вас нет подзапроса, который вы не можете иметь в представлениях.