mysql - mysql group_concat用于左右表

我正在使用group_concat函数来汇总产品的一些移动，这些移动已经通过(stockout)用于右表left join。 MySQL代码如下-

SELECT
    p.serialno AS SISN,
    p.in_quantity AS INQTY,
    SUM(IFNULL(s.out_quantity, '0')) AS OUTQTY,
        GROUP_CONCAT(
            CONCAT(
                COALESCE(IFNULL(s.out_quantity,'0'), ''),'|'
            )
        ) details
FROM stockin p LEFT JOIN stockout s
    ON p.serialno = s.serialno
    WHERE p.productid = 'TF00123'
GROUP BY p.stockin_id, p.serialno, s.serialno

输出如下

+------+-------+--------+--------------------------------+
| SISN | INQTY | OUTQTY | details                        |
+------+-------+--------+--------------------------------+
| AAA1 |   500 |    740 | 300|,100|,50|,50|,20|,20|,200| |
| AAA2 |   500 |      0 | 0|                             |
| AAA3 |     1 |      3 | 1|,1|,1|                       |
| AAA3 |     1 |      3 | 1|,1|,1|                       |
| AAA1 |   200 |    740 | 300|,100|,50|,50|,20|,20|,200| |
| AAA3 |     1 |      3 | 1|,1|,1|                       |
| AAA1 |   100 |    740 | 300|,100|,50|,50|,20|,20|,200| |
+------+-------+--------+--------------------------------+
7 rows in set (0.00 sec)

现在，如果p.serialno是相同的，我想在另一列中group_concat保留详细信息，例如详细信息。
如果我手动执行，请检查输出-

+------+-------+------------------+--------+--------------------------------+
| SISN | INQTY | details          | OUTQTY | details                        |
+------+-------+------------------+--------+--------------------------------+
| AAA1 |   800 | 500|,200|,100|   |    740 | 300|,100|,50|,50|,20|,20|,200| |
| AAA2 |   500 | 0|               |      0 | 0|                             |
| AAA3 |     3 | 1|,1|,1|         |      3 | 1|,1|,1|                       |
+------+-------+------------------+--------+--------------------------------+
3 rows in set (0.00 sec)

最佳答案

只需使用

 GROUP_CONCAT(
            CONCAT(
                COALESCE(IFNULL(p.out_quantity,'0'), ''),'|'
            )
        ) details1

像这样

SELECT
    p.serialno AS SISN,
    p.in_quantity AS INQTY,
    GROUP_CONCAT(
            CONCAT(
                COALESCE(IFNULL(p.out_quantity,'0'), ''),'|'
            )
        ) details1
    SUM(IFNULL(s.out_quantity, '0')) AS OUTQTY,
        GROUP_CONCAT(
            CONCAT(
                COALESCE(IFNULL(s.out_quantity,'0'), ''),'|'
            )
        ) details
FROM stockin p LEFT JOIN stockout s
    ON p.serialno = s.serialno
    WHERE p.productid = 'TF00123'
GROUP BY p.stockin_id, p.serialno, s.serialno