MySQL单查询实现班级调查达标占比统计（问题1得分>3）

Got it, let's work through this together. You want to calculate the percentage of classes in your project where Question 1 scores are above 3, and you're looking to combine two separate MySQL queries into a single statement—here's how to make that happen.

Core Assumption #

First, I’m assuming you want to count a class as "qualifying" if at least one of its student surveys has a Question 1 score greater than 3. If your logic is different (like average score over 3, or all surveys meeting the threshold), I can adjust the query accordingly!

Single MySQL Query Solution #

This consolidated query will compute the percentage in one go, no need for two separate steps:

SELECT 
    ROUND(
        (COUNT(DISTINCT CASE WHEN question1_score > 3 THEN class_id END) / COUNT(DISTINCT class_id)) * 100,
        2
    ) AS qualifying_class_percentage
FROM 
    classroom_surveys;

How This Works #

Let’s break down the key parts:

• COUNT(DISTINCT class_id): Grabs the total number of unique classes in your survey table (so even if a class has 5 surveys like your GENE01 example, it only counts once).
• COUNT(DISTINCT CASE WHEN question1_score > 3 THEN class_id END): Counts only unique classes where at least one survey has a Question 1 score over 3. The CASE statement filters qualifying rows first, then we use DISTINCT to avoid counting the same class multiple times.
• ROUND(..., 2): Rounds the final percentage to 2 decimal places for readability—feel free to adjust the number if you need more or less precision.

If You Need "Average Score >3" Instead #

If your requirement is to count classes where the average Question 1 score across all their surveys is greater than 3, use this adjusted query:

SELECT 
    ROUND(
        (COUNT(*) / (SELECT COUNT(DISTINCT class_id) FROM classroom_surveys)) * 100,
        2
    ) AS qualifying_class_percentage
FROM (
    SELECT 
        class_id,
        AVG(question1_score) AS avg_q1_score
    FROM 
        classroom_surveys
    GROUP BY 
        class_id
    HAVING 
        avg_q1_score > 3
) AS class_avg_scores;

This first calculates the average score per class, filters those that meet the threshold, then divides by the total number of unique classes to get the percentage.

Quick Notes #

• Swap classroom_surveys with your actual table name.
• Replace class_id with your column that identifies classes (like your GENE01 example).
• Replace question1_score with the real column storing Question 1 scores.

If you need to tweak the logic further—like counting classes where a certain percentage of surveys meet the score—just say the word!

内容的提问来源于stack exchange，提问作者Kenny Wilson