[MySQL] Estrapolare record finti/inisistenti

max1974

Utente Attivo
7 Mar 2013
107
0
16
Ragazzi parliamo di db mysql ed ho questa query
Codice:
SELECT MONTHNAME(Data) as Mese,YEAR(Data) as Anno, SUM(tb_product_dett.qta) AS QTA,tb_difetti.descrizione AS Difetto FROM `tb_product`
         LEFT JOIN tb_product_dett ON tb_product_dett.id_tb_product = tb_product.id
         LEFT JOIN tb_difetti ON tb_product_dett.id_difetti = tb_difetti.id
         GROUP BY Anno,Mese, Difetto
         ORDER BY Anno,Mese, Difetto
che mi estrapola questo dataset:
Codice:
[LIST]
[*][LIST]
[*]0: {Mese: "December", Anno: "2019", QTA: "1", Difetto: "Deformato"}
[*]1: {Mese: "December", Anno: "2019", QTA: "2", Difetto: "Sporco"}
[*]2: {Mese: "December", Anno: "2019", QTA: "1", Difetto: "Macchiato"}
[*]3: {Mese: "December", Anno: "2019", QTA: "1", Difetto: "Nessun Difetto"}
[*]4: {Mese: "November", Anno: "2019", QTA: "1", Difetto: "Macchiato"}
[/LIST]
[*]
 
[/LIST]

ma a me servirebbe anche sapere che per gli altri Difetti in quel Mese ed Anno (dove i record sono inesistenti) ossia le quantità era pari a zero.
Codice:
[LIST]
[*][LIST]
[*]5: {Mese: "November", Anno: "2019", QTA: "0", Difetto: "Deformato"}
[*]6: {Mese: "November", Anno: "2019", QTA: "0", Difetto: "Sporco"}
[*]7: {Mese: "November", Anno: "2019", QTA: "0", Difetto: "Nessun Difetto"}
[/LIST]
[/LIST]

perciò mi servirebbe il dataset completo.
ossia quando ci sta Mese/Anno di un difetto deve esserci per tutti i difetti apponendo la qta a Zero.

Codice:
[LIST]
[*]0: {Mese: "December", Anno: "2019", QTA: "1", Difetto: "Deformato"}
[*]1: {Mese: "December", Anno: "2019", QTA: "2", Difetto: "Sporco"}
[*]2: {Mese: "December", Anno: "2019", QTA: "1", Difetto: "Macchiato"}
[*]3: {Mese: "December", Anno: "2019", QTA: "1", Difetto: "Nessun Difetto"}
[*]4: {Mese: "November", Anno: "2019", QTA: "1", Difetto: "Macchiato"}
[*]5: {Mese: "November", Anno: "2019", QTA: "0", Difetto: "Deformato"}
[*]6: {Mese: "November", Anno: "2019", QTA: "0", Difetto: "Sporco"}
[*]7: {Mese: "November", Anno: "2019", QTA: "0", Difetto: "Nessun Difetto"}
[/LIST]

Se possibile non vorrei manipolarli dopo la query...
 

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
1575567648227.png

premesso che non vedo l'utilità di agganciare la tabella prodotti, perché i dati indicati sono tra loro correlati e non trovano legame con la predetta tabella prodotti,
ho quindi preparato un esempio che lavora con le altre 2 tabelle, ottenendo il risultato voluto
in particolare alla prima select "unisci" il risultato di una seconda select che esclude (where…) gli elementi presenti nella tabella di dettaglio

ma siccome credo sia un esercizio scolastico, ti lascio trovare la soluzione
 
  • Like
Reactions: max1974

max1974

Utente Attivo
7 Mar 2013
107
0
16
Vedi l'allegato 6777
premesso che non vedo l'utilità di agganciare la tabella prodotti, perché i dati indicati sono tra loro correlati e non trovano legame con la predetta tabella prodotti,
ho quindi preparato un esempio che lavora con le altre 2 tabelle, ottenendo il risultato voluto
in particolare alla prima select "unisci" il risultato di una seconda select che esclude (where…) gli elementi presenti nella tabella di dettaglio

ma siccome credo sia un esercizio scolastico, ti lascio trovare la soluzione

