Мне нужно сделать соединение с таблицей/результирующим набором/всем, что имеет целые числа от n до m включительно. Есть ли тривиальный способ получить это, не строя таблицу?
(Кстати, как будет вызываться этот тип конструкции, "Мета-запрос"?)
m-n ограничено чем-то разумным (< 1000s)
Генератор порядковых номеров (CREATE SEQUENCE) в MySQL. Ближайшая вещь AUTO_INCREMENT, которая может помочь вам построить таблицу.
Я нашел это решение в Интернете
SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r
Один запрос, быстрый и делает именно то, что я хотел: теперь я могу "подсчитать" "выбор", найденный из сложного запроса, с уникальными номерами, начиная с 1 и увеличивая один раз для каждой строки результата.
Я думаю, что это также будет работать для проблемы, перечисленной выше: отрегулируйте начальное начальное значение для @row и добавьте предложение limit, чтобы установить максимальное значение.
Кстати: Я думаю, что "r" действительно не требуется.
DDSP
Следующий вернет 1..10000 и не так медленно
SELECT @row := @row + 1 AS row FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(SELECT @row:=0) numbers;
Если вы используете MariaDB fork of MySQL, SEQUENCE engine позволяет напрямую генерировать числовые последовательности. Он делает это, используя виртуальные (поддельные) таблицы с одним столбцом.
Например, чтобы сгенерировать последовательность целых чисел от 1 до 1000, сделайте это
SELECT seq FROM seq_1_to_1000;
От 0 до 11, сделайте это.
SELECT seq FROM seq_0_to_11;
В течение недели ценность последовательных значений DATE, начиная с сегодняшнего дня, сделайте это.
SELECT FROM_DAYS(seq + TO_DAYS(CURDATE)) dateseq FROM seq_0_to_6
Для десятизначных последовательных значений DATE, начинающихся с '2010-01-01', сделайте это.
SELECT FROM_DAYS(seq + TO_DAYS('2010-01-01')) dateseq
FROM seq_0_to_3800
WHERE FROM_DAYS(seq + TO_DAYS('2010-01-01')) < '2010-01-01' + INTERVAL 10 YEAR
Если вы не используете MariaDB, подумайте об этом.
Вы можете попробовать что-то вроде этого:
SELECT @rn:[email protected]+1 as n
FROM (select @rn:=2)t, `order` rows_1, `order` rows_2 --, rows_n as needed...
LIMIT 4
Где order является просто примером некоторой таблицы с достаточно большим набором строк.
Изменить: исходный ответ был неправильным, и любой кредит должен быть у Дэвида Бедного, который представил рабочий пример той же концепции
Существует способ получить диапазон значений в одном запросе, но он немного медленный. Его можно ускорить, используя таблицы кэша.
Предположим, вы хотите выбрать с диапазоном всех значений BOOLEAN:
SELECT 0 as b UNION SELECT 1 as b;
мы можем сделать вид
CREATE VIEW ViewBoolean AS SELECT 0 as b UNION SELECT 1 as b;
то вы можете сделать Byte
CREATE VIEW ViewByteValues AS
SELECT b0.b + b1.b*2 + b2.b*4 + b3.b*8 + b4.b*16 + b5.b*32 + b6.b*64 + b7.b*128 as v FROM
ViewBoolean b0,ViewBoolean b1,ViewBoolean b2,ViewBoolean b3,ViewBoolean b4,ViewBoolean b5,ViewBoolean b6,ViewBoolean b7;
то вы можете сделать
CREATE VIEW ViewInt16 AS
SELECT b0.v + b1.v*256 as v FROM
ViewByteValues b0,ViewByteValues b1;
то вы можете сделать
SELECT v+MIN as x FROM ViewInt16 WHERE v<MAX-MIN;
Чтобы ускорить это, я пропустил автовычисление байтовых значений и сделал себя
CREATE VIEW ViewByteValues AS
SELECT 0 as v UNION SELECT 1 as v UNION SELECT ...
...
...254 as v UNION SELECT 255 as v;
Если вам нужен диапазон дат, вы можете сделать.
SELECT DATE_ADD('start_date',v) as day FROM ViewInt16 WHERE v<NumDays;
или
SELECT DATE_ADD('start_date',v) as day FROM ViewInt16 WHERE day<'end_date';
возможно, вы сможете ускорить это с помощью более быстрой функции MAKEDATE
SELECT MAKEDATE(start_year,1+v) as day FRON ViewInt16 WHERE day>'start_date' AND day<'end_date';
Обратите внимание, что эти трюки ОЧЕНЬ МЕДЛЕННЫ и позволяют создавать последовательности FINITE в заранее определенном домене (например, int16 = 0... 65536)
Я уверен, что вы можете немного изменить запросы, чтобы ускорить процесс, намекая на MySQL, где прекратить вычислять;) (используя предложения ON вместо предложений WHERE и т.д.)
Например:
SELECT MIN + (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) FROM
ViewByteValues b0,
ViewByteValues b1,
ViewByteValues b2,
ViewByteValues b3
WHERE (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) < MAX-MIN;
будет поддерживать ваш SQL-сервер в течение нескольких часов
Однако
SELECT MIN + (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) FROM
ViewByteValues b0
INNER JOIN ViewByteValues b1 ON (b1.v*256<(MAX-MIN))
INNER JOIN ViewByteValues b2 ON (b2.v*65536<(MAX-MIN))
INNER JOIN ViewByteValues b3 ON (b3.v*16777216<(MAX-MIN)
WHERE (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) < (MAX-MIN);
будет работать достаточно быстро - даже если MAX-MIN огромен, если вы ограничиваете результат с помощью LIMIT 1,30 или что-то в этом роде. COUNT (*), однако, займет много времени, и если вы допустите ошибку при добавлении ORDER BY, когда MAX-MIN больше, чем 100k, он снова займет несколько секунд, чтобы вычислить...
Последовательность чисел от 1 до 100.000:
SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5
order by 1
Я использую его для аудита, если какое-то число не соответствует последовательности, примерно так:
select * from (
select 121 id
union all select 123
union all select 125
union all select 126
union all select 127
union all select 128
union all select 129
) a
right join (
SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5
order by 1
) seq on seq.n=a.id
where seq.n between 121 and 129
and id is null
В результате будет зазор числа 122 и 124 последовательности между 121 и 129:
id n
---- ---
null 122
null 124
Может быть, это помогает кому-то!
Насколько велика m?
Вы можете сделать что-то вроде:
create table two select null foo union all select null;
create temporary table seq ( foo int primary key auto_increment ) auto_increment=9 select a.foo from two a, two b, two c, two d;
select * from seq where foo <= 23;
где auto_increment установлен в n, а предложение where сравнивается с m и количеством повторений двух таблиц, по крайней мере, является ceil (log (m-n + 1)/log (2)).
(Не временная две таблицы могут быть опущены путем замены двух (выберите null foo union all select null) в временной таблице временного размещения.)
Вот компактная бинарная версия метода, используемого в других ответах здесь:
select ((((((b7.0 << 1 | b6.0) << 1 | b5.0) << 1 | b4.0)
<< 1 | b3.0) << 1 | b2.0) << 1 | b1.0) << 1 | b0.0 as n
from (select 0 union all select 1) as b0,
(select 0 union all select 1) as b1,
(select 0 union all select 1) as b2,
(select 0 union all select 1) as b3,
(select 0 union all select 1) as b4,
(select 0 union all select 1) as b5,
(select 0 union all select 1) as b6,
(select 0 union all select 1) as b7
Не существует уникальных или фаз сортировки, нет преобразования строк в число, нет арифметических операций, и каждая фиктивная таблица имеет только 2 строки, поэтому она должна быть довольно быстрой.
В этой версии используются 8 "битов", поэтому она подсчитывается от 0 до 255, но вы можете легко настроить ее.
Этот запрос генерирует числа от 0 до 1023. Я считаю, что он будет работать в любом sql-сервере базы данных:
select
i0.i
+i1.i*2
+i2.i*4
+i3.i*8
+i4.i*16
+i5.i*32
+i6.i*64
+i7.i*128
+i8.i*256
+i9.i*512
as i
from
(select 0 as i union select 1) as i0
cross join (select 0 as i union select 1) as i1
cross join (select 0 as i union select 1) as i2
cross join (select 0 as i union select 1) as i3
cross join (select 0 as i union select 1) as i4
cross join (select 0 as i union select 1) as i5
cross join (select 0 as i union select 1) as i6
cross join (select 0 as i union select 1) as i7
cross join (select 0 as i union select 1) as i8
cross join (select 0 as i union select 1) as i9
Похоже, вы можете построить достаточно большие множества с помощью:
select 9 union all select 10 union all select 11 union all select 12 union all select 13 ...
Я получил переполнение стека парсера в 5300, на 5.0.51a.
Предупреждение: если вы вставляете числа по одной строке за раз, вы в конечном итоге выполняете N команд, где N - количество строк, которые нужно вставить.
Вы можете получить это до O (log N), используя временную таблицу (см. ниже для вставки чисел от 10000 до 10699):
mysql> CREATE TABLE `tmp_keys` (`k` INTEGER UNSIGNED, PRIMARY KEY (`k`));
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO `tmp_keys` VALUES (0),(1),(2),(3),(4),(5),(6),(7);
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+8 from `tmp_keys`;
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+16 from `tmp_keys`;
Query OK, 16 rows affected (0.03 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+32 from `tmp_keys`;
Query OK, 32 rows affected (0.03 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+64 from `tmp_keys`;
Query OK, 64 rows affected (0.03 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+128 from `tmp_keys`;
Query OK, 128 rows affected (0.05 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+256 from `tmp_keys`;
Query OK, 256 rows affected (0.03 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+512 from `tmp_keys`;
Query OK, 512 rows affected (0.11 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> INSERT INTO inttable SELECT k+10000 FROM `tmp_keys` WHERE k<700;
Query OK, 700 rows affected (0.16 sec)
Records: 700 Duplicates: 0 Warnings: 0
edit: fyi, к сожалению, это не будет работать с текущей временной таблицей с MySQL 5.0, поскольку она не может вставляться в себя ( вы можете отскочить назад и вперед между двумя временными таблицами).
edit: вы можете использовать механизм хранения MEMORY, чтобы это фактически не было утечкой в "реальной" базе данных. Интересно, разработал ли кто-то механизм виртуального хранения NUMBERS для создания виртуального хранилища для создания таких последовательностей. (увы, непереносимо вне MySQL)
SET @seq := 0;
SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM your_table yt;
SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM (SELECT @seq := 0) s, your_table yt;
Функция FLOOR() используется здесь, чтобы получить INTEGER вместо FLOAT. Иногда это необходимо.
Мой ответ был вдохновлен ответом Дэвида Бедного. Спасибо Дэвид!
Если вы использовали Oracle, "конвейерные функции" - это путь. К сожалению, у MySQL нет такой конструкции.
В зависимости от масштаба номеров, которые вы хотите наборы, я вижу два простых способа: вы либо заполняете временную таблицу с помощью только нужных вам чисел (возможно, используя таблицы памяти, заполненные хранимой процедурой) для одного запрос или, вперед, вы создаете большую таблицу, которая насчитывает от 1 до 1 000 000 и выбирает ограниченные области.
попробуйте это.. у меня работает в MySQL версии 8.0. Вы можете изменить ниже запрос в соответствии с вашим необходимым диапазоном
WITH recursive numbers AS (
select 0 as Date
union all
select Date + 1
from numbers
where Date < 10)
select * from numbers;
и да без создания таблицы, как указано в вашем посте
Счетчик от 1 до 1000:
:
select tt.row from
(
SELECT cast( concat(t.0,t2.0,t3.0,t4.0) + 1 As UNSIGNED) as 'row' FROM
(select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t,
(select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
) tt
order by tt.row
Кредиты: ответ, комментарий Сета МакКоли под ответом.