17

Jul

[SQL] Usare una query ricorsiva con il CTE per avere il “precedente” ed il “successivo” elemento di una tabella/vista

Ciao a tutti

 

approfitto sempre del periodo estivo per scrivervi 1 po sul come è possibile tramite SQL Server avere a disposizione in una query l’elemento precedente e successivo per ogni elemento della query stessa

 

immaginiamo di avere una select per le nostre fatture, filtrate per cliente, e ordinate per data

potremmo farci dire se una data fattura è insoluta, e se non è la prima, così come potremo anche sapere quante siano queste fatture non pagate di data precedente o successiva a quella desiderata

 

farò un semplice esempio su una tabella auto-generata:

 

SQL:

 

--DECOMMENTARE PER CREARE LA TABELLA

--CREATE TABLE [dbo].[ValuesTable](
--    [ID] [int] IDENTITY(1,1) NOT NULL,
--    [Value] [varchar](50) NOT NULL,
-- CONSTRAINT [PK_ValuesTable] PRIMARY KEY CLUSTERED
--(
--    [ID] ASC
--)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
--) ON [PRIMARY]


--PULIZIA E CREAZIONE DEI DATI
--COMMENTARE PER USO REALE SU TABELLA DATI

if exists (select * from ValuesTable)
    truncate table ValuesTable

declare @i int = 0;

while @i < 1000
begin
    insert into ValuesTable select 'value' + CONVERT(varchar(50), @i)
    set @i = @i + 1
end
go

--SELECT RICORSIVA PER CERCARE IL PRECEDENTE ED IL SUCCESSIVO
--IN BASE AL ROW_NUMBER

with tb as
(
    select ROW_NUMBER() over (order by id) as RN, *
    from ValuesTable
)
select
    c.ID as actualID,
    c.Value as actualValue,
    p.ID as previousID,
    p.Value as previousValue,
    n.ID as nextID,
    n.Value as nextValue
from tb as c
left outer join tb as p on p.id = c.id -1
left outer join tb as n on n.id = c.id +1

 

questo il risultato:

 

actualID    actualValue                                        previousID  previousValue                                      nextID      nextValue
----------- -------------------------------------------------- ----------- -------------------------------------------------- ----------- --------------------------------------------------
1           value0                                             NULL        NULL                                               2           value1
2           value1                                             1           value0                                             3           value2
3           value2                                             2           value1                                             4           value3
4           value3                                             3           value2                                             5           value4
5           value4                                             4           value3                                             6           value5
6           value5                                             5           value4                                             7           value6
7           value6                                             6           value5                                             8           value7
8           value7                                             7           value6                                             9           value8
9           value8                                             8           value7                                             10          value9
10          value9                                             9           value8                                             11          value10

 

a presto

by Antonio Esposito on 7/17/2012