mysql 选择与条件首选项 - mysql SELECT with condition preference

mysql SELECT with condition preference

本文关键字：首选项条件选择 mysql | 更新日期: 2023-09-27

我有一个包含 4 个字段的数据库，我想通过多个 OR 条件从该数据库中搜索用户，问题是如何根据 true 或条件的数量索引选择。字段是课程，学校，学院，城市我希望结果应该根据 true 或条件的数量显示，这里是代码

    <?php
    $con = mysql_connect("xxx.xx.xxx.xxx","myusername","mypassword");
    if (!$con)
      {
     die('Could not connect: ' . mysql_error());
      }
    mysql_select_db("character", $con);
    $searchfrnd = mysql_query("SELECT * FROM user WHERE (course='physics' or  school='st thomas' or college='mit' or city='austin'))");
    WHILE($display = mysql_fetch_array($searchfrnd))
    {
    $frnd=$display[userid];
    echo $frnd;
    }
    ?>

我只希望显示的结果的优先级应该是真实OR条件数量的递减顺序。换句话说，按照匹配值数量的递减顺序

你可以做这样的事情

select (case when surname = 'jack' then 1 else 0 end +
        case when school ='st' then 1 else 0 end +
        case when college='mit' then 1 else 0 end +
        case when city='austin' then 1 else 0 end) as score,
        *
from 
user 
WHERE surname='jack' or  
      school='st'     or 
      college='mit' or 
      city='austin')
order by score desc;

尝试以下查询：

SELECT *
FROM user 
WHERE surname='jack' 
  OR  school='st' 
  OR college='austin' 
  OR city='mit'
ORDER BY (
  IF(surname='jack', 1, 0)
  + IF(school='st', 1, 0)
  + IF(college='austin' , 1, 0)
  + IF(city='mit', 1, 0)
) DESC;