时间戳

首页 > 解决方案 > mysql排序记录基于不同的where

mysql - mysql排序记录基于不同的where

问题描述

基本上我想按以下顺序得到结果

1）首先显示所有结果，其中users_packages。has_fair= 1

2）第二个显示所有结果，其中

users_packages. is_vip= 1 和 users_packages。has_fair= 0

3) 第三

users_packages. has_fair= 0 和 users_packages。is_vip= 0

请指导我，我需要完全满足每个条件的上述订单中的所有记录。谢谢

Sql查询如下

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
    users_packages.has_fair DESC
LIMIT 8 OFFSET 0

预期产出

商店广告将首先展示

VIP 广告将在第二个展示

特殊广告将显示第三

标签： mysqlsqldatabase

解决方案

您可以对多列进行排序

order by users_packages.has_fair desc,users_packages.is_vip desc

如果这不能产生您想要的结果，请将示例数据和预期输出作为文本添加到问题中（或小提琴）

推荐阅读