[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
2.918
164
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
2.918
164
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
2.918
164
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
2.918
164
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: