英文:
How can I make an intersection of two columns of two tables and get the entire rows?
问题
我有一个SQLite表。让我们称它为 people。
| 姓名 | 年龄 |
|---|---|
| 珍妮 | 50 |
| 约翰 | 80 |
| 爱丽丝 | 46 |
| 马克 | 25 |
| 哈里 | 33 |
我有另一个表 work。
| 姓名 | 工作编号 |
|---|---|
| 珍妮 | 1 |
| 阿曼达 | 2 |
| 菲利普 | 3 |
| 爱丽丝 | 4 |
| 杰克 | 5 |
| 哈里 | 6 |
我想要获取那些在 people 和 work 表中都有的行。但我不仅仅想要姓名。实际上，我一点也不关心姓名。我只需要它们来找到匹配的条目。我想要匹配行的 work.work_id 和 people.age 列。结果应该如下所示：
| 工作编号 | 年龄 |
|---|---|
| 1 | 50 |
| 4 | 46 |
| 6 | 33 |
两个表都可以有数百到数千个条目。
我还需要两者之间的差异，即 work 表中姓名不在 people 表中的行。但这应该可以通过我下面概述的第二种解决方案来解决。
我正在使用 Python3 并使用内置的 sqlite3 模块来处理这个问题。但这应该是一个纯粹的SQL问题，与SQL客户端无关。
我尝试过的方法
显而易见的选择是使用 INTERSECT：
SELECT Name FROM people INTERSECT SELECT Name FROM work
正如我所说，我需要 Name 列来找到表的交集，但我需要实际想要的内容，即 people.age 和 work.work_id 列，而不是 Name。
互联网引导我尝试子查询。
SELECT Name, Age FROM people WHERE Name IN (SELECT Name FROM work)
这是一种强大的技巧，但我还需要 work 表的 work_id 列，所以这不是一个解决方案。
这是否比较了 people 表中的每一行与 work 表的所有行？比较的次数是 SELECT Count(*) FROM people × SELECT Count(*) FROM work，还是有一种优化方法？
英文:
I have a SQLite table. Let's call it people.
| Name | Age |
|---|---|
| Jane | 50 |
| John | 80 |
| Alice | 46 |
| Mark | 25 |
| Harry | 33 |
I have another table work.
| Name | work_id |
|---|---|
| Jane | 1 |
| Amanda | 2 |
| Filip | 3 |
| Alice | 4 |
| Jack | 5 |
| Harry | 6 |
I'd like to get all rows whose name is in both people and work. But I do not only want the names. In fact I don't care about the names at all. I just need them to find the matching entries. I want the work.work_id and people.age columns of the matching rows. The result should look like this:
| work_id | age |
|---|---|
| 1 | 50 |
| 4 | 46 |
| 6 | 33 |
Both tables can have hundreds to thousands of entries.
I also need a difference of the two i.e. The rows of work whose name isn't in people. But this should be solvable with the second solution I have outlined below.
I am doing this in Python3 using the builtin sqlite3 module. But this should be a purely SQL problem independent of the SQL client.
What I've tried
The obvious choice is to do an INTERSECT:
SELECT Name FROM people INTERSECT SELECT Name FROM work_id
As I said, I need the Name columns to find the intersection of the tables but I need the rows themselves to get the things I actually want, people.age and work.work_id, not the Names.
The internet lead me to subqueries.
SELECT Name, Age FROM people where Name IN (SELECT Name FROM work)
This is a pretty powerful technique but I also need the work_id column of work so this isn't a solution.
Is this comparing each row in people with all rows of work? Is the number of comparisons SELECT Count(*) FROM people × SELECT Count(*) FROM work or is it somehow optimized?
答案1
得分: 1
你想从两个表中选择列，这意味着你需要使用 INNER JOIN：
SELECT w.work_id,
p.Age
FROM work AS w INNER JOIN people AS p
ON p.Name = w.Name;
对于 工作中名字不在人员中的行，可以使用 NOT IN：
SELECT *
FROM work
WHERE Name NOT IN (SELECT Name FROM people);
请参见演示。<br/>
英文:
You want to select columns from both tables and this means you need an INNER JOIN:
SELECT w.work_id,
p.Age
FROM work AS w INNER JOIN people AS p
ON p.Name = w.Name;
For the rows of work whose name isn't in people use NOT IN:
SELECT *
FROM work
WHERE Name NOT IN (SELECT Name FROM people);
See the demo.<br/>
答案2
得分: -1
SELECT work_id, Age
FROM people AS p
JOIN work AS w ON p.Name = w.Name;
SELECT name, work_id
FROM work
EXCEPT
SELECT name
FROM people
英文:
SELECT work_id, Age
FROM people AS p
JOIN work AS w ON p.Name = w.Name;
SELECT name, work_id
FROM work
EXCEPT
SELECT name
FROM people
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论