Uno dei quesiti più tipici che vengono posti ad uno sviluppatore di database quello di recuperare un numero finito di record da una o più tabelle di un database. Per svolgere il compito richiesto Transact SQL ci mette a disposizione la clausola TOP, che permette di limitare il numero di righe ritornate dal rowset.
Un esempio: il database MYNEWS
Supponiamo che il nostro sito web pubblichi delle news per informare i propri utenti su vari argomenti, in particolar modo tecnologia, musica e sport. All'interno del sito oltre a visualizzare le news vogliamo anche pubblicare una classifica di gradimento, precisamente una classifica che contenga la notizia più richiesta per ogni singola categoria.
Proprio su quest'ultima richiesta si concentreranno i nostri sforzi, dovremo trovare una soluzione lato SQL Server che sia elegante (senza usare cursori) ed efficace (performante e veloce) per recuperare con una singola SELECT le righe necessarie per creare la nostra classifica.
Primo passo: creiamo il database
Apriamo il query analyzer e creiamo il database MYNEWS sfruttando tutte le
opzioni di default di SQL Server
CREATE DATABASE MYNEWS
Con questa istruzione abbiamo creato all'interno di SQL Server un nuovo database dal nome MYNEWS con le impostazioni di DEFAULT offerta da SQL Server.
Secondo passo: creiamo le tabelle e le relazioni
All'interno del database MYNEWS servono al nostro scopo due sole tabelle, una per contenere le news che chiameremo news e l'altra per contenere le categorie in cui organizziamo le news e che chiameremo cat_news.
Vediamo ora la relazione esistente tra le news e le loro categorie, per comodità rappresentata graficamente qui sotto:
Ad ogni riga della tabella news deve avere associata una sola riga della tabella cat_news, al contrario per ogni riga della tabella cat_news possono collegate una o più righe della tabella news. La relazione tra news e categorie è uno a molti. Ovviamente la chiave primaria della tabella news sarà newsID, i valori di questa colonna saranno generati automaticamente dal campo IDENTITY di SQL Server, la stessa cosa per la colonna cat_newsID della tabella categorie (dobbiamo usare la funzione IDENTITY di SQL Server per generare i valori univoci delle chiavi primarie perché per queste due tabelle non ci sono chiavi naturali candidate a farlo! Ad esempio un codice fiscale!).
Ecco lo script per creare le tabelle:
USE MYNEWS GO CREATE TABLE news ( newsID INT IDENTITY(1,1) PRIMARY KEY, cat_newsID INT NOT NULL, titolo VARCHAR(255) NOT NULL, corpo VARCHAR(1000) NOT NULL, hits INT DEFAULT(0), data_creazione DATETIME DEFAULT(GETDATE()) ) GO CREATE TABLE cat_news ( cat_newsID INT IDENTITY(1,1) PRIMARY KEY, descrizione VARCHAR(255) ) GO
Ed anche lo script per creare la relazione tra le news e le categorie:
ALTER TABLE [dbo].[news] ADD CONSTRAINT [FK_news_cat_news] FOREIGN KEY ([cat_newsID]) REFERENCES [dbo].[cat_news] ([cat_newsID])
ora inseriamo dei valori all'interno delle tabelle:
--Annullo i messaggi del conteggio delle righe inserite SET NOCOUNT ON --Inserisco le categorie delle news INSERT INTO cat_news VALUES ('Tecnologia') INSERT INTO cat_news VALUES ('Musica') INSERT INTO cat_news VALUES ('Sport') --Inserisco le news INSERT INTO news VALUES (1,'Prima news sulla Tecnologia','Corpo della news',12,DEFAULT) INSERT INTO news VALUES (1,'Seconda news sulla Tecnologia','Corpo della news',1,DEFAULT) INSERT INTO news VALUES (1,'Terza news sulla Tecnologia','Corpo della news',142,DEFAULT) INSERT INTO news VALUES (2,'Prima news sulla Musica','Corpo della news',12,DEFAULT) INSERT INTO news VALUES (2,'Seconda news sulla Musica','Corpo della news',62,DEFAULT) INSERT INTO news VALUES (2,'Terza news sulla Musica','Corpo della news',11,DEFAULT) INSERT INTO news VALUES (3,'Prima news sullo Sport','Corpo della news',124,DEFAULT) INSERT INTO news VALUES (3,'Seconda news sullo Sport','Corpo della news',412,DEFAULT) INSERT INTO news VALUES (3,'Terza news sullo Sport','Corpo della news',142,DEFAULT)
Terzo passo: la query per la classifica delle news più viste
Il risultato a cui dobbiamo pervenire è quello rappresentato nella figura sottostante:
i modi per raggiungerlo possono essere diversi, ma a noi interessa una soluzione elegante che non faccia uso di cursori lato server e che non ricorra all'uso di sql dinamico generato dalle pagine ASP.
La soluzione finale
I vincoli che ci siamo imposti non sono semplici da superare ma con una piccola conoscenza dell'SQL standard possiamo pervenire ad una soluzione molto elegante facendo uso di una semplice subquery o sottoquery per dirla all'italiana!
SELECT N3.descrizione AS Categoria, N1.titolo AS Titolo, N1.hits AS Hits FROM news N1 INNER JOIN cat_news N3 ON N3.cat_newsID = N1.cat_newsID WHERE N1.newsID IN ( SELECT TOP 1 N2.newsID -- la prima news FROM news N2 WHERE N2.cat_newsID = N1.cat_newsID --per ogni categoria ORDER BY N2.hits DESC -- che ha il maggior numero di visualizzazioni ) ORDER BY N3.cat_newsID, N1.hits DESC
Facciamo la scomposizione della query per analizzare in dettaglio la soluzione, la query è composta da:
- Una query di JOIN classica tra la tabella news e cat_news, questa indispensabile per il matching delle news con le oppurtune categorie alla quale appartengono.
- Una subquery filtrante (indicata in grassetto), che ritorna la news più letta della tabella news, questa grazie all'uso della clausola
TOP. Questa subquery, ed è proprio qui la magia, però possiede una clausola WHERE che filtra solo le news più lette per ogni categoria.
A livello SQL il punto 2 appena descritto viene implementato grazie all'istruzione:
WHERE N2.cat_newsID = N1.cat_newsID
Quindi dal primo insieme di righe offerto dalla query al punto 1, noi filtriamo solamente le righe che soddisfano la condizione imposta nella subquery al punto 2.
Compitino
A scopo didattico provate a pervenire allo stesso risultato attraverso una pagina ASP ricorrendo all'uso di SQL dinamico, vi accorgerete che il numero di righe necessarie ad implementare una simile soluzione sarà enormente maggiore! Inoltre se trovate un'altra soluzione mandatemela pure via email al mio indirizzo info@lucamilan.it sarò felice di valutarla (ricordate l'importante è fare tutto con un'unica SELECT).
In allegato troverete il file codice.sql che contiene il codice SQL per generare il vostro esempio di prova!
