如何从查询中选择不同的值并连接到一列中 - How to select distinct values from query and concatenate into one column

How to select distinct values from query and concatenate into one column

本文关键字：连接一列查询选择 | 更新日期: 2023-09-27

我有一个表的category列。除了选择客户端之外，我还想在每一行上选择类别的可能的值—也就是说，该子集中类别的所有惟一值。

我的表是这样的:

| id | name          | category    |
------------------------------------
| 1  | Test Client   | Retail      |
| 2  | Test Client 2 | Corporate   |
| 3  | Test Client 3 | Retail      |
| 4  | Test Client 4 | Retail      |
| 5  | Test Client 5 | Leisure     |

我认为GROUP_CONCAT可以解决这个问题:

SELECT `client`.*, GROUP_CONCAT(DISTINCT client.category) AS possible_categories
FROM (`client`)
WHERE  `name`  LIKE '%query%'
GROUP BY `client`.`id`

…但它只给出了这一行的类别，而不是其他的

我可以在代码中完成，但这是一个O(n)操作，我宁愿节省处理时间。为了便于说明，下面是我如何在代码中这样做的:

return array_unique(array_map(function($client)
{
    return $client->category;
}, $clients));

理想的场景是看到这样的表:

| id | name          | category    | possible_categories     |
---------------------------------------------------------------
| 1  | Test Client   | Retail      | Retail,Corporate,Leisure |
| 2  | Test Client 2 | Corporate   | Retail,Corporate,Leisure |
| 3  | Test Client 3 | Retail      | Retail,Corporate,Leisure |
| 4  | Test Client 4 | Retail      | Retail,Corporate,Leisure |
| 5  | Test Client 5 | Leisure     | Retail,Corporate,Leisure |

假设您指的是匹配名称的可能类别:-

SELECT `client`.*, Sub1.possible_categories
FROM (`client`)
CROSS JOIN (SELECT GROUP_CONCAT(DISTINCT client.category) AS possible_categories FROM (`client`) WHERE  `name`  LIKE '%query%') Sub1
WHERE  `name`  LIKE '%query%'

注意like开头的通配符可能会使它运行缓慢

我想这对你有用:

SELECT id, name, category, (SELECT GROUP_CONCAT(distinct category) FROM Client) AS possible_categories
FROM client

看到演示在SQLFiddle 。

如果您所说的"可能的类别"是指所有类别，那么您需要分别计算它们并将它们添加到:

SELECT `client`.*, cc.possible_categories
FROM `client` cross join 
     (select GROUP_CONCAT(DISTINCT client.category) AS possible_categories
      from `client`
      where `name`  LIKE '%query%'
     ) cc
WHERE  `name`  LIKE '%query%'