mysql：连接表+ 使用 AND 样式查询查找记录，而不是 OR
注意：使用MySQL 4.0，这意味着没有子查询（目前）。
我有 2 个表：
- 一个“user_details”表
- 一个“skills”表，其中包含 user_id 和“skill_id”，它映射到其他地方定义的一组预定义技能。
当前的查询允许管理员通过选择技能来搜索用户，并且查询以 OR 方式工作，例如：
LEFT JOIN skills
ON (ud.user_id = skills.user_id)
WHERE skills.skill_id in (51, 52, 53, 54, 55)
GROUP BY ud.user_id
这返回太多记录，因此我希望此搜索字段以 AND 方式工作，其中用户必须拥有 ALL搜索中要返回的选定技能。
如果子查询是最佳选择，也许可以升级 MySQL。
编辑：与分组依据、计数、拥有等有关。您可以通过命令限制分组，并要求返回多少匹配行吗？ （例如本例中为 5）。
编辑2：测试：
HAVING COUNT( * ) > 5
Note: Using MySQL 4.0, which means no subqueries (at present).
I have 2 tables:
- A "user_details" table
- A "skills" table, which has the user_id and a "skill_id", which maps to a predefined set of skills defined elsewhere.
The current query allows an admin to search for users by selecting skills, and the query works in an OR fashion, eg:
LEFT JOIN skills
ON (ud.user_id = skills.user_id)
WHERE skills.skill_id in (51, 52, 53, 54, 55)
GROUP BY ud.user_id
This returns too many records and thus I want this search field to work in an AND fashion, where a user must have ALL the selected skills to be returned in the search.
It may be possible to get MySQL upgraded if subqueries are the best option.
edit: Something to do with group by, count, having etc. Can you restrict a group by command with a requirement on how many matched rows you return? (eg 5 in this example).
edit2: Testing out:
HAVING COUNT( * ) > 5
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您不需要子查询或联接。
You don't need a subquery or a join.
只需添加更多单连接即可。
INNER JOIN 技能 s ON u.id - us.userid AND Skill_id = $s1
内连接技能 s ON u.id - us.userid AND Skill_id = $s2
内连接技能 s ON u.id - us.userid AND Skill_id = $s3
内连接技能 s ON u.id - us.userid AND Skill_id = $s4
等等。
需要加入所有这些。 您不需要任何组或计数。
Just add more single joins.
INNER JOIN skills s ON u.id - us.userid AND skill_id = $s1
INNER JOIN skills s ON u.id - us.userid AND skill_id = $s2
INNER JOIN skills s ON u.id - us.userid AND skill_id = $s3
INNER JOIN skills s ON u.id - us.userid AND skill_id = $s4
etc.
It will be required to join to them all. You don't need any groups or counts.
如果您希望在同一查询中包含用户详细信息，您只需执行以下操作：
If you wanted the user details included in the same query you could simply do the following: