SQL查询优化需求：合并medication_info中同药物的数量

要实现相同药物数量合并求和的需求，需要先对药物数据按处方和药物标识分组聚合，再进行拼接。以下是优化后的SQL语句：

WITH medication_agg AS (
    SELECT
        c.id_ord,
        CONCAT(REPLACE(lp.name, '(', ''), ' ', REPLACE(lp.dosage, ')', '')) AS drug_label,
        SUM(c.amount) AS total_amount
    FROM `changement` c
    LEFT JOIN `inventory` p ON c.id_prodoit = p.id
    LEFT JOIN `list_prodoit` lp ON p.list_prodoit = lp.id
    GROUP BY c.id_ord, drug_label
),
non_completed_agg AS (
    SELECT
        nc.ord_id,
        CONCAT(REPLACE(nlp.name, '(', ''), ' ', REPLACE(nlp.dosage, ')', '')) AS drug_label,
        SUM(nc.amount) AS total_amount
    FROM `noncompliant` nc
    LEFT JOIN `list_prodoit` nlp ON nc.list_prodoit = nlp.id
    GROUP BY nc.ord_id, drug_label
)
SELECT
    o.id,
    GROUP_CONCAT(CONCAT(ma.drug_label, ' : ', ma.total_amount) SEPARATOR '\n') AS medication_info,
    GROUP_CONCAT(CONCAT(nca.drug_label, ' : ', nca.total_amount) SEPARATOR '\n') AS non_completed_info,
    CONCAT(cl.fname, ' ', cl.name) AS client_name,
    cl.id AS client_id,
    u.name AS user_name,
    u.id AS user_id,
    o.id_pharm,
    DATE_FORMAT(o.created, '%d/%m/%Y') AS order_created,
    DATE_FORMAT(o.ord_date, '%d/%m/%Y') AS ord_date,
    DATE_FORMAT(o.next_date, '%d/%m/%Y') AS next_date,
    o.dure,
    o.ModifiedDate,
    o.complited
FROM `prescription` o
LEFT JOIN medication_agg ma ON o.id = ma.id_ord
LEFT JOIN non_completed_agg nca ON o.id = nca.ord_id
LEFT JOIN `client` cl ON o.id_client = cl.id
LEFT JOIN `users` u ON o.id_user = u.id
WHERE o.id_pharm = $pharm_id
GROUP BY o.id;

关键优化点： #

• 新增两个CTE（medication_agg和non_completed_agg），分别对已完成、未完成的药物数据按处方ID和**药物标识（名称+剂量）**分组，用SUM()合并相同药物的数量。
• 主查询中关联聚合后的结果，再用GROUP_CONCAT()拼接药物信息，确保每个药物只显示一次且数量为总和。
• 修正了原SQL中users表关联时的笔误（u.your textid改为u.id）。

内容的提问来源于stack exchange，提问作者Moh Sen