MySQL关系查询

发布于 2024-11-17 20:37:46 字数 331 浏览 2 评论 0原文

我有两个表，第一个表：

users(id,name, birth_date)
skills(user_id,skill_name,skill_level)

我想选择具有 3 级 2 级技能的所有用户。

是否可以在一个查询中实现这一点？

示例：

user has
3,marcus,19/03/1989
4,anderson,08/02/1990

skills has
3,php,2
3,html,1
4,php,1

我想要的是：所有拥有 php 2 和 html 1 的用户。

原文

I have two tables, the 1st:

users(id,name, birth_date)
skills(user_id,skill_name,skill_level)

I want to select all users with 3 some skills on level 2.

its possible make this in one query ?

example:

user has
3,marcus,19/03/1989
4,anderson,08/02/1990

skills has
3,php,2
3,html,1
4,php,1

what i want is: all users who has php 2 AND html 1.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

假装爱人 2024-11-24 20:37:46

select *
   from users u join skills s on u.id=s.user_id 
   where skill_level=2 
   group by id 
   having count(*)>2

select *
   from users u join skills s on u.id=s.user_id 
   where skill_level=2 
   group by id 
   having count(*)>2

回复收藏 0 原文

自此以后，行同陌路 2024-11-24 20:37:46

好的。现在您已经更新了您的问题，答案变成“是的，可以做到，但您不一定应该这样做”。

首先，只是为了表明它可以完成：

SELECT u.* FROM users u 
INNER JOIN skills s1 ON (u.id = s1.user_id AND s1.skill_name = 'php') 
INNER JOIN skills s2 ON (u.id = s2.user_id AND s2.skill_name = 'html') 
WHERE s1.skill_level = 2 AND s2.skill_level = 1 GROUP BY u.id;

如果你一开始就解释了你想要什么，我会节省很多打字时间！ :)

现在你应该执行上述操作吗？它不是很漂亮，但原则是您针对两种不同的技能两次加入表，使用别名（s1 和 s2）来隐藏它是同一个表的事实。有时这是正确的方法。问题是，我怀疑你会在这方面有很多变化，有时你想找到具有不同级别技能的人，有时只有一种技能，等等。您可能会发现编写自动生成这些查询的代码有点复杂，而且不一定能很好地扩展。

您需要阅读数据库规范化以更好地设计您的表。而且您还应该为技能表设置一个 id 字段，然后您可以在需要时更轻松地使用子查询。

Okay. Now that you've updated your question a bit more, the answer becomes "yes, it can be done, but you shouldn't necessarily do it like that".

Firstly, just to show that it can be done:

SELECT u.* FROM users u 
INNER JOIN skills s1 ON (u.id = s1.user_id AND s1.skill_name = 'php') 
INNER JOIN skills s2 ON (u.id = s2.user_id AND s2.skill_name = 'html') 
WHERE s1.skill_level = 2 AND s2.skill_level = 1 GROUP BY u.id;

Would have saved me quite a bit of typing if you'd explained what you wanted in the beginning! :)

Now should you do the above? It's not very pretty, but the principle is that you join to the table twice for both different skills, using aliases (s1 and s2) to hide the fact that it's the same table. Sometimes this is the right approach. The trouble is that I suspect you'll have loads of variations on this where you want to sometimes find people with lots of skills at different levels, sometimes only one, etcetera. You might find writing the code to automatically generate those queries slightly complicated and it wont necessarily scale well.

You need to read up on database normalization to better design your tables. And you should also have an id field for the skills table and then you can more easily use sub-queries when you need to.

回复收藏 0 原文

三月梨花 2024-11-24 20:37:46

要检索 HTML 技能为 1 且 PHP 技能为 2 的用户列表：

SELECT u.name
FROM users u JOIN skills s1 ON u.id = s1.user_id AND s1.skill = 'php'
JOIN skills s2 ON u.ud = s2.user_id AND s2.skill = 'html'
WHERE s1.skill_level = 2
AND s2.skill_level = 1

如果您想要所有用户同时具备 HTML 和 PHP，则可以完全删除 WHERE 子句。如果您希望所有用户的 PHP 技能至少为 2，您只需将该子句更改为

s1.skill_level >= 2

To retrieve a list of users who have an HTML skill of 1 and a PHP skill of 2:

SELECT u.name
FROM users u JOIN skills s1 ON u.id = s1.user_id AND s1.skill = 'php'
JOIN skills s2 ON u.ud = s2.user_id AND s2.skill = 'html'
WHERE s1.skill_level = 2
AND s2.skill_level = 1

If you wanted all of the users with both HTML and PHP you could drop the WHERE clause entirely. If you wanted all of the users with a PHP skill of at least 2, you'd just change the clause to