我正在写一个旅行约会应用程序。
我的 table
表1:用户
id (key) | gender | pref_m | pref_f
------------------------------------
1 male 1 0
2 male 1 1表2:国家选择
id (key) | userid | countryid
------------------------------------
1 1 123
2 1 111
3 1 100
4 1 110
5 2 123
6 2 111
7 2 202
8 2 210
那么select语句必须做什么
输入:当前用户的用户名
输出(以逻辑方式):在所有想要和我旅行到相同国家并且想要和具有我的性别的人一起旅行的所有人中，选择用户名和匹配的国家/地区
(加入)显然，我只需要我所寻找性别的人。
由与我最匹配的国家/地区的人订购DESC。
到目前为止我所拥有的(警告:不多)
$sql = "SELECT userid,count(*) AS matches from countryselection";
$sql .= " WHERE countryid IN (SELECT countryid FROM countryselection WHERE userid = :userid) GROUP BY userid ORDER BY matches DESC;";这列出了所有想和我去同一个国家的人(以及我们有多少个国家)最后的笔记
我显然在性别选择方面挣扎。
不知道我是否做对了以我现有的方式存储用户选择的正确操作。
我可能在那里也需要一些指导。
显然-谢谢大家。
最佳答案
SELECT
us2.id, -- etc.
COUNT(cs2.countryid) as countries_in_common
FROM
countryselection cs1 -- let's gather user countries he want to visit
LEFT JOIN -- now let's find other users!
countryselection cs2 ON
(
cs2.userid <> :userid AND -- which are not him
cs2.countryid = cs1.countryid -- and want to visit same countries
)
INNER JOIN -- let's grab our user_data
users us1 ON
(
us1.id = cs1.userid
)
INNER JOIN -- and let's grab other user data!
users us2 ON
(
us2.id = cs2.userid
)
WHERE
cs1.userid = :userid AND -- finding our user countries he want to visit
-- final checks
(
(us1.pref_m = 1 AND us2.gender = 'male')
-- he is looking for male and second user is male
OR
(us1.pref_f = 1 AND us2.gender = 'female')
-- he is looking for female and second user is female
) AND
(
(us2.pref_m = 1 AND us1.gender = 'male')
OR
(us2.pref_f = 1 AND us1.gender = 'female')
)
GROUP BY
cs2.userid -- finally group by user_id
最好的事情是没有子查询，您可以通过多种方式轻松使用此查询。 (更改顺序，分组依据和使用聚合函数)
关于php - 我无法绕过SQL语句(脑子太小)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14553233/