mysql - 从sql查询和分组中获取百分比

Question

所以我目前有一个包含调查问题的数据库，现在我想运行一个特殊的查询，但不知道该怎么做。 注意事项:根据问题的不同，每个问题可以有 2-6 个答案。

这些是我正在使用的表格和一些示例数据:

 Table: answers_only         Table: questions_only
╔════════════════╦═══════════╗ ╔═════════════════╦════════════════════════════╗
║ answer_ID (PK) ║ answer    ║ ║question_ID (PK) ║ question                   ║
╠════════════════╬═══════════╣ ╠═════════════════╬════════════════════════════╣
║    65114       ║ yes       ║ ║       123       ║ Are you happy?             ║
║    614         ║ no        ║ ║       1967      ║ You think you're smart?    ║
║    23          ║ sometimes ║ ╚═════════════════╩════════════════════════════╝      
╚════════════════╩═══════════╝

                    Table: questions
╔════════════════╦══════════════════╦════════════════╦════════════════╗
║ unique_ID (PK) ║ question_ID (FK) ║ answer_ID (FK) ║ person_ID (FK) ║
╠════════════════╬══════════════════╬════════════════╬════════════════╣
║              1 ║              123 ║          65114 ║           5521 ║
║              2 ║              123 ║          614   ║           2511 ║
║              3 ║             1967 ║          614   ║           2511 ║
╚════════════════╩══════════════════╩════════════════╩════════════════╝

所以我有一个表 questions，其中包含 questions_only 的 ID (FK)，其中包含实际问题。

我想要获得的是问题的最高百分比，然后以此排序并获得超过 90% 的任何内容(查询)。所以...假设我们有 100 个人被问及“你快乐吗？”问题。 5% 的人说不，95% 的人说是。因为它是 95%，所以它会出现在查询中。另一方面，如果 20% 的人对“您认为自己很聪明？”回答否。问题，80% 的人说是 然后它就不会出现了。我想我需要接下来的一堆子查询，但我不确定。这就是我所拥有的……但我知道我离题太远了。任何帮助将不胜感激。

SELECT question, answer, round(COUNT(*)/(select count(*) from questions 
INNER JOIN questions_only on questions_only.question_ID=questions.question_ID)*100) AS    
'Percent' FROM questions 
INNER JOIN answers_only ON answers_only.answer_ID=questions.answer_ID 
INNER JOIN questions_only ON questions_only.question_ID=questions.question_ID 
GROUP BY answer order by COUNT(*) DESC

这里的最终目标是找出所有容易回答的问题，大多数人都赞成。

                         wanted results from query
╔═══════════════════════════════╦══════════════════╦════════════════╗
║ question                      ║      answer      ║      Percent   ║ 
╠═══════════════════════════════╬══════════════════╬════════════════╬
║  Are you happy?               ║          Yes     ║          97    ║        
║  You think you're smart?      ║          Yes     ║          96    ║          
║  1-5 How exciting is surfing? ║            5     ║          92    ║          
╚═══════════════════════════════╩══════════════════╩════════════════╝

Answer 1

您应该为每个问题创建一个包含答案计数的子查询，并将其与您的 QUESTIONS 表连接:

SELECT MAX(questions_only.question),
       MAX(answers_only.answer),
       (COUNT(*)/MAX(t.all_count))*100 as answer_percent


FROM questions
   JOIN ( 
           SELECT question_id, 
                  COUNT(*) as all_count
           FROM questions
           GROUP BY question_id 
         ) as t on questions.question_ID=t.question_ID
   JOIN questions_only ON questions.question_ID=questions_only.question_ID
   JOIN answers_only ON questions.answer_id=answers_only.answer_id

GROUP BY questions.question_ID,questions.answer_ID
HAVING (COUNT(*)/MAX(t.all_count))*100>=90

SQLFiddle demo