我正在为和我一起玩的人制作一个关于高尔夫的统计页面。我试图拉出数据库的次数，我们所有的记分卡，我们收到小鸟(这是-1低于标准杆)。它确实每洞打出- 15，但是我注意到如果你在记分卡上有2只小鸟，它仍然只算作1只小鸟，而不是2只。我想让它继续计数，所以如果有人得到9只小鸟，那9只就加到总数中。
$query_p321 = "SELECT t1.*,COUNT(t1.player_id),t2.* FROM scorecards t1 LEFT JOIN courses t2 ON t1.course_id=t2.course_id
WHERE t1.hole1<t2.hole1_par AND t1.hole1>t2.hole1_par-2
OR t1.hole2<t2.hole2_par AND t1.hole2>t2.hole2_par-2
OR t1.hole3<t2.hole3_par AND t1.hole3>t2.hole3_par-2
OR t1.hole4<t2.hole4_par AND t1.hole4>t2.hole4_par-2
OR t1.hole5<t2.hole5_par AND t1.hole5>t2.hole5_par-2
OR t1.hole6<t2.hole6_par AND t1.hole6>t2.hole6_par-2
OR t1.hole7<t2.hole7_par AND t1.hole7>t2.hole7_par-2
OR t1.hole8<t2.hole8_par AND t1.hole8>t2.hole8_par-2
OR t1.hole9<t2.hole9_par AND t1.hole9>t2.hole9_par-2
OR t1.hole10<t2.hole10_par AND t1.hole10>t2.hole10_par-2
OR t1.hole11<t2.hole11_par AND t1.hole11>t2.hole11_par-2
OR t1.hole12<t2.hole12_par AND t1.hole12>t2.hole12_par-2
OR t1.hole13<t2.hole13_par AND t1.hole13>t2.hole13_par-2
OR t1.hole14<t2.hole14_par AND t1.hole14>t2.hole14_par-2
OR t1.hole15<t2.hole15_par AND t1.hole15>t2.hole15_par-2
OR t1.hole16<t2.hole16_par AND t1.hole16>t2.hole16_par-2
OR t1.hole17<t2.hole17_par AND t1.hole17>t2.hole17_par-2
OR t1.hole18<t2.hole18_par AND t1.hole18>t2.hole18_par-2
GROUP BY t1.player_id ORDER BY count(t1.player_id) DESC";
$result_p321 = mysql_query($query_p321);
$number = 1;
while ($row_p321 = mysql_fetch_array($result_p321)) {
$player_id2 = $row_p321["player_id"];
}
and so on..
你会注意到"-2"在那里。也就是用标准杆减去2杆，因为我不想记录这个人是否低于标准杆2杆。就差一杆。任何帮助都是感激的。谢谢你！哦，还有，GROUP BY需要使用，因为我不想列出球员的名字不止一次。我只想让它计算所有的小鸟。我想我最大的问题是它每行计数不超过1。谢谢。
问题是where子句。您需要在select子句中进行比较以便对它们进行计数:
SELECT t1.*,
sum((t1.hole1 = t2.hole1_par - 1) +
(t1.hole2 = t2.hole2_par - 1) +
. . .
(t1.hole18 = t2.hole18_par - 1)
) as birdies
FROM scorecards t1 LEFT JOIN
courses t2 ON t1.course_id=t2.course_id
GROUP BY t1.player_id
ORDER BY birdies DESC
使用MySQL约定true为1,false为0来将数字相加。另一种使用标准SQL的公式是:
sum((case when t1.hole1 = t2.hole1_par - 1) then 1 else 0 end) +
试试这样做:
SELECT t1.*, SUM( IF(t1.hole1 = t2.hole1_par-1,1,0) +
IF(t1.hole2 = t2.hole2_par-1,1,0) +
IF(t1.hole3 = t2.hole3_par-1,1,0) +
IF(t1.hole4 = t2.hole4_par-1,1,0) +
-- etc.
IF(t1.hole18 = t2.hole18_par-1,1,0) ) AS birdies
FROM scorecards t1
LEFT JOIN courses t2 ON t1.course_id=t2.course_id
GROUP BY t1.player_id
ORDER BY birdies DESC