多对多关系选择和排序 [英] 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屋!
查看全文