多对多关系选择和排序 [英] Many-to-many relationship select and order by

问题描述

我在两个表之间建立了多对多关系:人员和收藏夹.我有三列:

I have a many-to-many relationship between two tables: person and favorites. I have three columns:

person_id         int(8)
favorites_id      int(8)
is_main_favorite  enum('y','n')

为:

person_id | favorite_id | is_main_favorite

2         | 1           |   'y'
2         | 2           |   'n'
3         | 1           |   'n'
3         | 2           |   'n'
1         | 1           |   'y'
1         | 2           |   'y'

我正在使用PHP和MySQL.

I'm using PHP and MySQL.

如何检索具有(favorite_id 1和2在一起)的person_id并按具有更多is_main_favorite ='y'的person id排序结果，因此结果应为:

How I can retrieve person_id that have (favorite_id 1 and 2 together) and order the result by person id that have more is_main_favorite ='y', so the result should be as:

person_id 

1          (because he has favorite_id 1 and 2 and have two is_main_favorite = 'y')
2          (because he has favorite_id 1 and 2 and have one is_main_favorite = 'y')

推荐答案

可能与此类似:

SELECT
    a.person_id
FROM
    table AS a,
    table AS b
WHERE
    a.person_id = b.person_id AND
    a.favorite_id = 1 AND
    b.favorite_id = 2
ORDER BY
    ( IF( a.is_main_favorite = "y", 1, 0 )
      +
      IF( b.is_main_favorite = "y", 1, 0 ) ) DESC

顺便说一句:您可能希望在数据库中存储1/0而不是y/n，这样就不需要IF调用

By the way: You may want to store 1/0 instead of y/n in the database so that you won't need the IF call

这篇关于多对多关系选择和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！