Sort multiple text fields in MYSQL according to information entered or not

 

I have a expert table with fields like expert_description , company_description , expert_website , expert_email , company_website .

These fields are not required, so they may be NULL .

When searching the tables, entries with most non- NULL fields should come first.

For example, there are four users:

  • user1 : who had entered info for all 5 fields
  • user2 : who had entered info for only 1 fields
  • user3 : who had entered info for only 4 fields
  • user4 : who had entered info for only 3 fields

    • The result should be

  • user1
  • user3
  • user4
  • user2

    • I suggest you to create a field in the user table that will store the count of fields filled.

    This column should be updated every time the user adds/remove a field.

    Then you simply can order by the "fieldcount" column.

    use this query: 

    SELECT user FROM your_table 
ORDER BY expert_description DESC, company_description DESC, 
expert_website DESC, expert_email DESC, company_website DESC;

    Thank you.

    You can use something like this, if the values of your empty fields are NULL. 

    SELECT expert_description, company_description, expert_website, expert_email, company_website, 
((expert_description IS NULL) + (company_description IS NULL) + (expert_website IS NULL) + (expert_email IS NULL) + (company_website IS NULL)) AS empty_count 
FROM experts 
WHERE 1 
ORDER BY empty_count;

    IS NULL will return 1 if the value of the field is NULL else it will return 0 .

    链接地址: http://www.djcxy.com/p/95054.html

    上一篇: 如何在多个用户中执行sql脚本（模式）

    下一篇: 根据输入或不输入的信息对MYSQL中的多个文本字段进行排序