Query a campi incrociati | SQL & MySQL

Topic: Pubblico - Composto da 6 Posts di 2 Utenti.

02 Agosto, 2010 13:50	#1
manlio Utente Registrato: Aug, 2010 Posts: 4 Offline	Salve a tutti provengo da Access e ho necessita' di fare una query incrociata in mysql. In access il tutto funzionava tranquillamente con l'istruzione TRASFORM. In mysql ho visto che bisogna costruirla diversamente. Ecco il mio problema. Ho due tabelle tabella clienti campo codice tabella visite campo codice campo data campo esito (A,B,C) e vorrei fare una query con questo risultato 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 cliente 01 A A B B C cliente 02 A c c B cliente 03 C C A A B query mysql select clienti.codice, if(day(visite.data)=1,visite.esito,' ') as '1', [altri giorni da 2 a 30], if(day(visite.data)=31,visite.esito,' ') as '31' FROM clienti left join visite on clienti.codice=visite.codice group by clienti.codice Il risulato che mi restituisce questa query è quasi corretto, nel senso che mi visualizza solo l'esito dell'ultima visita (nel caso del cliente 01, ad esempio, mette solo C in corrispondenza del giorno 20. Chi sa aiutarmi?

02 Agosto, 2010 13:50

manlio
Utente

Registrato: Aug, 2010
Posts: 4
Offline

Salve a tutti

provengo da Access e ho necessita' di fare una query incrociata in mysql.
In access il tutto funzionava tranquillamente con l'istruzione TRASFORM. In mysql ho visto che bisogna costruirla diversamente.
Ecco il mio problema.

Ho due tabelle

tabella clienti
campo codice

tabella visite
campo codice
campo data
campo esito (A,B,C)

e vorrei fare una query con questo risultato

              1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
cliente 01   A       A                    B                    B              C
cliente 02 A               c                          c     B
cliente 03                            C                 C           A     A         B

query mysql

select clienti.codice, if(day(visite.data)=1,visite.esito,' ') as '1', [altri giorni da 2 a 30], if(day(visite.data)=31,visite.esito,' ') as '31'
FROM clienti left join visite on clienti.codice=visite.codice group by clienti.codice

Il risulato che mi restituisce questa query è quasi corretto, nel senso che mi visualizza solo l'esito dell'ultima visita
(nel caso del cliente 01, ad esempio, mette solo C in corrispondenza del giorno 20.

Chi sa aiutarmi?

03 Agosto, 2010 08:24	#2
darasc Moderatore Registrato: Jul, 2010 Posts: 64 Offline	Ciao manlio, prova con questa query: select clienti.codice, ifnull((select v.esito from visite v where day(v.data)=1 and v.codice=clienti.codice),'') as '1', [altri giorni da 2 a 30] ifnull((select v.esito from visite v where day(v.data)=31 and v.codice=clienti.codice),'') as '31' from clienti left outer join visite on clienti.codice=visite.codice group by clienti.codice è una soluzione un pò macchinosa ma efficace ;) Buon lavoro.

03 Agosto, 2010 08:24

darasc
Moderatore

Registrato: Jul, 2010
Posts: 64
Offline

Ciao manlio,

prova con questa query:

select clienti.codice,

ifnull((select v.esito from visite v where day(v.data)=1 and v.codice=clienti.codice),'') as '1',

[altri giorni da 2 a 30]

ifnull((select v.esito from visite v where day(v.data)=31 and v.codice=clienti.codice),'') as '31'

from clienti

left outer join visite on clienti.codice=visite.codice

group by clienti.codice

è una soluzione un pò macchinosa ma efficace ;)

Buon lavoro.

03 Agosto, 2010 08:44	#3
manlio Utente Registrato: Aug, 2010 Posts: 4 Offline	grazie darasc sei un grande! ho provato ed effettivamente funziona. La mia query è un po' più complicata di quella dell'esempio. ho riadattato un po' e funziona alla grande. Poichè sono nuovo dell'sql (la mia esperienza in access non mi fa un esperto di linguaggio sql) mi spiegheresti il principio dell'istruzione? e quella clausola left outer? Grazie ancora

03 Agosto, 2010 08:44

manlio
Utente

Registrato: Aug, 2010
Posts: 4
Offline

grazie darasc

sei un grande! ho provato ed effettivamente funziona. La mia query è un po' più complicata di quella dell'esempio. ho riadattato un po' e funziona alla grande.