NON è una esercitazione,
Innanzitutto grazie, anche se la tua foto si vede a metà percui non è leggibile bene la prima parte di query se riusciresti a postarla tutta ti ringrazio.

Poi ti spiego subito l'utilità di agganciare la tabella "tb_product". eccola qui...
CREATE TABLE `tb_product` (
`id` int(11) NOT NULL,
`Num` int(11) DEFAULT '0',
`Data` datetime NOT NULL,
`id_del` int(11) DEFAULT '0'
)
1° Come vedi il campo DATA da dove estrapolo Mese ed Anno sono all'interno di questa tabella
2° Perche poi nel finale ci aggiungo "WHERE tb_product.id_del<>0" per escludere i record eliminati che non elimino fisicamente dal db.

questa invece la tabella tb_product_dett
CREATE TABLE `tb_product_dett` (
`id` int(11) NOT NULL,
`id_articoli` int(11) DEFAULT '0',
`qta` int(11) DEFAULT '0',
`id_um` int(11) DEFAULT '0',
`id_difetti` int(11) DEFAULT '0',
`id_colore` int(11) DEFAULT '0',
`id_count` int(11) DEFAULT '0',
`id_del` int(11) DEFAULT '0',
`id_tb_product` int(11) DEFAULT '0'
)
da dove estrapolo la qta ed il link ai difetti del prodotto.

questa la tabella dei difetti tb_difetti

CREATE TABLE `tb_difetti` (
`id` int(11) NOT NULL,
`codice` varchar(50) DEFAULT NULL,
`descrizione` varchar(100) DEFAULT NULL,
`notes` text,
`id_pref` int(11) DEFAULT '0',
`id_del` int(11) DEFAULT '0'
)

INSERT INTO `tb_difetti` (`id`, `codice`, `descrizione`, `notes`, `id_pref`, `id_del`) VALUES
(1, '01', 'Nessun Difetto', '', 1, 0),
(2, '02', 'Deformato', '', 0, 0),
(3, '03', 'Sporco', '', 0, 0),
(4, '04', 'Macchiato', '', 0, 0);


GRAZIE 1000
 

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
é meglio se pubblichi tu gli esempi per popolare le tabelle tb_product e tb_product_dett
perché ci possono essere interpretazioni diverse ….
 
  • Like
Reactions: max1974

max1974

Utente Attivo
7 Mar 2013
107
0
16
Ecco Il tutto

CREATE TABLE `tb_product` (
`id` int(11) NOT NULL,
`Num` int(11) DEFAULT '0',
`Data` datetime NOT NULL,
`id_del` int(11) DEFAULT '0',
)
INSERT INTO `tb_product` (`id`, `Num`, `Data`, `id_del`) VALUES
(1, 1, '2019-11-28 00:00:00', 0, 1),
(2, 2, '2019-12-05 00:00:00', 0, 1);

CREATE TABLE `tb_product_dett` (
`id` int(11) NOT NULL,
`id_articoli` int(11) DEFAULT '0',
`qta` int(11) DEFAULT '0',
`id_um` int(11) DEFAULT '0',
`id_difetti` int(11) DEFAULT '0',
`id_colore` int(11) DEFAULT '0',
`id_count` int(11) DEFAULT '0',
`id_del` int(11) DEFAULT '0',
`id_tb_product` int(11) DEFAULT '0'
)
INSERT INTO `tb_product_dett` (`id`, `id_articoli`, `qta`, `id_um`, `id_difetti`, `id_colore`, `id_count`, `id_del`, `id_tb_product`) VALUES
(128, 1876, 2, 1, 4, 1, 1, 0, 1),
(129, 1882, 1, 1, 2, 1, 1, 0, 2),
(130, 1908, 2, 1, 3, 1, 2, 0, 2),
(131, 1023, 1, 1, 4, 8, 3, 0, 2),
(132, 1029, 1, 1, 1, 8, 4, 0, 2);

CREATE TABLE `tb_difetti` (
`id` int(11) NOT NULL,
`codice` varchar(50) DEFAULT NULL,
`descrizione` varchar(100) DEFAULT NULL,
`id_pref` int(11) DEFAULT '0',
`id_del` int(11) DEFAULT '0'
)

