sql - sumar - select que calcule datos con registro anterior

¿Cuál es el SQL para ''siguiente'' y ''anterior'' en una tabla?

Question

Tengo una tabla de elementos, cada uno de los cuales tiene una fecha asociada. Si tengo la fecha asociada con un elemento, ¿cómo consulto la base de datos con SQL para obtener los elementos "anterior" y "subsiguiente" en la tabla?

No es posible simplemente agregar (o restar) un valor, ya que las fechas no tienen un espacio regular entre ellas.

Una posible aplicación sería enlaces ''anterior / siguiente'' en un álbum de fotos o aplicación web de blog, donde los datos subyacentes están en una tabla SQL.

Creo que hay dos posibles casos:

En primer lugar, donde cada fecha es única:

Data de muestra:

1,3,8,19,67,45

¿Qué consulta (o consultas) daría 3 y 19 cuando se suministró 8 como parámetro? (o las filas 3,8,19). Tenga en cuenta que no siempre hay tres filas que se devolverán: al final de la secuencia, faltaría una.

En segundo lugar , si hay una clave única separada para ordenar los elementos, ¿cuál es la consulta para devolver el conjunto ''rodeando'' una fecha? La orden esperada es por fecha y luego clave.

Data de muestra:

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8

Qué consulta para ''8'' devuelve el conjunto:

2:3,3:8,4:8,16:8,5:19

o qué consulta genera la tabla:

key date prev-key next-key
1   1    null     2
2   3    1        3
3   8    2        4
4   8    3        16
5   19   16       10
10  19   5        11
11  67   10       15
15  45   11       null
16  8    4        5

El orden de la tabla no es importante, solo los campos de la siguiente clave y la clave anterior.

Tanto TheSoftwareJedi como Cade Roux tienen soluciones que funcionan para los conjuntos de datos que publiqué anoche. Para la segunda pregunta, ambos parecen fallar para este conjunto de datos:

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8

La orden esperada es por fecha clave, por lo que un resultado esperado podría ser:

2:3,3:8,4:8,16:8,5:19

y otro:

key date prev-key next-key
1   1    null     2
2   3    1        3
3   8    2        4
4   8    3        16
5   19   16       10
10  19   5        11
11  67   10       15
15  45   11       null
16  8    4        5

El orden de la tabla no es importante, solo los campos de la siguiente clave y la clave anterior.

Answer 1

En primer lugar, esto debería funcionar (el ORDER BY es importante):

select min(a)
from theTable
where a > 8

select max(a)
from theTable
where a < 8

Para la segunda pregunta que te pedí que hicieras ...:

    select * 
    from theTable
    where date = 8

    union all

    select *
    from theTable
    where key = (select min(key) 
                 from theTable
                 where key > (select max(key)
                              from theTable
                              where date = 8)
                )

    union all

    select *
    from theTable
    where key = (select max(key) 
                 from theTable
                 where key < (select min(key)
                              from theTable
                              where date = 8)
                )

    order by key

Answer 2

Mi propio intento de solución de conjunto, basado en TheSoftwareJedi.

Primera pregunta:

select date from test where date = 8
union all
select max(date) from test where date < 8
union all
select min(date) from test where date > 8
order by date;

Segunda pregunta:

Al depurar esto, utilicé el conjunto de datos:

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8,17:3,18:1

para dar este resultado:

select * from test2 where date = 8
union all
select * from (select * from test2
                   where date = (select max(date) from test2 
                                     where date < 8)) 
    where key = (select max(key) from test2 
                    where date = (select max(date) from test2 
                                      where date < 8))
union all
select * from (select * from test2
                   where date = (select min(date) from test2 
                                     where date > 8)) 
    where key = (select min(key) from test2 
                    where date = (select min(date) from test2 
                                      where date > 8))
order by date,key;

En ambos casos, la orden final por cláusula es estrictamente optativa.

Answer 3

Prueba esto...

