无需多个类似操作符即可检索多个单个或引用的数据 [英] Retrieve data for multiple single or reference without like operator

问题描述

我有两个表，如下所示.在employee表中，而不是直接提及技能，我将其用作其他表的参考.

I have two tables, which looks like below. In the employee table instead mentioning the skills directly, I'm using as reference from another table.

问题:

select * from Employee where Skills = "1";

上面的查询将显示仅具有"Python"技能的员工记录.当然，它不会显示包含Python的其他组合，例如"1,2"(Python，Java).我该如何在不使用like运算符的情况下实现这一目标，因为如果我的技能分别为10、11、21，那不是问题.

The above query will show the employee record who has ONLY the "Python" skill. Certainly, it won't show the other combination in which Python is included, such as "1,2" (Python, Java). How can i achieve this without using like operator, since in case if i have 10, 11, 21 in skills it'll be problem isn't it.

如果您觉得这种使用参考的方式困难或不建议使用，请提出您的想法:-)

If you feel this way of using reference is difficult or not recommend, please suggest your idea :-)

员工表:

+-----+-------------+-------------+
| id  | Name        | Skills      |
+-----+-------------+-------------+
| 1   | Xyz         | 1,2,4       |
| 2   | Xyy         | 1,3         |
| 3   | Abc         | 1,2,3       |
| 4   | Asd         | 1           |
+-----+-------------+-------------+

技能表:

+-----+-------------+
| id  | SkillSet    |
+-----+-------------+
| 1   | Python      |
| 2   | Java        |
| 3   | C           |
| 4   | PHP         |
+-----+-------------+

推荐答案

请考虑以下内容

mysql> select * from employee ;
+------+------+--------+
| id   | name | skills |
+------+------+--------+
|    1 | xyz  | 1,2,4  |
|    2 | abc  | 1,3    |
|    3 | lmn  | 1,2,3  |
+------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from skillset ;
+------+----------+
| id   | skillset |
+------+----------+
|    1 | Python   |
|    2 | Java     |
|    3 | C        |
|    4 | PHP      |
+------+----------+
4 rows in set (0.00 sec)

此结构与您的结构相似，但是在这种情况下可以进行查询，但是我们可以使用find_in_set，但是效率很低，这里有几个示例

This structure is similar to yours and to do query in these situation we can however use find_in_set but thats pretty inefficient , here are few examples

mysql> select e.id,
e.name, 
group_concat(s.skillset) as skillset 
from employee e join skillset s 
on find_in_set(s.id,e.skills) > 0 
where find_in_set(1,e.skills) > 0 
 group by e.id ;
+------+------+-----------------+
| id   | name | skillset        |
+------+------+-----------------+
|    1 | xyz  | Python,Java,PHP |
|    2 | abc  | C,Python        |
|    3 | lmn  | Java,Python,C   |
+------+------+-----------------+
3 rows in set (0.00 sec)

select e.id,
e.name, 
group_concat(s.skillset) as skillset 
from employee e 
join skillset s on find_in_set(s.id,e.skills) > 0 
where find_in_set(2,e.skills) > 0  group by e.id ;
+------+------+-----------------+
| id   | name | skillset        |
+------+------+-----------------+
|    1 | xyz  | Python,PHP,Java |
|    3 | lmn  | C,Java,Python   |
+------+------+-----------------+

现在适当的规范化将使生活变得更加简单，并将具有以下关联表

Now a proper normalization would make life much simpler and will have the following association table

mysql> select * from employee_skills; 
+------------+----------+
| idemployee | idskills |
+------------+----------+
|          1 |        1 |
|          1 |        2 |
|          1 |        3 |
|          2 |        1 |
|          2 |        3 |
|          3 |        1 |
|          3 |        2 |
|          3 |        3 |
+------------+----------+

在这种情况下，现在执行查询会更加有效

Now doing query will be much more efficient in this case

mysql> select e.id,
e.name,
s.skillset from employee e 
join employee_skills es on es.idemployee = e.id 
join skillset s on s.id = es.idskills where s.id = 1 ;
+------+------+----------+
| id   | name | skillset |
+------+------+----------+
|    1 | xyz  | Python   |
|    2 | abc  | Python   |
|    3 | lmn  | Python   |
+------+------+----------+

使用最后一种方法，可以很容易地完成更复杂的计算.

Using the last approach more complex calculations could be done pretty easily.

这篇关于无需多个类似操作符即可检索多个单个或引用的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！