INSERT INTO `tb_difetti` (`id`, `codice`, `descrizione`, `id_pref`, `id_del`) VALUES
(1, '01', 'Nessun Difetto', 1, 0),
(2, '02', 'Deformato', 0, 0),
(3, '03', 'Sporco', 0, 0),
(4, '04', 'Macchiato', 0, 0),
(5, '05', 'Altri Danni', 0, 0),
(6, '06', 'Crepati', 0, 0);
 

max1974

Utente Attivo
7 Mar 2013
107
0
16
INSERT INTO `tb_product 4 colonne 5 valori
SCUSA te lo avevo semplificato ......questo corretto 4 colonne
INSERT INTO `tb_product` (`id`, `Num`, `Data`, `id_del`) VALUES
(1, 1, '2019-11-28 00:00:00', 0),
(2, 2, '2019-12-05 00:00:00', 0);
 

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
1575585775565.png

SQL:
SELECT anno, mese, DIFETTO, SUM(QTA) FROM (
SELECT
  YEAR(p.data)   AS anno
, MONTH(p.data)  AS mese
, d.descrizione  AS DIFETTO
, SUM(pd.qta)    AS QTA
FROM @tb_product_dett pd
LEFT JOIN @tb_product p ON pd.id_tb_product = p.id
LEFT JOIN @tb_difetti d ON pd.id_tb_difetti = d.id
GROUP BY YEAR(p.data), MONTH(p.data), d.descrizione
UNION
SELECT
  YEAR(p.data)   AS anno
, MONTH(p.data)  AS mese
, d.descrizione  AS DIFETTO
, 0              AS QTA
FROM @tb_difetti d, @tb_product p
GROUP BY YEAR(p.data), MONTH(p.data), d.descrizione
) x
GROUP BY anno, mese, DIFETTO
ORDER BY anno, mese, DIFETTO

la "x" é necessaria, non è un errore
 

max1974

Utente Attivo
7 Mar 2013
107
0
16
Vedi l'allegato 6778
SQL:
SELECT anno, mese, DIFETTO, SUM(QTA) FROM (
SELECT
  YEAR(p.data)   AS anno
, MONTH(p.data)  AS mese
, d.descrizione  AS DIFETTO
, SUM(pd.qta)    AS QTA
FROM @tb_product_dett pd
LEFT JOIN @tb_product p ON pd.id_tb_product = p.id
LEFT JOIN @tb_difetti d ON pd.id_tb_difetti = d.id
GROUP BY YEAR(p.data), MONTH(p.data), d.descrizione
UNION
SELECT
  YEAR(p.data)   AS anno
, MONTH(p.data)  AS mese
, d.descrizione  AS DIFETTO
, 0              AS QTA
FROM @tb_difetti d, @tb_product p
GROUP BY YEAR(p.data), MONTH(p.data), d.descrizione
) x
GROUP BY anno, mese, DIFETTO
ORDER BY anno, mese, DIFETTO

la "x" é necessaria, non è un errore
GRAZIE 1000 FUNZIONA PERFETTAMENTE, ma fare copia incolla so capaci tutti......sto cercando di capire la logica....

STEP 1°.... in questa parte sarebbe la query che avevo realizzato io......ossia selezioni e sommi le qta dei record veramente presenti....
SELECT
YEAR(p.data) AS anno
, MONTH(p.data) AS mese
, d.descrizione AS DIFETTO
, SUM(pd.qta) AS QTA
FROM @tb_product_dett pd
LEFT JOIN @tb_product p ON pd.id_tb_product = p.id
LEFT JOIN @tb_difetti d ON pd.id_tb_difetti = d.id
GROUP BY YEAR(p.data), MONTH(p.data), d.descrizione

STEP 2° passaggio in questa seconda parte crei tutti record fantasmi (ossia con quantita a zero)

SELECT
YEAR(p.data) AS anno
, MONTH(p.data) AS mese
, d.descrizione AS DIFETTO
, 0 AS QTA
FROM @tb_difetti d, @tb_product p
GROUP BY YEAR(p.data), MONTH(p.data), d.descrizione

STEP 3.....con UNION unisci le 2 query accorando i record dell'una e dell'altra query.
STEP 4.....dopo di che raggruppando di nuovo e sommando le qta con quelle che stanno a Zero avresti tutti i record sia reali che quelli fantasma...
SELECT anno, mese, DIFETTO, SUM(QTA) FROM(.....)
GROUP BY anno, mese, DIFETTO
ORDER BY anno, mese, DIFETTO

Spero di aver capito la logica :) :) :) Di nuovo Grazie
 

marino51

Utente Attivo
28 Feb 2013
3.039
192
63
Lombardia
Spero di aver capito la logica
si é proprio così
STEP 1°.... in questa parte sarebbe la query che avevo realizzato io
si, con la differenza che ho usato tb_product_dett come tabella principale, considerande le altre una sorta di "anagrafiche"
e così vengono "pulite" anche le left join

la union "vuole" le colonne uguali per nome e tipo nelle select unite (nel caso volessi aggiungere altre colonne)

da ultimo, trovi le "@" di fronte ai nomi delle tabelle,
servono a definire tabelle temporanee in sql server, evitando di creare tabelle effettive, per fare le prove
(toglile)

ps, aggiungi "AS", mi é rimasto nella penna
SELECT anno, mese, DIFETTO, SUM(QTA) AS QTA FROM (

saluti
 
Ultima modifica:
Discussioni simili
Autore Titolo Forum Risposte Data
A Mysql MySQL 0
F Ricreare struttura php+mysql su Xampp Apache 0
M Array associativi php su 2 campi mysql PHP 10
Z Controllo giorni MYSQL PHP 0
L php mysql non salva solo id PHP 21
L php mysql cerca e visualizza pagina PHP 0
L Mysql: Nascondere le pagine dopo una ricerca PHP 1
R Aggiornare record mysql con Ajax, jQuery e php Ajax 2
S problema con recupero dati tabella mysql PHP 2
E Progressbar estrazione dati da tabella mySQL Ajax 9
Z MySql injection PHP PHP 1
D controllare valore in tabella mysql PHP 0
A pulsante di update campo mysql con javascript Javascript 2
R Tutto su utf-8 ma ancora problemi con i caratteri speciali in mysql MySQL 1
T differenza fra mysql xampp e un mysql server Database 0
R Importazione csv su mysql tramite array PHP 2
Z Problema con INT MySQL PHP 1
Z Problema database MySQL con XAMPP PHP 0
D problema php mysql PHP 1
D problema php mysql PHP 1
N Server mysql non raggiungibile da connessione esterna MySQL 1
B Crea pdf da tabella mysql "ultima riga modificata" MySQL 4
D evitare di inserirre duplicati in mysql PHP 4
L salvare codice html in mysql PHP 3
L Google chart php mysql PHP 2
S Gestire scelta dropdown con dati da Mysql PHP 2
K cron job mysql PHP 3
elpirata Query per leggere dati da una tabella mysql e mostrarli a video in base a parametri passati tramite GET PHP 5
R Errore UPDATE tabella mysql PHP 1
R Caricamento immagine su cartella remota + mysql PHP 3
L Aiuto per programma web php/mySQL PHP 2
S Problema esportazione tabelle Mysql in Excel PHP 0
S Cancellare una riga MYSQL PHP 1
L Ricerca valore mysql e incremento PHP 73
G database mysql contengono informazioni ? MySQL 0
G Testo in mysql format 3 MySQL 0
S Problemi delle funzioni eliminate con PHP e MySQL PHP 4
G inserire dati automaticamente in mysql PHP 0
A Importare array in mysql PHP 1
C Aiuto compiuto scuola PHP/MySQL PHP 2
FabioJ [mysql] importazione csv o json o excel Database 0
L scaricare MySQL MySQL 2
G Rendere dati di una tabella mysql link PHP 22
G eseguire calcoli matematici su risultati 2 tabelle mysql PHP 4
S filtrare database mysql MySQL 3
G Problema caricamento tabelle MySql da PhP PHP 0
L update tabelle in php mysql [risolto] PHP 6
M Grafico dinamico mysql chartjs Ajax 5
M Esportare tabelle mdb con relazioni in mysql MS Access 0
F Creare un set di date a seconda del frazionamento scelto da inserire in MySQL PHP 6

Discussioni simili