SELECT TOP 3 * FROM YourTable
WHERE Col >= (SELECT MAX(Col) FROM YourTable b WHERE Col < @Parameter)
ORDER BY Col

Answer 4

Se une a sí mismo.

Para la mesa:

/*
CREATE TABLE [dbo].[_203302](
    [val] [int] NOT NULL
) ON [PRIMARY]
*/

Con el parámetro @val

SELECT cur.val, MAX(prv.val) AS prv_val, MIN(nxt.val) AS nxt_val
FROM _203302 AS cur
LEFT JOIN _203302 AS prv
    ON cur.val > prv.val
LEFT JOIN _203302 AS nxt
    ON cur.val < nxt.val
WHERE cur.val = @val
GROUP BY cur.val

Puede hacer que este sea un procedimiento almacenado con parámetros de salida o simplemente unir esto como una subconsulta correlacionada con los datos que está extrayendo.

Sin el parámetro, para sus datos el resultado sería:

val         prv_val     nxt_val
----------- ----------- -----------
1           NULL        3
3           1           8
8           3           19
19          8           45
45          19          67
67          45          NULL

Para el ejemplo modificado, usa esto como una subconsulta correlacionada:

/*
CREATE TABLE [dbo].[_203302](
    [ky] [int] NOT NULL,
    [val] [int] NOT NULL,
    CONSTRAINT [PK__203302] PRIMARY KEY CLUSTERED (
        [ky] ASC
    )
)
*/

SELECT cur.ky AS cur_ky
        ,cur.val AS cur_val
        ,prv.ky AS prv_ky
        ,prv.val AS prv_val
        ,nxt.ky AS nxt_ky
        ,nxt.val as nxt_val
FROM (
    SELECT cur.ky, MAX(prv.ky) AS prv_ky, MIN(nxt.ky) AS nxt_ky
    FROM _203302 AS cur
    LEFT JOIN _203302 AS prv
        ON cur.ky > prv.ky
    LEFT JOIN _203302 AS nxt
        ON cur.ky < nxt.ky
    GROUP BY cur.ky
) AS ordering
INNER JOIN _203302 as cur
    ON cur.ky = ordering.ky
LEFT JOIN _203302 as prv
    ON prv.ky = ordering.prv_ky
LEFT JOIN _203302 as nxt
    ON nxt.ky = ordering.nxt_ky

Con la salida como se esperaba:

cur_ky      cur_val     prv_ky      prv_val     nxt_ky      nxt_val
----------- ----------- ----------- ----------- ----------- -----------
1           1           NULL        NULL        2           3
2           3           1           1           3           8
3           8           2           3           4           19
4           19          3           8           5           67
5           67          4           19          6           45
6           45          5           67          NULL        NULL

En SQL Server, prefiero hacer que la subconsulta sea una Expresión de tabla común. Esto hace que el código parezca más lineal, menos anidado y más fácil de seguir si hay una gran cantidad de anidamientos (también, se requiere menos repetición en algunas re-uniones).

Answer 5

Seleccione max (element) From Data Where Element <8

Unión

Seleccione min (element) From Data Where Element> 8

Pero en general es más útil pensar en sql para operaciones orientadas a conjuntos en lugar de operaciones iterativas.

Answer 6

SELECT ''next'' AS direction, MIN(date_field) AS date_key
FROM table_name
  WHERE date_field > current_date
GROUP BY 1 -- necessity for group by varies from DBMS to DBMS in this context
UNION
SELECT ''prev'' AS direction, MAX(date_field) AS date_key
  FROM table_name
  WHERE date_field < current_date
GROUP BY 1
ORDER BY 1 DESC;

Produce:

direction  date_key
---------  --------
prev              3
next             19

Answer 7

Si su RDBMS es compatible con LAG y LEAD, esto es sencillo (Oracle, PostgreSQL, SQL Server 2012)

Estos permiten elegir la fila a cada lado de cualquier fila dada en una sola consulta