Poichè sono nuovo dell'sql (la mia esperienza in access non mi fa un esperto di linguaggio sql) mi spiegheresti il principio dell'istruzione? e quella clausola left outer?

Grazie ancora

03 Agosto, 2010 09:17	#4
manlio Utente Registrato: Aug, 2010 Posts: 4 Offline	un'altra cosetta. questa è la mia query completa: select mtdett_clientizonatab.codice, mtdett_clientizonatab.ditta, mtdett_clientizonatab.codzona, mtdett_totvisitetab.totv, mtdett_totvisitetab.lastv, mtdett_clientifatttab.totfatt, mtdett_lastvtab.lastvabs, ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=1 and v.codcliente=mtdett_clientizonatab.codice),'') as '1', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=2 and v.codcliente=mtdett_clientizonatab.codice),'') as '2', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=3 and v.codcliente=mtdett_clientizonatab.codice),'') as '3', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=4 and v.codcliente=mtdett_clientizonatab.codice),'') as '4', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=5 and v.codcliente=mtdett_clientizonatab.codice),'') as '5', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=6 and v.codcliente=mtdett_clientizonatab.codice),'') as '6', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=7 and v.codcliente=mtdett_clientizonatab.codice),'') as '7', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=8 and v.codcliente=mtdett_clientizonatab.codice),'') as '8', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=9 and v.codcliente=mtdett_clientizonatab.codice),'') as '9', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=10 and v.codcliente=mtdett_clientizonatab.codice),'') as '10', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=11 and v.codcliente=mtdett_clientizonatab.codice),'') as '11', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=12 and v.codcliente=mtdett_clientizonatab.codice),'') as '12', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=13 and v.codcliente=mtdett_clientizonatab.codice),'') as '13', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=14 and v.codcliente=mtdett_clientizonatab.codice),'') as '14', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=15 and v.codcliente=mtdett_clientizonatab.codice),'') as '15', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=16 and v.codcliente=mtdett_clientizonatab.codice),'') as '16', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=17 and v.codcliente=mtdett_clientizonatab.codice),'') as '17', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=18 and v.codcliente=mtdett_clientizonatab.codice),'') as '18', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=19 and v.codcliente=mtdett_clientizonatab.codice),'') as '19', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=20 and v.codcliente=mtdett_clientizonatab.codice),'') as '20', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=21 and v.codcliente=mtdett_clientizonatab.codice),'') as '21', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=22 and v.codcliente=mtdett_clientizonatab.codice),'') as '22', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=23 and v.codcliente=mtdett_clientizonatab.codice),'') as '23', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=24 and v.codcliente=mtdett_clientizonatab.codice),'') as '24', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=25 and v.codcliente=mtdett_clientizonatab.codice),'') as '25', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=26 and v.codcliente=mtdett_clientizonatab.codice),'') as '26', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=27 and v.codcliente=mtdett_clientizonatab.codice),'') as '27', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=28 and v.codcliente=mtdett_clientizonatab.codice),'') as '28', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=29 and v.codcliente=mtdett_clientizonatab.codice),'') as '29', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=30 and v.codcliente=mtdett_clientizonatab.codice),'') as '30', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=31 and v.codcliente=mtdett_clientizonatab.codice),'') as '31' from (((mtdett_clientizonatab left join mtdett_totvisitetab on mtdett_clientizonatab.codice = mtdett_totvisitetab.codice) left join mtdett_clientifatttab on mtdett_clientizonatab.codice = mtdett_clientifatttab.codcliente) left outer join mtdett_visitezonatab on mtdett_clientizonatab.codice = mtdett_visitezonatab.codcliente) left join mtdett_lastvtab on mtdett_clientizonatab.codice = mtdett_lastvtab.codice group by mtdett_clientizonatab.codice, mtdett_clientizonatab.ditta, mtdett_clientizonatab.codzona, mtdett_totvisitetab.totv, mtdett_totvisitetab.lastv, mtdett_clientifatttab.totfatt, mtdett_lastvtab.lastvabs order by mtdett_clientifatttab.totfatt desc il problema è questo. la tabella mtdett_visitezonatab contiene le visite fatte sui clienti. quando in un giorno ci sono più visite su un cliente (quindi due righe con codice e data identiche) la quesry mi restituisce questo errore #1242 - Subquery returns more than 1 row Ho risolto parzialmente facendo estrarre la tabella mtdett_visitezonatab con un group by data, codice ma il problema è che poi non m i quadrano le visite totali. Cioe': cliente 01 visitato il 2 3 4 5 6(2 volte) 9 cliente 02 visitato il 5 7 9 10(2 volte) 15 18 il totale delle visite è 14 (non 12 come le visite uniche prese con group by) C'e' un modo per far si che le righe multiple siano prese solo una volta (ad esempio con una funzione max, mi verrebbe da pensare)? Grazie ancora

