如何在多列AVG查询及JOIN语句中使用WHERE IN条件？

Hey there! Let's fix this up. Your original approach uses separate subqueries for each rating category, which works for a single institute—but it's inefficient and messy when you need data for multiple institutes at once. Here's a cleaner, faster way to handle this with WHERE IN support:

The Better Approach: Conditional Aggregation #

Instead of repeating subqueries for every institute and rating item, we can use conditional aggregation (combining AVG() with CASE WHEN) to calculate all averages in one pass over your tables. This lets us easily filter for multiple institutes using WHERE IN.

Example Query #

SELECT 
    i.instituteId,
    -- Calculate average for Academics (ratingItemId = '1')
    COALESCE(AVG(CASE WHEN rs.ratingItemId = '1' THEN rs.score END), 0) AS 'Academics',
    -- Calculate average for Area of Study (ratingItemId = '2')
    COALESCE(AVG(CASE WHEN rs.ratingItemId = '2' THEN rs.score END), 0) AS 'Area_of_study',
    -- Add more lines here for other ratingItemId values
    COALESCE(AVG(CASE WHEN rs.ratingItemId = '3' THEN rs.score END), 0) AS 'Campus_Facilities'
FROM institutions i
-- Left join to include institutes with no reviews (returns 0 for averages)
LEFT JOIN reviews r ON i.instituteId = r.instituteId
LEFT JOIN rating_score rs ON r.reviewsId = rs.reviewsId
-- Filter for your target institutes
WHERE i.instituteId IN ('1','2','4','15')
-- Group results by institute to get one row per institute
GROUP BY i.instituteId;

Breakdown of Key Parts: #

• LEFT JOIN: Ensures even institutes with no reviews show up in results (we use COALESCE() to turn NULL averages into 0—remove this if you prefer NULL instead).
• Conditional AVG(): The CASE WHEN clause only includes scores from the matching ratingItemId when calculating the average, so each column gets the specific rating category's average for the institute.
• GROUP BY i.instituteId: Groups all data by institute, giving you one row per institute with all its rating averages.

If You Don't Need to Include Institutes Without Reviews #

If you only want to return institutes that have at least one review, swap the LEFT JOINs for INNER JOINs:

SELECT 
    r.instituteId,
    AVG(CASE WHEN rs.ratingItemId = '1' THEN rs.score END) AS 'Academics',
    AVG(CASE WHEN rs.ratingItemId = '2' THEN rs.score END) AS 'Area_of_study'
FROM reviews r
INNER JOIN rating_score rs ON r.reviewsId = rs.reviewsId
WHERE r.instituteId IN ('1','2','4','15')
GROUP BY r.instituteId;

Why Avoid Your Original Subquery Approach for Multiple Institutes? #

If you tried to adapt your original subquery method for WHERE IN, you'd have to write a separate subquery block for every institute (using UNION ALL to combine results). This is incredibly redundant, slow (it scans your tables multiple times), and hard to maintain. The conditional aggregation method is far more efficient and scalable.

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