mysql - 在mysql中使用子查询获取逗号分隔列表

Question

我有截至特定日期的可用组件数量和库存历史成本的显示数据。使用以下查询可以正常工作。

SELECT s.part_id,
       sum(s.updated_quantity),
       p.item_code,
       sum(
             (SELECT (s.updated_quantity * cost)
              FROM inventory
              WHERE inventory.id=s.inv_id)) AS tcost
FROM status_history AS s,
     inventory AS i,
     part_master AS p
WHERE s.action='add'
  AND DATE(s.date_created)<='2013-04-09'
  AND i.currency_id=1
  AND s.inv_id=i.id
  AND s.part_id=p.id
GROUP BY s.part_id

我还想在单个字段中显示以逗号分隔的组件的位置名称。为了获得我想要的结果，我尝试了以下查询，但它只返回一个位置名称，而不是多个位置名称的逗号分隔字符串。

SELECT s.part_id,
       sum(s.updated_quantity),
       p.item_code,
       sum(
             (SELECT (s.updated_quantity * cost)
              FROM inventory
              WHERE inventory.id=s.inv_id)) AS tcost,
       CONCAT_WS(',',
                   (SELECT name
                    FROM location_master
                    WHERE id=i.location_id)) AS LOCATION
FROM status_history AS s,
     inventory AS i,
     part_master AS p
WHERE s.action='add'
  AND DATE(s.date_created)<='2013-04-09'
  AND i.currency_id=1
  AND s.inv_id=i.id
  AND s.part_id=p.id
GROUP BY s.part_id

Answer 1

看下面的例子:

mysql> select Country,Abbreviation from Country;
+--------------------------+--------------+
| Country                  | Abbreviation |
+--------------------------+--------------+
| INDIA                    | ID           |
| Canada                   | CAN          |
| Australiya               | AUS          |
| United Kingdom           | UK           |
| United States Of America | USA          |
| PAKISTAN                 | PAK          |
| MILAN                    | Panchal      |
| MILAN                    | Panchal      |
| JAPAN                    | JP           |
+--------------------------+--------------+
9 rows in set (0.00 sec)

Panchal有2条记录

按缩写分组将仅显示 1 条记录。

mysql> 从Country group by Abbreviation中选择Country,Abbreviation;

+--------------------------+--------------+
| Country                  | Abbreviation |
+--------------------------+--------------+
| Australiya               | AUS          |
| Canada                   | CAN          |
| INDIA                    | ID           |
| JAPAN                    | JP           |
| PAKISTAN                 | PAK          |
| MILAN                    | Panchal      |
| United Kingdom           | UK           |
| United States Of America | USA          |
+--------------------------+--------------+

Country 上的 GROUP_CONCAT 将显示逗号分隔值。

mysql> select GROUP_CONCAT(Country),Abbreviation from Country group by Abbreviation;

+--------------------------+--------------+
| GROUP_CONCAT(Country)    | Abbreviation |
+--------------------------+--------------+
| Australiya               | AUS          |
| Canada                   | CAN          |
| INDIA                    | ID           |
| JAPAN                    | JP           |
| PAKISTAN                 | PAK          |
| MILAN,MILAN              | Panchal      |
| United Kingdom           | UK           |
| United States Of America | USA          |
+--------------------------+--------------+

从上往下看第 6 条记录。因为它包含逗号分隔值。

了解更多关于 GROUP_CONCAT() 函数的信息 click here