create - generar una secuencia de enteros en MySQL
mysql select numbers from 1 to n (14)
Necesito hacer una combinación con una tabla / conjunto de resultados / lo que tenga los números enteros n hasta m inclusive. ¿Hay alguna manera trivial de obtener eso sin solo construir la mesa?
(Por cierto, ¿cómo se llamaría ese tipo de constructo, una "búsqueda de meta"?)
mn está limitado a algo razonable (<1000)
La forma más sencilla de hacer esto es:
SET @seq := 0;
SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM your_table yt;
o en una consulta:
SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM (SELECT @seq := 0) s, your_table yt;
La función FLOOR() se usa aquí para obtener un INTEGER en lugar de un FLOAT . A veces es necesario.
Mi respuesta fue inspirada por la respuesta de David Poor . Gracias David!
¿Qué tan grande es m?
Podrías hacer algo como:
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;
donde auto_increment se establece en n y la cláusula where se compara con m y el número de veces que se repiten las dos tablas es al menos ceil (log (m-n + 1) / log (2)).
(La tabla no temporal dos podría omitirse reemplazando dos con (seleccione nulo foo unión todo seleccione nulo) en la creación de tabla temporal seq.)
Advertencia: si inserta números una fila a la vez, terminará ejecutando N comandos donde N es el número de filas que necesita insertar.
Puede bajar esto a O (log N) usando una tabla temporal (vea a continuación para insertar números del 10000 al 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, desafortunadamente esto no funcionará con una verdadera tabla temporal con MySQL 5.0 ya que no se puede insertar en sí misma (podría rebotar entre dos tablas temporales).
editar: puede usar un motor de almacenamiento de MEMORIA para evitar que esto sea un gasto en la base de datos "real". Me pregunto si alguien ha desarrollado un motor de almacenamiento virtual "NÚMEROS" para crear instancias de almacenamiento virtual y crear secuencias como esta. (por desgracia, no portable fuera de MySQL)
Aquí hay una versión binaria compacta de la técnica utilizada en otras respuestas aquí:
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
No hay fases únicas ni de clasificación, no hay conversión de cadena a número, no hay operaciones aritméticas, y cada tabla ficticia solo tiene 2 filas, por lo que debería ser bastante rápida.
Esta versión usa 8 "bits", por lo que cuenta de 0 a 255, pero puede modificarla fácilmente.
Encontré esta solución en la web
SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r
Consulta única, rápida y hace exactamente lo que quería: ahora puedo "enumerar" las "selecciones" que se encuentran en una consulta compleja con números únicos que comienzan en 1 y se incrementan una vez por cada fila en el resultado.
Creo que esto también funcionará para el problema mencionado anteriormente: ajuste el valor de inicio inicial para @row y agregue una cláusula de límite para establecer el máximo.
Por cierto: creo que la "r" no es realmente necesaria.
ddsp
Esta consulta genera números del 0 al 1023. Creo que funcionaría en cualquier sabor de base de datos 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
Hay una manera de obtener un rango de valores en una sola consulta, pero es un poco lento. Se puede acelerar usando tablas de caché.
supongamos que desea seleccionar con un rango de todos los valores BOOLEAN:
SELECT 0 as b UNION SELECT 1 as b;
podemos hacer una vista
CREATE VIEW ViewBoolean AS SELECT 0 as b UNION SELECT 1 as b;
entonces puedes hacer un 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;
entonces puedes hacer un
CREATE VIEW ViewInt16 AS
SELECT b0.v + b1.v*256 as v FROM
ViewByteValues b0,ViewByteValues b1;
entonces puedes hacer un
SELECT v+MIN as x FROM ViewInt16 WHERE v<MAX-MIN;
Para acelerar esto, omití el cálculo automático de los valores de bytes y me hice un
CREATE VIEW ViewByteValues AS
SELECT 0 as v UNION SELECT 1 as v UNION SELECT ...
...
...254 as v UNION SELECT 255 as v;
Si necesita un rango de fechas, puede hacerlo.
SELECT DATE_ADD(''start_date'',v) as day FROM ViewInt16 WHERE v<NumDays;
o
SELECT DATE_ADD(''start_date'',v) as day FROM ViewInt16 WHERE day<''end_date'';
es posible que puedas acelerar esto con la función MAKEDATE ligeramente más rápida
SELECT MAKEDATE(start_year,1+v) as day FRON ViewInt16 WHERE day>''start_date'' AND day<''end_date'';
Tenga en cuenta que estos trucos son MUY LENTOS y solo permiten la creación de secuencias FINITE en un dominio predefinido (por ejemplo int16 = 0 ... 65536)
Estoy seguro de que puede modificar las consultas un poco para acelerar las cosas al hacer alusión a MySQL donde dejar de calcular;) (utilizando cláusulas ON en lugar de cláusulas WHERE y cosas por el estilo)
Por ejemplo:
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;
mantendrá su servidor SQL ocupado durante unas horas
sin embargo
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);
funcionará razonablemente rápido, incluso si MAX-MIN es enorme, siempre y cuando limite el resultado con LIMIT 1,30 o algo así. un COUNT (*) sin embargo tomará años y si comete el error de agregar ORDER BY cuando MAX-MIN es mayor que 100k, tardará de nuevo varios segundos en calcular ...
Lo siguiente devolverá 1..10000 y no es tan lento
SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 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 3 union all select 4 union all select 5 union all select 6 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 3 union all select 4 union all select 5 union all select 6 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 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(SELECT @row:=0)
Parece que puede construir conjuntos razonablemente grandes con:
select 9 union all select 10 union all select 11 union all select 12 union all select 13 ...
Obtuve un desbordamiento de la pila del analizador en el 5300, en 5.0.51a.
Podrías probar algo como esto:
SELECT @rn:=@rn+1 as n
FROM (select @rn:=2)t, `order` rows_1, `order` rows_2 --, rows_n as needed...
LIMIT 4
Donde el order es solo un ejemplo de alguna tabla con un conjunto de filas razonablemente grande.
Editar: La respuesta original fue incorrecta, y cualquier crédito debe ir a David Poor, que proporcionó un ejemplo de trabajo del mismo concepto
Secuencia de números entre 1 y 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
Lo uso para auditar si algún número está fuera de secuencia, algo como esto:
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
El resultado será la brecha del número 122 y 124 de la secuencia entre 121 y 129:
id n
---- ---
null 122
null 124
Tal vez ayuda a alguien!
Si está utilizando el fork MariaDB de MySQL , el motor SEQUENCE permite la generación directa de secuencias numéricas. Hace esto usando tablas virtuales (falsas) de una columna.
Por ejemplo, para generar la secuencia de enteros de 1 a 1000, haz esto
SELECT seq FROM seq_1_to_1000;
Para 0 a 11, haz esto.
SELECT seq FROM seq_0_to_11;
Para una semana de valor de DATE consecutivos a partir de hoy, haga esto.
SELECT FROM_DAYS(seq + TO_DAYS(CURDATE)) dateseq FROM seq_0_to_6
Durante una década, los valores consecutivos de DATE que comienzan con ''2010-01-01'' lo hacen.
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
Si no está usando MariaDB, considérelo.
Si estuviera usando Oracle, las "funciones segmentadas" serían el camino a seguir. Desafortunadamente, MySQL no tiene tal construcción.
Dependiendo de la escala de los números que desea establecer, veo dos formas simples de hacerlo: o rellena una tabla temporal con solo los números que necesita (posiblemente usando tablas de memoria pobladas por un procedimiento almacenado) para una sola consulta o, Delante, construyes una gran tabla que cuenta de 1 a 1,000,000 y seleccionas regiones limitadas de ella.
No hay un generador de número de secuencia ( CREATE SEQUENCE ) en MySQL. Lo más cercano es AUTO_INCREMENT , que puede ayudarlo a construir la tabla.