dans ma table 'lignes de ticket', j'ai une colonne qui indique qu'un
article a été offert ou non (det_ticket.pforce = 2 si oui, 0 sinon)
Je voudrais faire une requète qui m'affiche
l'article, la quantité vendue, la quantité offerte.
Voici un exemple de ce que j'ai commencé à faire.
select det_ticket.id_art, libelle, qte, pxvttc, num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where det_ticket.pforce = 2 and datfct >= '20100111' ;
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Pierre BOUSQUET
soit avec un équivalent WD à 'CASE WHEN'
select det_ticket.id_art, libelle , CASE WHEN det_ticket.pforce = 0 THEN qte ELSE 0 END as qte , CASE WHEN det_ticket.pforce = 2 THEN qte ELSE 0 END as offert , pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where datfct >= '20100111'
soit avec UNION
select det_ticket.id_art, libelle, SUM(qte), SUM(offert) as offert ... from ( select det_ticket.id_art, libelle, 0 as qte, qte as offert, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 2 and datfct >= '20100111' UNION select det_ticket.id_art, libelle, qte, 0 as offert, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 0 and datfct >= '20100111' ) group by det_ticket.id_art, libelle...
Jacques Trepp avait soumis l'idée :
select det_ticket.id_art, libelle, qte, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 2 and datfct >= '20100111'
soit avec un équivalent WD à 'CASE WHEN'
select det_ticket.id_art, libelle
, CASE WHEN det_ticket.pforce = 0 THEN qte ELSE 0 END as qte
, CASE WHEN det_ticket.pforce = 2 THEN qte ELSE 0 END as offert
, pxvttc, num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where datfct >= '20100111'
soit avec UNION
select det_ticket.id_art, libelle, SUM(qte), SUM(offert) as offert ...
from (
select det_ticket.id_art, libelle, 0 as qte, qte as offert, pxvttc,
num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where det_ticket.pforce = 2 and datfct >= '20100111'
UNION
select det_ticket.id_art, libelle, qte, 0 as offert, pxvttc,
num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where det_ticket.pforce = 0 and datfct >= '20100111'
)
group by det_ticket.id_art, libelle...
Jacques Trepp avait soumis l'idée :
select det_ticket.id_art, libelle, qte, pxvttc, num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where det_ticket.pforce = 2 and datfct >= '20100111'
select det_ticket.id_art, libelle , CASE WHEN det_ticket.pforce = 0 THEN qte ELSE 0 END as qte , CASE WHEN det_ticket.pforce = 2 THEN qte ELSE 0 END as offert , pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where datfct >= '20100111'
soit avec UNION
select det_ticket.id_art, libelle, SUM(qte), SUM(offert) as offert ... from ( select det_ticket.id_art, libelle, 0 as qte, qte as offert, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 2 and datfct >= '20100111' UNION select det_ticket.id_art, libelle, qte, 0 as offert, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 0 and datfct >= '20100111' ) group by det_ticket.id_art, libelle...
Jacques Trepp avait soumis l'idée :
select det_ticket.id_art, libelle, qte, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 2 and datfct >= '20100111'
Jacques Trepp
Pierre BOUSQUET vient de nous annoncer :
soit avec un équivalent WD à 'CASE WHEN'
select det_ticket.id_art, libelle , CASE WHEN det_ticket.pforce = 0 THEN qte ELSE 0 END as qte , CASE WHEN det_ticket.pforce = 2 THEN qte ELSE 0 END as offert , pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where datfct >= '20100111'
soit avec UNION
select det_ticket.id_art, libelle, SUM(qte), SUM(offert) as offert ... from ( select det_ticket.id_art, libelle, 0 as qte, qte as offert, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 2 and datfct >= '20100111' UNION select det_ticket.id_art, libelle, qte, 0 as offert, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 0 and datfct >= '20100111' ) group by det_ticket.id_art, libelle...
Jacques Trepp avait soumis l'idée :
select det_ticket.id_art, libelle, qte, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 2 and datfct >= '20100111'
Merci beaucoup Pierre ! :) je connaissais le Case..When sur postgresql, mais pas sur mysql. c'est parfait.
Pierre BOUSQUET vient de nous annoncer :
soit avec un équivalent WD à 'CASE WHEN'
select det_ticket.id_art, libelle
, CASE WHEN det_ticket.pforce = 0 THEN qte ELSE 0 END as qte
, CASE WHEN det_ticket.pforce = 2 THEN qte ELSE 0 END as offert
, pxvttc, num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where datfct >= '20100111'
soit avec UNION
select det_ticket.id_art, libelle, SUM(qte), SUM(offert) as offert ...
from (
select det_ticket.id_art, libelle, 0 as qte, qte as offert, pxvttc,
num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where det_ticket.pforce = 2 and datfct >= '20100111'
UNION
select det_ticket.id_art, libelle, qte, 0 as offert, pxvttc, num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where det_ticket.pforce = 0 and datfct >= '20100111'
)
group by det_ticket.id_art, libelle...
Jacques Trepp avait soumis l'idée :
select det_ticket.id_art, libelle, qte, pxvttc, num_poste,
datfct, hrfct from det_ticket
inner join ticket on det_ticket.id_ticket = ticket.id_ticket
inner join article on det_ticket.id_art = article.id_art
where det_ticket.pforce = 2 and datfct >= '20100111'
Merci beaucoup Pierre ! :)
je connaissais le Case..When sur postgresql, mais pas sur mysql.
c'est parfait.
select det_ticket.id_art, libelle , CASE WHEN det_ticket.pforce = 0 THEN qte ELSE 0 END as qte , CASE WHEN det_ticket.pforce = 2 THEN qte ELSE 0 END as offert , pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where datfct >= '20100111'
soit avec UNION
select det_ticket.id_art, libelle, SUM(qte), SUM(offert) as offert ... from ( select det_ticket.id_art, libelle, 0 as qte, qte as offert, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 2 and datfct >= '20100111' UNION select det_ticket.id_art, libelle, qte, 0 as offert, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 0 and datfct >= '20100111' ) group by det_ticket.id_art, libelle...
Jacques Trepp avait soumis l'idée :
select det_ticket.id_art, libelle, qte, pxvttc, num_poste, datfct, hrfct from det_ticket inner join ticket on det_ticket.id_ticket = ticket.id_ticket inner join article on det_ticket.id_art = article.id_art where det_ticket.pforce = 2 and datfct >= '20100111'
Merci beaucoup Pierre ! :) je connaissais le Case..When sur postgresql, mais pas sur mysql. c'est parfait.
Jerome PAULIN
Jacques Trepp a écrit :
Merci beaucoup Pierre ! :) je connaissais le Case..When sur postgresql, mais pas sur mysql. c'est parfait.
C'est dans MySQL depuis les versions 5 il me semble. Sinon tu aurais pu t'en sortir avec des if() imbriqués.
gg
Jacques Trepp a écrit :
Merci beaucoup Pierre ! :)
je connaissais le Case..When sur postgresql, mais pas sur mysql.
c'est parfait.
C'est dans MySQL depuis les versions 5 il me semble.
Sinon tu aurais pu t'en sortir avec des if() imbriqués.
