SQL选择并按另一列分组后显示一列的所有值 [英] Sql select and display all values of one column after grouping by other column

问题描述

我有一张下表，其中存储了每个车手和他竞选的球队的得分.

I have the following table that stores the score of each driver and the team he is racing for.

driver    team     points
-------------------------
Josh      Mercedes   12
Aron      BMW         5
Ben       Mercedes    9
Jake      BMW        17
Mike      BMW         7
Brad      Chevrolet   3

现在，我想显示每个团队的分数+团队中所有车手的姓名.

Now I'd like to display the score for each team + the names of all drivers in the team.

所需的输出:

BMW         29     Aron, Jake, Mike
Mercedes    21     Josh, Ben
Chevrolet    3     Brad

查询:我的查询的问题在于它仅显示第一个驱动程序的名称.我必须使用什么函数来包含所有驱动程序的名称?

Query: The problem with my query is that it only displays the first driver's name. What function do I have to use to include the names of all the drivers?

<?php
$sql = "SELECT driver, team, points, SUM(points) AS totals FROM `example-table` GROUP BY team ORDER BY `points` DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute();

if($stmt->rowCount())
{
while ($result = $stmt->fetch(PDO::FETCH_ASSOC))
{

echo $result['team'];
echo $result['driver'];
echo $result['totals'];
?>
<br>

<?php 
}// end while
}// end if
else {
 echo '0 results';
}// end else
?>

推荐答案

使用 Group_Concat 函数

SELECT team, SUM(points) AS totals,group_concat(driver)
FROM `example-table` 
GROUP BY team 
ORDER BY `points` DESC

这篇关于SQL选择并按另一列分组后显示一列的所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！