Join
將多個表的結果分組以在計數操作後得到唯一的結果
survey_categories +---------------+-------------------+ | survey_cat_id | survey_cat_status | +---------------+-------------------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +---------------+-------------------+ survey_question +---------+---------------+------+ | ques_id | survey_cat_id | data | +---------+---------------+------+ | 1 | 1 | c | | 2 | 1 | c | | 3 | 1 | t | | 4 | 2 | c | | 5 | 2 | c | | 6 | 3 | c | | 7 | 3 | t | | 8 | 3 | c | | 9 | 4 | t | | 10 | 4 | t | +---------+---------------+------+ survey_details +-----------+---------+----------+---------------+ | survey_id | ques_id | store_id | answer | +-----------+---------+----------+---------------+ | 1 | 1 | 102 |excellent | | 1 | 2 | 102 |very Good | | 1 | 3 | 102 |xxxxxxxxxxxxx | | 1 | 4 | 102 |very Good | | 1 | 5 | 102 |poor | | 1 | 6 | 102 |excellent | | 1 | 7 | 102 |xxxxxxxxxxxxx | | 2 | 1 | 102 |excellent | | 2 | 2 | 102 |very Good | | 2 | 3 | 103 |xxxxxxxxxxxxx | | 2 | 4 | 103 |very Good | | 2 | 5 | 103 |poor | | 2 | 6 | 103 |excellent | | 2 | 7 | 103 |xxxxxxxxxxxxx | +-----------+---------+----------+---------------+我需要一個查詢形式的解決方案來獲得以下結果輸出：
+---------------+----------+----------+---------------+----------+----------+---------------+ | survey_cat_id | store_id |excellent | Very Good |Good |Average |Poor | +---------------+----------+----------+---------------+----------+----------+---------------+ | 1 | 102 |2 | 2 |0 |0 |0 | +---------------+----------+----------+---------------+----------+----------+---------------+ | 2 | 102 |0 | 1 |0 |0 |1 | +---------------+----------+----------+---------------+----------+----------+---------------+ | 3 | 102 |1 | 0 |0 |1 |0 | +---------------+----------+----------+---------------+----------+----------+---------------+ | 1 | 103 |0 | 0 |0 |0 |0 | +---------------+----------+----------+---------------+----------+----------+---------------+ | 2 | 103 |0 | 1 |0 |0 |1 | +---------------+----------+----------+---------------+----------+----------+---------------+ | 3 | 103 |0 | 1 |0 |0 |0 | +---------------+----------+----------+---------------+----------+----------+---------------+為了實現這一點，我編寫了一個 sql 查詢，但它沒有提供預期的結果：
select survey_categories.survey_cat_id, survey_questions.ques_id, survey_Details.store_id, count( Answer = 1 ) AS Excellent, count( Answer = 2 ) AS Very_Good, count( Answer = 3 ) AS Good, count( Answer = 4 ) AS Average, count( Answer = 5 ) AS Poor from survey_categories INNER JOIN survey_questions ON survey_categories.survey_cat_id = survey_questions.ques_title INNER JOIN survey_details ON survey_questions.ques_id = survey_details.ques_id group by survey_categories.survey_cat_id order by ques_id,answer;
我已經修改了您的查詢，並使用
CASE了以下語句COUNT：詢問：
select survey_categories.survey_cat_id, survey_question.ques_id, survey_details.store_id, count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent, count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good, count(CASE WHEN survey_details.answer = 'good' THEN 1 END) AS Good, '' AS Average, count(CASE WHEN survey_details.answer = 'poor' THEN 1 END) AS Poor from survey_categories INNER JOIN survey_question ON survey_categories.survey_cat_id = survey_question.survey_cat_id INNER JOIN survey_details ON survey_question.ques_id = survey_details.ques_id group by survey_categories.survey_cat_id,survey_question.ques_id order by ques_id,answer;測試：
mysql> select -> survey_categories.survey_cat_id, -> survey_question.ques_id, -> survey_details.store_id, -> count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent, -> count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good, -> count(CASE WHEN survey_details.answer = 'good' THEN 1 END) AS Good, -> '' AS Average, -> count(CASE WHEN survey_details.answer = 'poor' THEN 1 END) AS Poor -> from survey_categories -> INNER JOIN survey_question ON survey_categories.survey_cat_id = survey_question.survey_cat_id -> INNER JOIN survey_details ON survey_question.ques_id = survey_details.ques_id -> group by survey_categories.survey_cat_id,survey_question.ques_id -> order by ques_id,answer; +---------------+---------+----------+-----------+-----------+------+---------+------+ | survey_cat_id | ques_id | store_id | Excellent | Very_Good | Good | Average | Poor | +---------------+---------+----------+-----------+-----------+------+---------+------+ | 1 | 1 | 102 | 2 | 0 | 0 | | 0 | | 1 | 2 | 102 | 0 | 2 | 0 | | 0 | | 1 | 3 | 102 | 0 | 0 | 0 | | 0 | | 2 | 4 | 102 | 0 | 2 | 0 | | 0 | | 2 | 5 | 102 | 0 | 0 | 0 | | 2 | | 3 | 6 | 102 | 2 | 0 | 0 | | 0 | | 3 | 7 | 102 | 0 | 0 | 0 | | 0 | +---------------+---------+----------+-----------+-----------+------+---------+------+ 7 rows in set (0.00 sec) mysql>順便說一句，對於
AVG，您對哪些欄位的預期計算是什麼？