在MySQL中如何选择组?

I am trying to select and group by column, but keeping other column with all rows. I get error message that #1242 - Subquery returns more than 1 row.

我尝试按列选择和分组，但要保留其他列与所有行。我得到错误消息，#1242 -子查询返回超过1行。

My table

我的表

The result I want

我想要的结果

Bellow is my query :

Bellow是我的疑问:

SELECT name FROM table WHERE (SELECT pro_id FROM table GROUP BY pro_id)

2 个解决方案

#1

Try this query:

试试这个查询:

SELECT Pro_id
     , GROUP_CONCAT(Name SEPARATOR ', ') AS Name
  FROM MyTable 
 GROUP BY Pro_ID;

Result:

结果:

| PRO_ID |         NAME |
-------------------------
|      1 | john, sandra |
|      2 |          jeo |
|      3 | bruno, piter |

See this SQLFiddle

#2

You cannot "merge cells" in a SQL result set as you do in your example. The best you could do is either "concat" several values in one "cell" using the GROUP_CONCAT function, or sort the rows to group common values together using ORDER BY clause.

在SQL结果集中，不能像在示例中那样“合并单元格”。您所能做的最好的事情就是使用GROUP_CONCAT函数在一个“cell”中“concat”几个值，或者按照ORDER BY子句排序将共同的值分组到一起。

Considering that, you need a very basic SELECT ... ORDER BY statement:

考虑到这一点，你需要一个非常基础的选择…ORDER BY声明:

SELECT name, pro_id FROM table ORDER BY pro_id

Producing:

生产:

+---------+--------+
|  NAME   | PRO_ID |
+---------+--------+
| john    |      1 |
| sandra  |      1 |
| jeo     |      2 |
| bruno   |      3 |
| piter   |      3 |
+---------+--------+

See http://sqlfiddle.com/#!2/6ea716/2

看到http://sqlfiddle.com/ ! 2/6ea716/2