如何从查询中选择不同的值并连接到一列

I've got a table with a category column. Along with the selection of the clients, I'd like to select on each row the possible values of the category - that is, all the unique values of category in that subset.

我有一个带有类别列的表。在选择客户端的同时,我想在每一行中选择该类别的可能值 - 即该子集中类别的所有唯一值。

My table looks like this:

我的表看起来像这样:

| 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     |

I thought GROUP_CONCAT would do the trick:

我以为GROUP_CONCAT可以做到这一点:

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

...but it just gives me that row's category, not the others.

...但它只是给了我那个行的类别,而不是其他类别。

I can do it in code, but it's an O(n) operation and I'd rather save on the processing time. Here's how I could do it in code, for illustrative purposes:

我可以在代码中完成它,但它是一个O(n)操作,我宁愿节省处理时间。以下是我可以在代码中执行此操作的示例:

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

The ideal scenario would be to see a table like this:

理想的情况是看到这样的表格:

| 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 |

3 个解决方案

#1

Assuming you mean the possible categories for the matching names:-

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

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%'

Note the leading wildcard in the LIKEs will probably make it run slowly.

请注意,LIKE中的前导通配符可能会使其运行缓慢。

#2

I think this would work for you:

我认为这对你有用:

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

See the demo on SQLFiddle.

请参阅SQLFiddle上的演示。

#3

If by "possible categories" you mean all categories, then you need to calculate them separately and add them in:

如果“可能的类别”是指所有类别,那么您需要单独计算它们并将它们添加到:

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%'

#1