[MySQL] Estrapolare record finti/inisistenti

[max1974]

Ragazzi parliamo di db mysql ed ho questa query

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:

[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.

[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.

[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]

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

 

[max1974]

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]

é meglio se pubblichi tu gli esempi per popolare le tabelle tb_product e tb_product_dett
perché ci possono essere interpretazioni diverse ….

 

[max1974]

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);

 

[marino51]

INSERT INTO `tb_product 4 colonne 5 valori

 

[max1974]

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]

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]

Vedi l'allegato 6778

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]

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