SQL查询仅返回一行结果，需调整以满足指定排序与筛选规则

问题描述 #

我拥有profile和jobs两张表，当前使用的SQL查询如下：

SELECT COUNT(jobs.id) as jobs_completed,
      profile.rating,
      profile.id
FROM  profile
JOIN jobs ON jobs.user_id = profile.user_id AND jobs.ended <> 0
WHERE profile.whoami = 'contractor' HAVING jobs_completed > 5
ORDER BY rating DESC, jobs_completed DESC 
LIMIT 6

输入数据集 #

预期输出数据集 #

当前查询仅返回1行结果，与预期的6行不符。需要修改查询实现：

• 排序规则：最高rating优先，其次最高jobs_completed优先
• 筛选规则：优先选取jobs_completed > 5的记录，当这类记录不足6条时，补充jobs_completed <=5的记录

修改后的SQL #

WITH ranked_contractors AS (
    SELECT 
        COUNT(jobs.id) as jobs_completed,
        profile.rating,
        profile.id,
        CASE WHEN COUNT(jobs.id) > 5 THEN 1 ELSE 0 END AS priority
    FROM profile
    JOIN jobs ON jobs.user_id = profile.user_id AND jobs.ended <> 0
    WHERE profile.whoami = 'contractor'
    GROUP BY profile.id, profile.rating
)
SELECT id, jobs_completed, rating
FROM ranked_contractors
ORDER BY 
    priority DESC,
    rating DESC,
    jobs_completed DESC
LIMIT 6;

逻辑说明 #

1. 通过CTE计算每个承包商的完成任务数，同时新增priority字段标记是否符合jobs_completed >5的条件，符合则标记为1，否则为0
2. 排序时先按priority降序，确保符合条件的记录排在最前面
3. 再按rating和jobs_completed降序排序，满足要求的优先级规则
4. 最后取前6条记录：当符合jobs_completed >5的记录不足6条时，自动补充不符合条件的记录，直到凑满6条

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