Question

我有两个这样的表格:

表Person:

id name
---------
1  Jason
2  Dave
3  Amy
...

表Points

id points
----------
1  8
2  9
3  5
...

我试图将第一个表格中的“姓名”栏改为第二个表格,其依据是他们的孩子。预期的结果应当看一看:

id name   points
-----------------
1  Jason  8
2  Dave   9
3  Amy    5
...

我的特定问题是:

SELECT 
    id, 
    CASE 
        WHEN Person.id = Points.id 
            THEN Person.name 
    END AS name, 
    Points.points
FROM 
    Person, Points;

我获得的结果是:

id name   points
-----------------
1  Jason  8
2  NULL   9
3  NULL   5
...

我如何加以修改,以获得预期产出?

提前感谢你们的时间和帮助

Answer 1

您可使用<代码>INNER JOIN,以取得您的预期结果。

SELECT t1.id, t1.name, t2.points
FROM Person AS t1
INNER JOIN Points AS t2 ON t1.id = t2.id;

下面举一个完整的例子:db<>fiddle。

Answer 2

select p.id,p.name,poi.points
from person p
join point as poi on p.id=poi.id

你们是否需要这样做?

Answer 3

还在MySQL数据库中尝试了另外两件事。

Natural Join:
select * from person natural join points

Self Join: 
select * from person self join points using (id)

https://dbfiddle.uk/j9bYkFc4”rel=“nofollow noreferer”>Result

友情链接