>例如我有两个表
用户
----------------------------------
id | name | blah
----------------------------------
1 | Samy | stackoverflow
2 | jhon | some thing
----------------------------------
技能
---------------------------------------
id | user_id | skill_title | level
---------------------------------------
1 | 1 | php | good
2 | 1 | css | excellent
3 | 1 | photoshop | fair
4 | 2 | php | good
---------------------------------------
我像这样运行查询
SELECT * FROM users
INNER JOIN skills ON users.id = skills.user_id
WHERE ($skill_title[0] LIKE 'skills_title' AND
$skill_title[1] LIKE 'skills_title')
其中 $skill_title 是一个数组我需要的是选择具有所有这些技能的用户,即PHP,CSS如果我像上面那样查询,它永远不会带来数据,因为它将每条记录与所有数组元素进行比较,如果我替换并带有 或者它会起作用,但它不会带来具有所有技能的用户
有什么想法吗?
@joeshmo的答案可能是你最好的解决方案,但也值得一试。如果您有大量用户,但很少有用户拥有您正在寻找的技能,它可能会更快:
SELECT * FROM users
WHERE
id IN (SELECT user_id FROM skills WHERE skill_title = '$skills[0]')
AND id IN (SELECT user_id FROM skills WHERE skill_title = '$skills[1]')
...
你的PHP脚本可能看起来像这样:
$skills = array("php", "css", ... ); // replace ... with the skills you are looking for
$whereClause = array();
for ($i=0, $count=count($skills); $i < $count; $i++)
$whereClause[] = "id IN (SELECT user_id FROM skills WHERE skill_title = '{$skills[$i]}')";
$query = "SELECT * FROM users". (count($whereClause) > 0 ? " WHERE ". implode(" AND ", $whereClause) : "");
<小时 />另一种可能的解决方案是按照@joeshmo的建议使用多个INNER JOIN
s。通过这样做,您可以使其更小、更干净:
SELECT *
FROM
users u
INNER JOIN skills s1 ON u.id = s1.user_id AND s1.skill_title = '$skills[0]'
INNER JOIN skills s2 ON u.id = s2.user_id AND s2.skill_title = '$skills[1]'
...
所以你的PHP脚本可能看起来像这样:
$skills = array("php", "css", ... );
$joins = array();
for ($i=0, $count=count($skills); $i < $count; $i++)
$joins[] = "INNER JOIN skills s{$i} ON u.id = s{$i}.user_id AND s{$i}.skill_title = '{$skills[$i]}'";
$query = "SELECT * FROM users u ". implode(" ", $joins);
<小时 />我会尝试这两种解决方案,看看哪一种更适合您的数据集。
$skills = Array("php","css", ..... );
$query = " select u.id, u.name from users u left join skills s on u.id=s.user_id "
." where s.skill_title in ('"".implode($skills,"'",'"")."'") "
." group by s.user_id having count(s.id) = ".count($skills);
我想你想为你正在寻找的每项技能再次加入它。
SELECT * FROM users
INNER JOIN skills as first_skill ON users.id = first_skill.user_id
INNER JOIN skills as second_skill ON users.id = second_skill.user_id
WHERE ($skill_title[0] LIKE first_skill.skill_title AND
$skill_title[1] LIKE second_skill.skill_title)
查询的问题在于它只联接技能表一次。 您的查询的工作方式如下:
-----------------------------------------------------
id | name | blah | skill_title | level
-----------------------------------------------------
1 | Samy | stackoverflow | php | good
1 | Samy | stackoverflow | css | excellent
1 | Samy | stackoverflow | photoshop | fair
2 | jhon | some thing | php | good
-----------------------------------------------------
没有一行同时包含 css 和 php。 我的查询工作原理如下:
----------------------------------------------------------------------------------------------------------------
id | name | blah | first_skill.skill_title | first_skill.level | second_skill.skill_title | second_skill.level |
----------------------------------------------------------------------------------------------------------------
1 | Samy | stackoverflow | php | good | php | good |
1 | Samy | stackoverflow | php | good | css | excellent |
1 | Samy | stackoverflow | php | good | photoshop | fair |
... (there would be nine for Samy)
2 | jhon | some thing | php | good | php
----------------------------------------------------------------------------------------------------------------
第二行有php
和css
。 这是匹配的。
对于多达十个技能来说,这将是乏味的。 您可以改为执行多个查询。