真的需要一些指导,我有一个网站,人们可以上传图像到比赛,他们得到了一个结果(分数),我想拉出一个领导委员会,看看每个人都在做什么。然而,我的SQL查询有重复的人名,有人能告诉我在哪里修改我的查询,以便将1个人参加3场比赛的结果相加,并且只在输出中声明一次总分。结果保存在tblMembEntComp
fldResult
中。
我现在得到的是:
我的PHP代码:
<p><b>LEADERSHIP BOARD</b></p>
<?php
$query = "SELECT `tblMember`.`fldFName`, `tblMember`.`fldSName`, `tblMembEntComp`.`fldResult` FROM `tblMember` AS `tblMember` JOIN `tblMembEntComp` as `tblMembEntComp` ON `tblMember`.`fldMemberID` = `tblMembEntComp`.`fldMemberID`ORDER BY `fldResult` DESC";
$result = $conn -> query($query);
while($row = $result -> fetch_assoc())
{
echo $row['fldFName']." ".$row['fldSName']." ".$row['fldResult']."<br>";
}
?>
更新:我已经按照下面用户的建议尝试了GROUP BY
函数,但是我被抛出了错误:
发布于 2019-03-18 02:30:43
您似乎想要一个聚合查询。类似于:
SELECT m.fldSName, SUM(mec.fldResult) as fldResult
FROM tblMember m JOIN
tblMembEntComp mec
ON m.fldMemberID =mec.fldMemberID
GROUP BY m.fldSName
ORDER BY SUM(mec.fldResult) DESC
发布于 2019-03-18 01:52:24
group by query by tblMember
主键如下:
<?php
$query = "SELECT `tblMember`.`fldFName`, `tblMember`.`fldSName`, `tblMembEntComp`.`fldResult`, `tblMember`.`fldId` FROM `tblMember` AS `tblMember` JOIN `tblMembEntComp` as `tblMembEntComp` ON `tblMember`.`fldMemberID` = `tblMembEntComp`.`fldMemberID` GROUP BY `tblMember`.`fldId` ORDER BY `fldResult` DESC";
$result = $conn -> query($query);
while($row = $result -> fetch_assoc())
{
echo $row['fldFName']." ".$row['fldSName']." ".$row['fldResult']."<br>";
}
?>
您可以将group by字段更新为主键。希望它能帮助您:)
https://stackoverflow.com/questions/55210068
复制相似问题