如何合并两个PHP/SQL查询并整合双表评分实现按总评分排序

Got it, let's work through this. First off, I’m assuming the shared identifier between your postextras and postcomments tables is post_id—that’s the standard setup for linking post-related data, but feel free to swap it out for your actual key if it’s different. The core idea is to aggregate ratings from both tables, calculate a single composite score, then sort your results by that score.

Here are two solid approaches depending on how you want to compute the composite rating:

Approach 1: Average of Averages (Weighted or Equal) #

Use this if you want to treat the average rating from postextras and postcomments as separate groups, then combine them (great for weighting one set of ratings more heavily than the other):

SELECT
    -- Replace with your main post table's key/columns if you need more post details
    COALESCE(pe.post_id, pc.post_id) AS post_id,
    -- Adjust the formula here to match your desired composite score logic
    COALESCE(
        (pe.avg_extra_rating * 0.6 + pc.avg_comment_rating * 0.4), -- Weighted example: 60% extras, 40% comments
        pe.avg_extra_rating, -- Fallback if no comment ratings exist
        pc.avg_comment_rating -- Fallback if no extra ratings exist
    ) AS composite_rating
FROM (
    -- Calculate average rating per post from postextras
    SELECT post_id, AVG(rating) AS avg_extra_rating
    FROM postextras
    GROUP BY post_id
) pe
FULL OUTER JOIN (
    -- Calculate average rating per post from postcomments
    SELECT post_id, AVG(rating) AS avg_comment_rating
    FROM postcomments
    GROUP BY post_id
) pc ON pe.post_id = pc.post_id
ORDER BY composite_rating DESC;

Notes for this approach: #

• FULL OUTER JOIN ensures posts that only have ratings in one table are still included. Use LEFT JOIN instead if you’re joining to a main posts table (just add FROM posts p and join pe/pc to p.post_id).
• Tweak the weighted formula to fit your needs—use (pe.avg_extra_rating + pc.avg_comment_rating)/2 for an equal average if weighting isn’t needed.
• Swap rating with your actual rating column names (e.g., extra_rating or comment_rating) if they differ between tables.

Approach 2: Overall Average of All Ratings #

Use this if you want to treat every individual rating from both tables equally (e.g., a post with 3 extra ratings and 5 comment ratings gets an average of all 8 scores):

SELECT
    post_id,
    AVG(rating) AS composite_rating
FROM (
    -- Combine all rating records from both tables
    SELECT post_id, rating FROM postextras
    UNION ALL
    SELECT post_id, rating FROM postcomments
) combined_ratings
GROUP BY post_id
ORDER BY composite_rating DESC;

Notes for this approach: #

• UNION ALL preserves all duplicate records (critical for accurate averaging—don’t use UNION here, as it removes duplicates and skews results).
• Again, adjust rating to match your actual column names if they’re different in each table.

Pick the approach that aligns with how you want to prioritize the ratings, and swap out any placeholder column/table names to match your database schema.

内容的提问来源于stack exchange，提问作者Alexander S.