salve,
non so se ho ben compreso...
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t (
ID_Annuncio int NOT NULL IDENTITY PRIMARY KEY,
Titolo varchar(10) NOT NULL DEFAULT 'a',
Testo varchar(10) NOT NULL DEFAULT 't',
Scadenza date NULL,
ID_Rinnovato int NULL,
[OnLine] bit NOT NULL DEFAULT 1,
);
GO
INSERT INTO dbo.t
VALUES ('a', 't', '20120101', NULL, 1),
('a', 't1', '20120101', 1, 1),
('a', 't2', '20120101', 1, 1),
('a', 't3', '20120101', 1, 1),
('a', 't4', NULL, 1, 0);
INSERT INTO dbo.t
VALUES ('b', 't', '20120101', NULL, 1),
('b', 't2', NULL, 6, 0);
INSERT INTO dbo.t
VALUES ('c', 't', '20120101', NULL, 1),
('c', 't1', '20120101', 8, 1)
INSERT INTO dbo.t
VALUES ('d', 't', '20120101', NULL, 1);
GO
PRINT 'articolo specifico';
DECLARE @Id int = 1;
WITH cte AS (
SELECT t.ID_Rinnovato, t.ID_Annuncio,
ROW_NUMBER() OVER (PARTITION BY t.ID_Rinnovato ORDER BY CASE WHEN t.Scadenza IS NULL THEN '21001231' ELSE t.Scadenza END DESC) AS [r]
FROM dbo.t t
WHERE t.ID_Rinnovato IS NOT NULL AND t.OnLine = 1
)
SELECT t.ID_Annuncio, t.OnLine, t.Scadenza, t.Titolo, t.Testo
, t2.ID_Annuncio AS [IdProsecuzione], t2.OnLine AS [ProsecuzioneOnLine], t2.Scadenza AS [ScadenzaProsecuzione]
, t2.Titolo AS [TitoloProsecuzione], t2.Testo AS [TestoProsecuzione]
FROM dbo.t t
LEFT JOIN cte c ON c.ID_Rinnovato = t.ID_Annuncio AND c.[r] = 1
LEFT JOIN dbo.t t2 ON t2.ID_Annuncio = c.ID_Annuncio
WHERE t.ID_Annuncio = @Id;
GO
PRINT 'tutti gli articoli validi';
WITH cte AS (
SELECT t.ID_Rinnovato, t.ID_Annuncio,
ROW_NUMBER() OVER (PARTITION BY t.ID_Rinnovato ORDER BY CASE WHEN t.Scadenza IS NULL THEN '21001231' ELSE t.Scadenza END DESC) AS [r]
FROM dbo.t t
WHERE t.ID_Rinnovato IS NOT NULL AND t.OnLine = 1
)
SELECT t.ID_Annuncio, t.OnLine, t.Scadenza, t.Titolo, t.Testo
, t2.ID_Annuncio AS [IdProsecuzione], t2.OnLine AS [ProsecuzioneOnLine], t2.Scadenza AS [ScadenzaProsecuzione]
, t2.Titolo AS [TitoloProsecuzione], t2.Testo AS [TestoProsecuzione]
FROM dbo.t t
LEFT JOIN cte c ON c.ID_Rinnovato = t.ID_Annuncio AND c.[r] = 1
LEFT JOIN dbo.t t2 ON t2.ID_Annuncio = c.ID_Annuncio
WHERE t.ID_Rinnovato IS NULL;
GO
DROP TABLE dbo.t;allora, abbiamo una serie di annunci... nel caso l'attributo ID_Rinnovato sia NULL, significa che la riga e' "la riga madre" per l'annuncio stesso... per convenienza indichiamo una data di scadenza che pero' probabilmente puo' anche essere NULL nel caso la scadenza non sia definita...
con la common table expression (cte), per ogni "annuncio madre", ricerchiamo tutti i "figli" e li "numeriamo" (attributo [r]) con la funzione di windowing ROW_NUMBER, ordinando per data di scadenza inversa (e nel caso sia NULL gli attribuiamo una scadenza massima del 31/12/2100)... avendo la numerazione ordinale inversa per ogni "annuncio madre", sappiamo che l'ordinale "1" identifica il piu' lontano, quindi nella proiezione finale possiamo mettere in join l'ID_Annuncio di tale riga con la tabella stessa per ottenere le informazioni relative...
ho utilizzato la join addizionale per evitare di recuperare nella cte tutti gli attributi non basilare (titolo e testo) che sicuramente saranno "corposi" con quindi un elevato ricorso sia di I/O che di memoria occupata, per quindi recuperarli solo nell'estrazione filtrata dell'unico ID maggiore richiesto per ogni articolo...
pero', di nuovo, non so se ho ben compreso :)
saluti
Modificato da Andrea Montanari il 02 agosto 2012 01.43 - dimenticavo il filtro per Online = 1
