mysql - 2 表连接和 1 组连接

Question

我有如下表格

销售

salesid    |custid    |date_created    |total_price
S0001       C0001      2015-12-31       20.00
S0002       C0002      2016-01-01       10.00
S0003       C0003      2016-01-02       30.00
S0004       C0001      2016-01-05       50.00

销售详情

salesdetailsid    |salesid    |product_name           |quantity
D0001              S0001       pencil                  3
D0002              S0001       book                    2
D0003              S0001       ruler                   1
D0004              S0002       Hard Cover File Folder  1
D0005              S0003       A4 Paper                1
D0006              S0003       Notebook                1
D0006              S0004       Mouse                   1

我的预期输出应该是:

如果custid是C0001，输出:

salesid    | Purchase Date    | Items
S0001        2015-12-31         pencil x 3,book x 2, ruler x 1
S0004        2016-01-05         Mouse x 1

如果custid是C0002，输出:

salesid    | Purchase Date    | Items
S0002        2016-01-01         Hard Cover File Folder x 1

如果custid是C0003，输出:

salesid    | Purchase Date    | Items
S0003        2016-01-02         A4 Paper x 1, Notebook x 1

这是我目前所拥有的:

SELECT s.salesid          AS "id", 
       s.time_created     AS "Purchase Date",   
       s.total_price      AS "Amount", 
       GROUP_CONCAT(Concat(prodname, ' x ', quantity) SEPARATOR ', ') AS "Items" 
FROM   sales s 
JOIN   salesdetails d 
  ON   s.salesid = d.salesid 
WHERE  s.custid = 'C0001' 

Answer 1

您缺少 GROUP BY 子句，这实际上使您的 group_concat 起作用，看看它是否起作用:

SELECT s.salesid          AS "id", 
       s.time_created     AS "Purchase Date",   
       s.total_price      AS "Amount", 
       Group_concat(Concat(prodname, ' x ', quantity) SEPARATOR ', ') AS "Items" 
FROM   sales s 
JOIN   salesdetails d 
ON     s.salesid = d.salesid 
WHERE  s.custid = 'C0001' 
GROUP BY s.salesid