03 Agosto, 2010 09:17

manlio
Utente

Registrato: Aug, 2010
Posts: 4
Offline

un'altra cosetta. questa è la mia query completa:

select mtdett_clientizonatab.codice, mtdett_clientizonatab.ditta, mtdett_clientizonatab.codzona, mtdett_totvisitetab.totv, mtdett_totvisitetab.lastv, mtdett_clientifatttab.totfatt, mtdett_lastvtab.lastvabs, ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=1 and v.codcliente=mtdett_clientizonatab.codice),'') as '1', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=2 and v.codcliente=mtdett_clientizonatab.codice),'') as '2', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=3 and v.codcliente=mtdett_clientizonatab.codice),'') as '3', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=4 and v.codcliente=mtdett_clientizonatab.codice),'') as '4', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=5 and v.codcliente=mtdett_clientizonatab.codice),'') as '5', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=6 and v.codcliente=mtdett_clientizonatab.codice),'') as '6', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=7 and v.codcliente=mtdett_clientizonatab.codice),'') as '7', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=8 and v.codcliente=mtdett_clientizonatab.codice),'') as '8', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=9 and v.codcliente=mtdett_clientizonatab.codice),'') as '9', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=10 and v.codcliente=mtdett_clientizonatab.codice),'') as '10', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=11 and v.codcliente=mtdett_clientizonatab.codice),'') as '11', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=12 and v.codcliente=mtdett_clientizonatab.codice),'') as '12', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=13 and v.codcliente=mtdett_clientizonatab.codice),'') as '13', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=14 and v.codcliente=mtdett_clientizonatab.codice),'') as '14', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=15 and v.codcliente=mtdett_clientizonatab.codice),'') as '15', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=16 and v.codcliente=mtdett_clientizonatab.codice),'') as '16', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=17 and v.codcliente=mtdett_clientizonatab.codice),'') as '17', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=18 and v.codcliente=mtdett_clientizonatab.codice),'') as '18', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=19 and v.codcliente=mtdett_clientizonatab.codice),'') as '19', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=20 and v.codcliente=mtdett_clientizonatab.codice),'') as '20', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=21 and v.codcliente=mtdett_clientizonatab.codice),'') as '21', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=22 and v.codcliente=mtdett_clientizonatab.codice),'') as '22', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=23 and v.codcliente=mtdett_clientizonatab.codice),'') as '23', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=24 and v.codcliente=mtdett_clientizonatab.codice),'') as '24', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=25 and v.codcliente=mtdett_clientizonatab.codice),'') as '25', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=26 and v.codcliente=mtdett_clientizonatab.codice),'') as '26', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=27 and v.codcliente=mtdett_clientizonatab.codice),'') as '27', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=28 and v.codcliente=mtdett_clientizonatab.codice),'') as '28', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=29 and v.codcliente=mtdett_clientizonatab.codice),'') as '29', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=30 and v.codcliente=mtdett_clientizonatab.codice),'') as '30', ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=31 and v.codcliente=mtdett_clientizonatab.codice),'') as '31' from (((mtdett_clientizonatab left join mtdett_totvisitetab on mtdett_clientizonatab.codice = mtdett_totvisitetab.codice) left join mtdett_clientifatttab on mtdett_clientizonatab.codice = mtdett_clientifatttab.codcliente) left outer join mtdett_visitezonatab on mtdett_clientizonatab.codice = mtdett_visitezonatab.codcliente) left join mtdett_lastvtab on mtdett_clientizonatab.codice = mtdett_lastvtab.codice group by mtdett_clientizonatab.codice, mtdett_clientizonatab.ditta, mtdett_clientizonatab.codzona, mtdett_totvisitetab.totv, mtdett_totvisitetab.lastv, mtdett_clientifatttab.totfatt, mtdett_lastvtab.lastvabs order by mtdett_clientifatttab.totfatt desc

il problema è questo. la tabella mtdett_visitezonatab contiene le visite fatte sui clienti. quando in un giorno ci sono più visite su un cliente (quindi due righe con codice e data identiche) la quesry mi restituisce questo errore

