|
Queries |
| 2.27 Esempio dell'uso delle Subquery (2) |
| Roberto, Giorgio Rancati |
Ipotiziamo di avere una tabella T1 che abbia la seguente struttura:ID Contatore e chiave primaria NomeCliente Testo Data_Arrivo Data/ora Data_Partenza Data/oraIn questa tabella, in un albergo, vengono memorizzati i clienti in arrivo ed in partenza (un record per ogni cliente). Ipotiziamo che con una query si voglia ottenere una lista, giorno per giorno, delle persone presenti in albergo e quelle che in quel giorno sono in partenza. Per prima cosa si creerà una tabella di supporto chiamata Calendario che contiene un solo campo di tipo Data/ora chiamato Data; la tabella dovrà contenere tutti i giorni dell'anno, uno per record. La query che visualizza i risultati di cui sopra potrebbe avere il seguente codice SQL: SELECT Calendario.Data, DCount("*","T1","Data_Arrivo <=#" & Format([Data],"mm/dd/yyyy") & "# AND Data_Partenza >=#" & Format([Data],"mm/dd/yyyy") & "#") AS Presenze,
DCount("*","T1","Data_Partenza =#" & Format([Data],"mm/dd/yyyy") & "#") AS In_Partenza
FROM Calendario
WHERE (((Calendario.Data) Between DMin("Data_Arrivo","T1") And DMax("Data_PArtenza","T1")));
Questo è il codice SQL della query che nel database di esempio allegato a questa FAQ si chiama ConFunzioniAggregazione.Questa query sicuramente visualizzerà quanto desiderato, ma presenta due inconvenienti: se i record sono molti potrebbe risultare abbastanza lenta ed inoltre risulta non trasportabile su piattaforme diverse da Access visto che fa riferimento a quattro funzioni di aggregazione sui dominii che sono proprie di Access. Per migliorare la performance della query e rendere il codice SQL trsportabile su altre piattaforme, si può sostituire le funzioni di aggregazione sui dominii con altrettante subqueries; la query così modificata avrà il seguente codice SQL:
SELECT Data, (SELECT Count(1)
FROM T1
WHERE Data BETWEEN Data_Arrivo AND
Data_Partenza) AS Presenze,
(SELECT Count(1)
FROM T1
WHERE Data = Data_Partenza) AS In_Partenza
FROM Calendario
WHERE Data BETWEEN (SELECT MIN(Data_Arrivo) FROM T1) AND
(SELECT MAX(DAta_Partenza) FROM T1);
Questo è il codice SQL della query che nel database di esempio allegato a questa FAQ si chiama ConSubquery.In questa query è mostrato come usare le subquery come campi della SELECT e come secondo temine di paragone di una proposizione WHERE. In termini di performance le cose risulteranno un po' migliorate, ma in presenza di molti dati le subqueries rallenteranno molto Access perché vengono valutate riga per riga, così come avviene per le funzioni di aggregazione della prima query. Sarebbe pertanto meglio trasformare le 4 subquery in tabelle derivate sfruttando gli inner e gli outer join del caso, ad esempio usando questo codice SQL: SELECT Calendario.Data,
Count(1) AS Presenze,
Val(Nz([In_Partenza],0)) AS In_Uscita
FROM ((Calendario
INNER JOIN
[SELECT Min(Data_Arrivo) AS DA,
Max(Data_Partenza) AS DP
FROM T1;]. AS Tx
ON (Calendario.Data >= Tx.DA) AND
(Calendario.Data <= Tx.DP))
LEFT JOIN
[SELECT T1.Data_Arrivo,
T1.Data_Partenza
FROM T1;]. AS T2
ON (Calendario.Data <= T2.Data_Partenza) AND
(Calendario.Data >= T2.Data_Arrivo))
LEFT JOIN
[SELECT T1.Data_Partenza, Count(1) AS In_Partenza
FROM T1
GROUP BY T1.Data_Partenza;]. AS T3
ON Calendario.Data = T3.Data_Partenza
GROUP BY Calendario.Data, T3.In_Partenza
ORDER BY Calendario.Data;
Questo è il codice SQL della query che nel database di esempio allegato a questa FAQ si chiama ConTabelleDerivate.In questa sintassi di codice SQL si mostra di fatto come usare delle sottoquery nella proposizione FROM. Le tre queries rendono esattamente lo stesso risultato, ma come si è detto la prima ha propblemi di performance e di trasportabilità. La terza è più performante della seconda, ma questo è vero solo con Access; il discorso cambia con database più professionali, infatti l'optimizer di questi ultimi trasforma automaticamnete la seconda query nella terza avendo così le stesse prestazioni. Download: |
