mysql - 如何获取技能='Python'和技能='Javascript'的uid记录

Question

所以基本上我需要让人们的技能='Python'和技能='Javascript'但是当我运行这个查询时我得到一个空集：

SELECT uid 
FROM personal_hard_skills 
WHERE 
(skill='Python' AND level >) 
AND skill='Javascript';

我搜索了互联网，人们建议使用交叉查询的形式。这会与同一张桌子不同吗？

因此，对于上下文，如果我运行查询以获取此表中的所有内容：

mysql> SELECT uid FROM personal_hard_skills;
+----+-------+-----------------+------------+-------+---------------------+
| id | uid   | skill           | skill_code | level | timestamp           |
+----+-------+-----------------+------------+-------+---------------------+
| 25 | Dave  | Javascript      |          1 |  3.00 | 2019-07-18 19:50:19 |
| 26 | Dave  | Python          |          3 |  5.00 | 2019-07-18 19:50:19 |
| 27 | John  | Javascript      |          1 |  1.00 | 2019-07-18 19:51:23 |
| 28 | John  | Python          |          3 |  3.45 | 2019-07-18 19:51:23 |
| 29 | John  | Adobe Photoshop |          8 |  4.20 | 2019-07-18 19:51:23 |
| 30 | John  | HTML            |         64 |  2.50 | 2019-07-18 19:51:24 |
| 31 | John  | CSS             |         49 |  0.82 | 2019-07-18 19:51:24 |
| 35 | Helen | Javascript      |          1 |  4.35 | 2019-07-18 19:51:52 |
| 36 | Helen | Python          |          3 |  4.99 | 2019-07-18 19:51:52 |
| 37 | Helen | CSS             |         49 |  3.50 | 2019-07-18 19:51:52 |
+----+-------+-----------------+------------+-------+---------------------+
10 rows in set (0.03 sec)

但是当我运行这个查询时：

mysql> SELECT uid FROM personal_hard_skills WHERE skill='Python' AND skill='Javascript';
Empty set (0.03 sec)

标签： mysql

Answer 1

您可以使用聚合：

SELECT uid 
FROM personal_hard_skills 
WHERE skill IN ('Python','Javascript')
GROUP BY uid
HAVING COUNT(DISTINCT skill) = 2;

编辑：

SELECT uid
FROM personal_hard_skills 
WHERE (skill = 'Python' AND level >= 4.0) OR (skill = 'JavaScript' AND level >= 2.0)
GROUP BY uid
HAVING COUNT(DISTINCT skill) = 2;