MySQL按一列分组，但选择所有数据 [英] MySQL Group by one column, but select all data

问题描述

我有一个包含很多数据的数据库.我想选择所有数据，但按一列分组.

I've got a database which contains lots of data. I would like to select all data, but Group by one column.

例如:

column a | column b
example  | apple
example  | pear
example  | orange
example  | strawberry

在示例列A是具有重复值且需要分组的列. 但是B列具有不同的值，我想选择所有这些值.

In the example column A is a column which has duplicate values and needs to be grouped. Column B however has different values, and I would like to select all these values.

最后，我想得到一个查询，结果为:

In the end, I would like to get a query that results in:

example
- apple
- pear
- orange
- strawberry

B列的值在jQuery手风琴中.

Where the values of column B are in a jQuery accordion.

我已经尝试并搜索了几个小时，但仍然没有找到一种方法.

I've tried and searched for a couple of hours but still haven't found a way to do this.

我设法使用Gordon提供的答案解决了问题. 使用PHP，我分解了查询结果，并通过while循环，从数组中收集了所有数据.

I've manage to solve the problem using the answer Gordon provided. Using PHP I exploded the query results and with a while loop I collected all the data from the arrays.

现在工作很好，谢谢大家！

It's working great now, thanks guys!

推荐答案

我认为您可以使用group_concat()获得所需的内容:

I think you can get what you want using group_concat():

select a, group_concat(b)
from t
group by a;

这将为每个a创建一个"b"列表.在您的示例中:

This will create a list of "b"s for each a. In your example:

example    apple,pear,orange,strawberry

您可以使用SEPARATOR关键字更改分隔符.

You can change the separator using the SEPARATOR keyword.

您可以多次使用group_concat():

select a, group_concat(b) as bs, group_concat(c) as cs
from t
group by a;

或者将其与concat()组合:

select a, group_concat(concat(b, ':', 'c')) as bcs
from t
group by a;

这篇关于MySQL按一列分组，但选择所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！