MySQL 从第二张表中按点排序

Question

所以我有 MySQL 3 表、项目(在本例中是住宿属性，数据在下面进行了简化)、属性可能提供的便利设施，以及 amenities_index，它是项目 ID 和提供的每个便利设施的便利设施 ID 的列表.最终用户可以选择他们想要的任意数量的设施，我想按照与他们正在寻找的设施相匹配的设施数量的顺序返回结果。因此，如果他们搜索 3 种不同的设施，我希望列出的项目提供所有 3 种，然后是提供 2、1 的项目，最后是其余项目。我有一个查询，我认为它正在以正确的顺序获得结果，但我希望我也可以根据匹配返回一个点值，这就是我遇到麻烦的地方。当涉及到更复杂的查询时，我的 SQL 技能有点欠缺。

这是我的一个示例查询，它以正确的顺序返回结果:

SELECT * FROM items 
ORDER BY 
(
     SELECT count(*) AS points 
     FROM `amenities_index` 
     WHERE 
       (amenity_id = 1 || amenity_id = 2) 
       AND amenities_index.item_id = items.id
) DESC

下面是表格的结构。感谢您的帮助。

items table
id  name    
1   location 1
2   location 2
3   location 3
4   location 4

amenities table
id  name
1   fireplace
2   television
3   handicapped accessible
4   kitchenette
5   phone

amenities_index
item_id amenity_id
1       2
1       3
1       5
2       1
2       2
2       6
3       2
3       3
3       4
3       5

Answer 1

您想将表达式移动到 select 子句中:

SELECT i.*,
       (SELECT count(*) AS points 
        FROM `amenities_index` ai
        WHERE amenity_id in (1, 2) AND
              ai.item_id = i.id
       ) as points
FROM items i
ORDER BY points desc;

您还可以将此操作作为带聚合的join 查询:

SELECT i.*, ai.points
FROM items i join
     (select ai.item_id, count(*) as points
      from amenities_index ai
      where amenity_id in (1, 2)
     ) ai
     on ai.item_id = i.id
ORDER BY ai.points desc;

在大多数数据库中，我更喜欢这个版本而不是第一个版本。然而，MySQL 在 View 中允许第一个而不是第二个，因此在某些情况下它有一些奇怪的限制。