如何通过WHERE IN条件关联MySQL机构查询与对应最新评论查询

Got it, let's figure out how to combine these two queries so you get each institution's details paired with its most recent review. I'll walk you through two reliable approaches depending on your MySQL version:

1. Using Window Functions (Recommended for MySQL 8.0+) #

This method is clean and efficient because it lets you rank reviews per institution in one pass, then pick only the latest one. Here's the full query:

SELECT 
    i.instituteId,
    i.instituteName,
    i.description,
    r.name,
    r.review,
    r.timestamp
FROM institutions i
LEFT JOIN (
    SELECT 
        name,
        review,
        timestamp,
        instituteId,
        ROW_NUMBER() OVER (PARTITION BY instituteId ORDER BY timestamp DESC) AS rn
    FROM reviews
) r ON i.instituteId = r.instituteId AND r.rn = 1
WHERE i.instituteId IN ('1','2','3');

How this works: #

• The subquery uses ROW_NUMBER() to assign a rank (rn) to each review for an institution, sorted by timestamp descending. The latest review gets rn = 1.
• We join this subquery to the institutions table, filtering only for rows where rn = 1 to get the newest review per institution.
• The LEFT JOIN ensures you still get institution data even if there are no reviews for it (remove LEFT if you only want institutions with existing reviews).

2. Subquery for Older MySQL Versions (Pre-8.0) #

If you're stuck on MySQL 5.x where window functions aren't available, you can first find the latest timestamp for each institution, then join back to the reviews table to get the corresponding review content:

SELECT 
    i.instituteId,
    i.instituteName,
    i.description,
    r.name,
    r.review,
    r.timestamp
FROM institutions i
LEFT JOIN (
    -- Get the latest timestamp per institution
    SELECT instituteId, MAX(timestamp) AS latest_ts
    FROM reviews
    GROUP BY instituteId
) latest_r ON i.instituteId = latest_r.instituteId
LEFT JOIN reviews r ON latest_r.instituteId = r.instituteId AND latest_r.latest_ts = r.timestamp
WHERE i.instituteId IN ('1','2','3');

Note: #

• If multiple reviews have the exact same latest timestamp for an institution, this will return all of them. If you only want one, you might need to add an extra condition (like MAX(reviewId) if you have a unique ID) to pick a single entry.

Quick Optimization Tip #

To speed up these queries, add a composite index on the reviews table:

CREATE INDEX idx_reviews_institute_timestamp ON reviews(instituteId, timestamp DESC);

This helps MySQL quickly find the latest review for each institution without scanning the entire table.

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