如何如果我有几个行具有相同值的方式的同时,得到从 mysql 结果的单个值

标签: html5 MySQL PHP
发布时间: 2016/10/3 12:33:06
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有一个叫做 '雇员' 的数据库使用此值︰

value1     | value2 | value3
-----------------------------
employee1  |  key1  |  boss1
employee2  |  key2  |  boss1
employee3  |  key3  |  boss1
employee4  |  key4  |  boss2
employee5  |  key5  |  boss2

在 php 中,我想遍历结果让所有员工的老板,但只有一次获得老板的名字,我试过用 GROUP BY ,但当循环这只显示第一个条目︰

$query = "SELECT * FROM employees WHERE value3 = 'boss1' GROUP BY value3";
$result = $db->query($query);
$foreach ($result as $row) {
    echo $row["value1"] . $row["value2"] . $row["value3"];
}

结果︰

employee1  |  key1  |  boss1

没有 GROUP BY ,当循环与 foreach ,它显示每个条目曾经的老板名称

$query = "SELECT * FROM employees WHERE value3 = 'boss1'";
$result = $db->query($query);
foreach ($result as $row) {
    echo $row["value1"] . $row["value2"] . $row["value3"];
}

结果︰

employee1  |  key1  |  boss1
employee2  |  key2  |  boss1
employee3  |  key3  |  boss1

我试图得到类似的结果︰

boss1
    employee1  |  key1
    employee2  |  key2
    employee3  |  key3

解决方法 1:

您可以使用 GROUP_CONCAT()comma (默认值) 分隔的列表 value1values2 为每个 boss 像这样︰

SELECT 
  GROUP_CONCAT(e.value1) AS value1,
  GROUP_CONCAT(e.value2) AS value2,
  e.value3 AS boss
FROM employees e
WHERE value3 = 'boss1' 
GROUP BY value3;

这会让你像为 boss1 :

employee1,employee2,employee3 | key1,key2,key3 | boss1

编辑 1:As a html list, something like: <ul>boss1 <li>employee1</li> <li>employee2</li> <li>employee3</li> </ul> I don't need the html code, but I need to get the data in a format which I can use as a list in html

SELECT 
  CONCAT('<ul>',GROUP_CONCAT(CONCAT('<li>',e.value1,'<\/li>')),'<\/ul>') AS value1,
  GROUP_CONCAT(e.value2) AS value2,
  e.value3 AS boss
FROM employees e
WHERE value3 = 'boss1' 
GROUP BY value3;

:-你应该使用服务器端的语言来实现这一目标。并使用第一个查询。

赞助商