求助：如何在SQL中按自定义评分逻辑排序职位发布列表

Got it, let's translate your PHP rating calculation into SQL so you can sort directly in your query. Here's how to do it step by step:

First, let's align your PHP logic to SQL operations:
Your PHP function computes:

1. Sum of three ratings: $rate1 + $rate2 + $rate3
2. Divide by the total number of ratings ($count)
3. Divide by 3 to get the final score percentage
4. Round to 1 decimal place for formatting

Adjusted Query with SQL-Based Rating Calculation #

SELECT 
    job_listing.id,
    -- Replicate your PHP rating logic, handle edge cases like division by zero
    IFNULL(
        ROUND(
            ( (job_order_ratings.rate1 + job_order_ratings.rate2 + job_order_ratings.rate3) / NULLIF(job_order_ratings.count, 0) ) / 3,
            1
        ),
        0
    ) AS calculated_rating
FROM job_listing 
LEFT JOIN job_order_ratings ON job_order_ratings.job_id = job_listing.id 
WHERE job_listing.job_status = 2 
-- Sort from highest rating to lowest (use DESC for descending order)
ORDER BY calculated_rating DESC

Key Breakdown: #

• NULLIF(job_order_ratings.count, 0): Prevents division-by-zero errors if a job has no ratings (returns NULL instead of crashing the query)
• IFNULL(..., 0): Sets the rating to 0 for jobs with no matching entries in job_order_ratings, so they'll appear at the end of your sorted list
• ROUND(..., 1): Matches the round(..., 1) call in your PHP function to keep exactly 1 decimal place
• ORDER BY calculated_rating DESC: Sorts jobs from highest to lowest rating (swap to ASC if you want the reverse, but your request specifies highest score first)

If Multiple Ratings Exist Per Job #

If your job_order_ratings table has multiple entries for the same job (instead of a single aggregated row), use this version to sum all ratings first:

SELECT 
    job_listing.id,
    IFNULL(
        ROUND(
            ( (SUM(job_order_ratings.rate1) + SUM(job_order_ratings.rate2) + SUM(job_order_ratings.rate3)) / NULLIF(SUM(job_order_ratings.count), 0) ) / 3,
            1
        ),
        0
    ) AS calculated_rating
FROM job_listing 
LEFT JOIN job_order_ratings ON job_order_ratings.job_id = job_listing.id 
WHERE job_listing.job_status = 2 
GROUP BY job_listing.id
ORDER BY calculated_rating DESC

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