Bonjour,
Je cherche à concaténer le px_com des lignes qui ont un nucdli=nu_docu.
Affecter la somme dans le champ px_com dans la ligne qui a un nu_doc et effacer la ligne qui a le nucdli correspondant.
Pour expliciter ma requête, voici le jeu de données suivant :
Code :
+-|id_prod |ty_doc|nu_doc|nucdli|px_com|id_prod|ty_doc |nu_doc | nucdli |px_com
+---------+--------+--------+--------+--------+---------+--------+--------+-----
| A | BE |13604 | NULL | 4 | E | CDC | 13661 | 13604 | 3 | |
| A | BE |13604 | NULL | 4 | B | CDC | 13361 | 13604 | 2 | |
| A | BE |13604 | NULL | 4 | E | CDT | 13360 | 13604 | 1 | |
| C | CDA |13604 | NULL | 3 | E | CDT | 13360 | 13604 | 1 | | |
| C | CDA |13604 | NULL | 3 | E | CDC | 13661 | 13604 | 3 | |
| C | CDA |13604 | NULL | 3 | B | CDC | 13361 | 13604 | 2 | |
| D | CDA |13604 | NULL | 3 | E | CDC | 13661 | 13604 | 3 | |
| D | CDA |13604 | NULL | 3 | B | CDC | 13361 | 13604 | 2 | |
| D | CDA |13604 | NULL | 3 | E | CDT | 13360 | 13604 | 1 | |
| E | FA | 444 | NULL | 1 | NULL | NULL | NULL | NULL | NULL | |
| E | BE | 555 | NULL | 1 | NULL | NULL | NULL | NULL | NULL | |
| E | CDA |13604 | NULL | 2 | E | CDC | 13661 | 13604 | 3 | |
| E | CDA |13604 | NULL | 2 | B | CDC | 13361 | 13604 | 2 | |
| E | CDA |13604 | NULL | 2 | E | CDT | 13360 | 13604 | 1 | |
+---------+--------+--------+--------+--------+---------+--------+------
Voici le résultat attendu :
Code :
+-|id_prod |ty_doc|nu_doc|nucdli|px_com|id_prod|ty_doc |nu_doc | nucdli |px_com
+---------+--------+--------+--------+--------+---------+--------+--------+-----
| A | BE |13604 | NULL | 4 |
| A | BE |13604 | NULL | 4 | B | CDC | 13361 | 13604 | 2 | |
| A | BE |13604 | NULL | 4 |
| C | CDA |13604 | NULL | 3 | |
| C | CDA |13604 | NULL | 3 |
| C | CDA |13604 | NULL | 3 | B | CDC | 13361 | 13604 | 2 | |
| D | CDA |13604 | NULL | 3 |
| D | CDA |13604 | NULL | 3 | B | CDC | 13361 | 13604 | 2 | |
| D | CDA |13604 | NULL | 3 | | |
| E | FA | 444 | NULL | 1 | NULL | NULL | NULL | NULL | NULL | |
| E | BE | 555 | NULL | 1 | NULL | NULL | NULL | NULL | NULL | |
| E | CDA |13604 | NULL | 12 | |
| E | CDA |13604 | NULL | 6 | B | CDC | 13361 | 13604 | 2 | |
| E | FA |13604 | NULL | 6 | |
+---------+--------+--------+--------+--------+---------+--------+------
Enfin voici ma requête :
Sauriez vous pourquoi je perds des lignes ?
C'est à cause du Group BY ?
SELECT id_prod,
ty_doc,
nu_doc,
nucdli,
Sum(px_com)
FROM (SELECT
z.id_prod,
CASE z.cde_ter
WHEN 0 THEN z.ty_doc
ELSE 'CDA'
END AS ty_doc,
CASE z.cde_ter
WHEN 1 THEN NULL
ELSE z.nucdli
END AS nucdli,
z.px_com,
CASE z.cde_ter
WHEN 1 THEN z.nucdli
ELSE z.nu_doc
END AS nu_doc
FROM
(SELECT *,
CASE
WHEN EXISTS (SELECT 1
FROM test.test a
WHERE a.id_prod = b.id_prod
AND b.nucdli = a.nu_doc
AND a.ty_doc = 'CDA') THEN 1
ELSE 0
END AS cde_ter
FROM test.test b) AS z) AS e
GROUP BY
id_prod,
ty_doc,
nu_doc,
nucdli
La requête groupe sur les produits, et les produits similaires disparaissent.