mysql - 我怎样才能让这个SQL按照最相关的顺序排序？

Question

我有一组似乎有效的 SQL 语句，它们在下拉场景中运行。第一个是全包 AND，其中每个请求的选项都匹配。如果失败，它将转到基于 OR 的第二个 sql 调用。

有人可以向我解释一下或提供更好的陈述，其中第二层 OR 场景首先按最相关的顺序排序，这意味着大多数选项与第一个条目匹配并从那里向下？

或者，如果您有更好的方法来处理此搜索(可能只需一次调用)，那就更好了。

第一个基于 AND 的 SQL 调用:

SELECT 
    l.id,l.title,l.city 
FROM listings l 
JOIN listings_facilities_xref x ON l.id = x.listing_id 
WHERE l.property_type = '1' 
    AND x.facility_id = '1' 
    AND x.facility_id = '8' 
    AND x.facility_id = '54' 
    AND x.facility_id = '11' 
    AND l.city = 'Orlando' 
    AND l.state = '16' 
GROUP BY l.id

基于OR的第二层调用:

SELECT l.id,l.title,l.city 
FROM listings l 
JOIN listings_facilities_xref x ON l.id = x.listing_id 
WHERE l.property_type = '1' 
AND (
    x.facility_id = '1' 
    OR x.facility_id = '8' 
    OR x.facility_id = '54' 
    OR x.facility_id = '11'
) 
AND l.city = 'Orlando' 
AND l.state = '16' 
GROUP BY l.id 

我的表格布局如下:

mysql> describe listings;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(12)      | NO   | PRI | NULL    | auto_increment |
| owner_id      | int(12)      | NO   |     | NULL    |                |
| property_type | int(12)      | NO   |     | NULL    |                |
| title         | varchar(100) | NO   |     | NULL    |                |
| description   | text         | NO   |     | NULL    |                |
| yearbuilt     | int(12)      | NO   |     | NULL    |                |
| beds          | int(11)      | NO   |     | NULL    |                |
| baths         | int(11)      | NO   |     | NULL    |                |
| sleeps        | int(11)      | NO   |     | NULL    |                |
| sqfeet        | int(12)      | NO   |     | NULL    |                |
| lotsize       | int(12)      | NO   |     | NULL    |                |
| address1      | varchar(255) | NO   |     | NULL    |                |
| address2      | varchar(255) | NO   |     | NULL    |                |
| city          | varchar(100) | NO   |     | NULL    |                |
| state         | int(12)      | NO   |     | NULL    |                |
| zipcode       | varchar(50)  | NO   |     | NULL    |                |
| latitude      | varchar(50)  | NO   |     | NULL    |                |
| longitude     | varchar(50)  | NO   |     | NULL    |                |
| created       | int(12)      | NO   |     | NULL    |                |
| updated       | int(12)      | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

mysql> describe listings_facilities_xref;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(12) | NO   | PRI | NULL    | auto_increment |
| listing_id  | int(12) | NO   |     | NULL    |                |
| facility_id | int(12) | NO   |     | NULL    |                |
| category_id | int(12) | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

示例导入数据可在以下位置找到:

列表:https://pastebin.com/EeuaEFrR

listings_facilities_xref:https://pastebin.com/7WeHgEaE

Answer 1

我怀疑你想要:

SELECT l.id, l.title, l.city 
FROM listings l 
JOIN listings_facilities_xref x ON l.id = x.listing_id 
WHERE l.property_type = 1 AND
      x.facility_id IN (1, 8, 54, 11) AND
      l.city = 'Orlando' AND
      l.state = 16 
GROUP BY l.id, l.title, l.city
ORDER BY COUNT(*) DESC;