MySQL按指定优先级条件排序查询结果的技术咨询

Got it, let's adjust your query to match the exact priority order you need. The current ORDER BY users_packages.has_fair DESC only handles the first priority group, but doesn't distinguish between VIP and regular non-fair entries. Here's how to fix it using a CASE statement to explicitly define your priority levels:

Modified Query #

SELECT 
    `users_packages`.*, 
    `poster_websites`.*, 
    `sub_cats`.`sub_cat_name_ar` AS `sub_cat_name_ar`, 
    `sub_cats`.`sub_cat_name_en` AS `sub_cat_name_en`, 
    `main_cats`.`cat_image_defult` AS `cat_image_defult`, 
    `main_cats`.`slogen_cat_ar` AS `slogen_cat_ar`, 
    `main_cats`.`slogen_cat_en` AS `slogen_cat_en`, 
    `cities`.`city_name_ar` AS `city_name_ar`, 
    `cities`.`city_name_en` AS `city_name_en`, 
    `users`.`name` AS `use_name`, 
    `users`.`user_postion` AS `user_postion` 
FROM `poster_websites` 
LEFT JOIN `main_cats` ON `main_cats`.`id` = `poster_websites`.`poster_cat_id` 
LEFT JOIN `sub_cats` ON `sub_cats`.`id` = `poster_websites`.`poster_subcat_id` 
LEFT JOIN `users` ON `users`.`id` = `poster_websites`.`user_id` 
LEFT JOIN `cities` ON `cities`.`id` = `poster_websites`.`city_id` 
LEFT JOIN `users_packages` ON `users_packages`.`user_id` = `poster_websites`.`user_id` 
WHERE 
    `poster_websites`.`active` = 2 
    OR ( `users_packages`.`has_fair` = 1 AND `users_packages`.`expired_at` > '2020-06-09 11:54:26' ) 
    OR ( `users_packages`.`is_vip` = 1 AND `users_packages`.`has_fair` = 0 AND `users_packages`.`expired_at` > '2020-06-09 11:54:26' ) 
    OR ( `users_packages`.`has_fair` = 0 AND `users_packages`.`is_vip` = 0 AND `users_packages`.`expired_at` > '2020-06-09 11:54:26' ) 
ORDER BY 
    CASE 
        -- Priority 1: Fair (商铺广告)
        WHEN users_packages.has_fair = 1 THEN 1
        -- Priority 2: VIP without fair (VIP广告)
        WHEN users_packages.is_vip = 1 AND users_packages.has_fair = 0 THEN 2
        -- Priority 3: Regular (普通广告)
        ELSE 3
    END ASC,
    -- Optional: Add secondary sort (e.g., show newer expiring entries first within each group)
    users_packages.expired_at DESC
LIMIT 8 OFFSET 0;

Why This Works #

The CASE statement assigns a numerical priority to each group:

• 1 for entries where has_fair = 1 (highest priority)
• 2 for VIP entries without fair access
• 3 for all other eligible entries

By sorting this priority value in ascending order (ASC), we ensure your desired order: Fair ads first, then VIP, then regular.

Quick Note #

Since you're using a LEFT JOIN on users_packages, keep in mind that entries where poster_websites.active = 2 but there's no matching users_packages record will have NULL values for has_fair and is_vip. These will fall into the ELSE 3 category (regular ads) with this logic. If you want to handle these differently, you can add an additional WHEN clause for users_packages.has_fair IS NULL to assign a specific priority.

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