mysql-返回第一列一次和所有对应的列数据 [英] mysql - return first column once and all corresponding column data

问题描述

不确定标题是否真的定义了我要寻找的内容.

我想做的是返回所有具有匹配列值的行.仅显示第一个值一次，并显示所有其他列.这是表格的外观:

id     name    data
======================
0      john    data1
1      john    data2
2      jane    data1
3      jane    data2
4      jane    data3

因此结果将显示为:

john
  data1
  data2

jane
  data1
  data2
  data3

我知道我可以使用DISTINCT一次获取名称，但是我是否需要首先从查询中获取所有名称，然后执行循环并运行第二个查询，还是可以在一条语句中执行此操作? /p>

现在，我有一个foreach，它使用数组循环遍历并获取所有内容.因此，当前显示为:

john - data1
john - data2
jane - data1
jane - data2
jane - data3

从美学角度来看，每次显示第一列似乎都不好，但是我不太确定如何有效地进行此操作.

谢谢您的帮助.

解决方案

您可以通过使用MySQL的group_concat函数来实现此目的，而无需使用PHP函数即可:

SELECT name, group_concat(`data`) FROM table GROUP BY name;

将使用"John"返回第一列，并使用"data1，data2"返回第二列.然后，您可以在第二列上使用PHP的explode()函数来获取包含"data1"和"data2"的数组.

Not sure if the title really defines what I'm looking for.

What I want to do is return all rows that have a matching column value. Display the first value only once, and display all other columns. Here's a visual of the table:

id     name    data
======================
0      john    data1
1      john    data2
2      jane    data1
3      jane    data2
4      jane    data3

So the result would be displayed as:

john
  data1
  data2

jane
  data1
  data2
  data3

I know I can use DISTINCT to get the name once, but would I need to get all names from a query first, then do a loop and run a second query, or can I do this in one statement?

Right now I have a foreach that uses an array to loop through and get everything. So it currently displays as:

john - data1
john - data2
jane - data1
jane - data2
jane - data3

It just seems aesthetically bad to show the first column every time, but I'm not too sure how to go about this efficiently.

Thank you for any help.

解决方案

You can do this without the use of a PHP function by taking advantage of MySQL's group_concat function which does exactly what you're looking for:

SELECT name, group_concat(`data`) FROM table GROUP BY name;

Will return one column with "John" and a second column with "data1,data2". You can then use PHP's explode() function on the second column to get an array containing "data1" and "data2".

这篇关于mysql-返回第一列一次和所有对应的列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！