MYSQL Null 和空值排序

Question

我有一个名为 users 的表，其中一些列具有空值，即 user_country、user_state_user_city、user_area、用户密码。

我想按计数对空值进行排序。

例如:

user_id user_country user_state user_city user_area user_pincode
  1       1            1           1         1         1              - all values
  2       1            1           1                   2              - 1 empty or null
  3       1                                            1              - 3 empty or null
  4       1            1           1                                  - 2 empty or null
  5       1                                                           - 4 empty or null

它应该像下面这样排序:期望输出

user_id user_country user_state user_city user_area user_pincode
  2       1            1           1                   1            - 1 empty or null
  4       1            1           1                                - 2 empty or null
  3       1                                            1            - 3 empty or null
  5       1                                                         - 4 empty or null
  1       1            1           1         1         1            - all values

我的代码:

$where1 = "user_country IS NOT NULL  AND user_state IS NOT NULL AND user_city IS NOT NULL AND user_area_name IS NOT NULL AND user_area_pincode IS NOT NULL  AND user_country!='' AND user_state!='' AND user_city!='' AND user_area_name!='' AND user_area_pincode!=''";
                $this->db->where($where1);
$order = "user_city IS NOT NULL";
$this->db->order_by($order,'desc');

Answer 1

您可以使用此查询:

SELECT yourtable.*
FROM yourtable
ORDER BY
  CONCAT(user_country,user_state,user_city,user_area,user_pincode) IS NOT NULL,
  (user_country IS NULL) +
  (user_state IS NULL) +
  (user_city IS NULL) +
  (user_area IS NULL) +
  (user_pincode IS NULL)

请参阅 fiddle here .