MySQL按第二表中的点数排序

So I have MySQL 3 tables, items (which in this case are lodging properties and the data is simplified below), amenities that the properties might offer, and amenities_index which is a list of item ids and amenity ids for each amenity offered. The end user can select any number of amenities they want and I want to return the results in order of the number of amenities that match what they are looking for. So, if they search for 3 different amenities, I want the items listed that offer all 3, then those that offer 2, 1 and finally the rest of the items. I have a query that I think is working for getting the results in the correct order, but I was hoping that I could also return a point value based on the matches, and that's where I'm running into trouble. My SQL skills are a bit lacking when it comes to more complex queries.

所以我有MySQL 3表格,项目(在这种情况下是住宿物业,数据在下面简化),物业可能提供的便利设施,以及amenities_index,它是项目ID列表和每个设施的便利设施ID。最终用户可以选择他们想要的任何数量的设施,我想按照他们想要的设施数量的顺序返回结果。因此,如果他们搜索3种不同的设施,我希望列出的项目提供全部3,然后提供2,1,最后提供剩余的项目。我有一个查询,我认为正在努力以正确的顺序获得结果,但我希望我也可以根据匹配返回一个点值,这就是我遇到麻烦的地方。当谈到更复杂的查询时,我的SQL技能有点缺乏。

Here is an example query I have that returns the results in the correct order:

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

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

And here is what the tables are structured like. Any help is appreciated.

以下是表格的结构。任何帮助表示赞赏。

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

1 个解决方案

#1

You want to move your expression into the select clause:

您希望将表达式移动到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;

You can also do this as a join query with aggregation:

您也可以使用聚合作为连接查询执行此操作:

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;

In most databases, I would prefer this version over the first one. However, MySQL would allow the first in a view but not the second, so it has some strange limitations under some circumstances.

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

#1