我有一張表格tbl_rating，其中包含具有以下結構的調查結果：
| office_name | rating |
|-------------|-------------|
| Office1 | Satisfied |
| Office3 | Unsatisfied |
| Office2 | Neutral |
| Office1 | Satisfied |
我的查詢無法顯示DISTINCT該office_name列的所有值以及該列中相同值的總數rating。
我正在尋找的查詢結果如下：
| office_name | Satisfied | Neutral | Unsatisfied |
|-------------|-----------|---------|-------------|
| Office1 | 2 | 0 | 0 |
| Office2 | 0 | 1 | 0 |
| Office3 | 0 | 0 | 1 |
這是我目前正在處理的查詢。它適用于計算宣告值的評級總數，但是當我嘗試使用DISTINCT office顯示陣列的結果時確實給了我一個錯誤
$office = $db->query("SELECT DISTINCT office
(SELECT COUNT(rating) FROM tbl_result WHERE rating='Satisfied') AS S,
(SELECT COUNT(rating) FROM tbl_result WHERE rating='Neutral') AS N,
(SELECT COUNT(rating) FROM tbl_result WHERE rating='Unsatisfied') AS U");
uj5u.com熱心網友回復：
SELECT
office_name,
SUM(IF(rating = 'Satisfied', 1, 0)) AS Satisfied,
SUM(IF(rating = 'Neutral', 1, 0)) AS Neutral,
SUM(IF(rating = 'Unsatisfied', 1, 0)) AS Unsatisfied
FROM
tbl_rating
GROUP BY
office_name
編輯（感謝sticky bit的評論）：
- 洗掉了 DISTINCT 子句
- 將字串周圍的雙引號更改為單引號
sqlfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/343334.html
標籤：mysql