带有条件偏好的mysql选择

Question

我有一个包含4个字段的数据库，我想通过多个或多个条件从该数据库中搜索用户，问题是如何根据真或条件的数量对所选内容进行索引。字段是课程，学校，学院，城市，我想要的结果应该按照真实的数量或条件显示，这里是代码。

    <?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;
    }
    ?>

• 我只想要显示的结果的优先级应该是按真或conditions.In数的递减顺序，换句话说，按匹配值数目的降序排列。

Answer 1

请尝试以下查询：

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;

Answer 2

你可以做这样的事

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;