我早些時(shí)候問了一個(gè)關(guān)于如何執(zhí)行此查詢的問題并收到了很好的回復(fù)。不幸的是，它比我通常運(yùn)行的查詢要高級一些，所以我不知道如何檢索結(jié)果并將它們顯示在我的網(wǎng)頁上。這是原始問題原始問題的鏈接 <?php// Associative array$result = $db->query("SELECT Name, COALESCE(AVG(CASE WHEN mth = 1 THEN PTS END), 0) AS Jan, COALESCE(AVG(CASE WHEN mth = 2 THEN PTS END), 0) AS Feb, COALESCE(AVG(CASE WHEN mth = 3 THEN PTS END), 0) AS Mar, COALESCE(AVG(CASE WHEN mth = 4 THEN PTS END), 0) AS Apr, COALESCE(AVG(CASE WHEN mth = 5 THEN PTS END), 0) AS May, COALESCE(AVG(CASE WHEN mth = 6 THEN PTS END), 0) AS Jun, COALESCE(AVG(CASE WHEN mth = 7 THEN PTS END), 0) AS Jul, COALESCE(AVG(CASE WHEN mth = 8 THEN PTS END), 0) AS Aug, COALESCE(AVG(CASE WHEN mth = 9 THEN PTS END), 0) AS Sep, COALESCE(AVG(CASE WHEN mth = 10 THEN PTS END), 0) AS Oct, COALESCE(AVG(CASE WHEN mth = 11 THEN PTS END), 0) AS Nov, COALESCE(AVG(CASE WHEN mth = 12 THEN PTS END), 0) AS Dec, AVG(PTS) AS AVGFROM ( SELECT Name, `Points Pass` AS PTS, MONTH(STR_TO_DATE(`OS Date`, '%a %b %e %H:%i:%s %Y')) AS mth FROM data) dGROUP BY Name");while($row = mysqli_fetch_array($result)) { ?> <tr </tr> <?php }if (!$result) { echo $db->error; }
1 回答
慕少森
TA貢獻(xiàn)2019條經(jīng)驗(yàn) 獲得超9個(gè)贊
你有幾個(gè)問題：
索引
$row需要用引號括起來，$row['name']否則你會收到很多“未定義常量”警告；$row['Jan']是檢索月度數(shù)據(jù)的正確方法，您需要為其他每個(gè)月復(fù)制它；替換
$avgGrade為$row['AVG']我不確定你想用什么來實(shí)現(xiàn)
<td class = "gScore-<?php echo $row[Jan]; ?>"></td>
你真的想要
<td class = "gScore"><?php echo $row[Jan]; ?></td>
您需要將
-- repeat for May to November行添加到查詢即
$result = $db->query("SELECT name,
COALESCE(AVG(CASE WHEN mth = 1 THEN PTS END), 0) AS Jan,
COALESCE(AVG(CASE WHEN mth = 2 THEN PTS END), 0) AS Feb,
COALESCE(AVG(CASE WHEN mth = 3 THEN PTS END), 0) AS Mar,
COALESCE(AVG(CASE WHEN mth = 4 THEN PTS END), 0) AS Apr,
COALESCE(AVG(CASE WHEN mth = 5 THEN PTS END), 0) AS May,
COALESCE(AVG(CASE WHEN mth = 6 THEN PTS END), 0) AS Jun,
COALESCE(AVG(CASE WHEN mth = 7 THEN PTS END), 0) AS Jul,
COALESCE(AVG(CASE WHEN mth = 8 THEN PTS END), 0) AS Aug,
COALESCE(AVG(CASE WHEN mth = 9 THEN PTS END), 0) AS Sep,
COALESCE(AVG(CASE WHEN mth = 10 THEN PTS END), 0) AS Oct,
COALESCE(AVG(CASE WHEN mth = 11 THEN PTS END), 0) AS Nov,
COALESCE(AVG(CASE WHEN mth = 12 THEN PTS END), 0) AS `Dec`,
AVG(PTS) AS AVG
FROM (
SELECT name, PTS AS PTS, MONTH(STR_TO_DATE(DATE, '%a %b %e %H:%i:%s %Y')) AS mth
FROM data
) d
GROUP BY name");
- 1 回答
- 0 關(guān)注
- 88 瀏覽
添加回答
舉報(bào)
0/150
提交
取消