#1242 - Subquery returns more than 1 row

Ho risolto parzialmente facendo estrarre la tabella mtdett_visitezonatab con un group by data, codice ma il problema è che poi non m i quadrano le visite totali. Cioe':

cliente 01 visitato il 2 3 4 5 6(2 volte) 9

cliente 02 visitato il 5 7 9 10(2 volte) 15 18

il totale delle visite è 14 (non 12 come le visite uniche prese con group by)

C'e' un modo per far si che le righe multiple siano prese solo una volta (ad esempio con una funzione max, mi verrebbe da pensare)?

Grazie ancora

03 Agosto, 2010 10:25	#5
manlio Utente Registrato: Aug, 2010 Posts: 4 Offline	risolto ho messo il group direttamente dentro la select ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=29 and v.codcliente=mtdett_clientizonatab.codice group by data,codcliente),'') as '29' e funziona. Pero' ti chiederei lo stesso di spiegarmi la logica della tua funzione. in particolare il passaggio - from mtdett_visitezonatab v - Grazie

03 Agosto, 2010 10:25

manlio
Utente

Registrato: Aug, 2010
Posts: 4
Offline

risolto

ho messo il group direttamente dentro la select

ifnull((select left(v.esito,2) from mtdett_visitezonatab v where day(v.data)=29 and v.codcliente=mtdett_clientizonatab.codice group by data,codcliente),'') as '29'

e funziona.

Pero' ti chiederei lo stesso di spiegarmi la logica della tua funzione. in particolare il passaggio - from mtdett_visitezonatab v -

Grazie

03 Agosto, 2010 11:36	#6
darasc Moderatore Registrato: Jul, 2010 Posts: 64 Offline	- from mtdett_visitezonatab v - significa che assegno alla tabella mtdett_visitezonatab un alias (nel nostro caso 'v'). Un alias mi permette di rappresentare la tabella con un nome a mia scelta. Da quel punto in poi per riferirmi a tale tabella utilizzerò l'alias. Nel caso di una subquery può essere utile se utilizzo la stessa tabella nella query principale e nella subquery. La logica della query si basa sul fatto che nella clausola SELECT è possibile richiamare delle subquery. Questo mi permette di estrarre dei dati in modo indipendente dalla query principale. Nel nostro caso verifico in ogni subquery se è presente un esito alla data che mi interessa e che sia riferito al cliente che sto prendendo in quel momento in esame nella query principale: ifnull((select v.esito from visite v where day(v.data)=1 and v.codice=clienti.codice),'') as '1', come vedi nella subquery utilizzo la tabella 'clienti' che viene richiamata nella clausola FROM della query principale. Ciò mi permette di avere un legame tra i dati estratti nella subquery e quelli estratti nella query principale. In pratica la query principale estrae tutte le informazioni relative ai clienti (crea una riga per cliente), mentre la subquery associa ad ognuno di essi il giusto esito per la data presa in considerazione effettuando una ricerca in tutta la tabella visite senza influire sulla query principale.

03 Agosto, 2010 11:36

darasc
Moderatore

Registrato: Jul, 2010
Posts: 64
Offline

- from mtdett_visitezonatab v - significa che assegno alla tabella mtdett_visitezonatab un alias (nel nostro caso 'v').

Un alias mi permette di rappresentare la tabella con un nome a mia scelta. Da quel punto in poi per riferirmi a tale tabella utilizzerò l'alias.

Nel caso di una subquery può essere utile se utilizzo la stessa tabella nella query principale e nella subquery.

La logica della query si basa sul fatto che nella clausola SELECT è possibile richiamare delle subquery. Questo mi permette di estrarre dei dati in modo indipendente dalla query principale. Nel nostro caso verifico in ogni subquery se è presente un esito alla data che mi interessa e che sia riferito al cliente che sto prendendo in quel momento in esame nella query principale:

ifnull((select v.esito from visite v where day(v.data)=1 and v.codice=clienti.codice),'') as '1',

come vedi nella subquery utilizzo la tabella 'clienti' che viene richiamata nella clausola FROM della query principale.

Ciò mi permette di avere un legame tra i dati estratti nella subquery e quelli estratti nella query principale.

In pratica la query principale estrae tutte le informazioni relative ai clienti (crea una riga per cliente), mentre la subquery associa ad ognuno di essi il giusto esito per la data presa in considerazione effettuando una ricerca in tutta la tabella visite senza influire sulla query principale.

Condividi su:

Loggati o Registrati per replicare