mysql - 我的sql查询有两个表

Question

我有两个名为 person 和 person_sibling 的表。 person_sibling 表包含引用此人的同级的同级 ID(例如:此人 SANGEETHA 的同级是 SURESH)。我需要获取所有年龄大于 sibling 年龄的名字。表格数据如下所示

人员表:

NAME       AGE   FNAME         ID   GENDER

SANGEETHA   20  PONNURANGAM     2   FEMALE
SARANYA     22  CHOKALINGAM     3   FEMALE
KANNA       22  ALAGRI          4   MALE
LAVANYA     21  MUNISWAMI       1   FEMALE
SURESH      25  PONNURANGAM     20  MALE
SARALA      26  CHOKALINGAM     21  FEMALE
KARAN       20  ALAGRI          2   MALE
ARTHI       20  ALAGRI          25  FEMALE

person_sibling 表:

ID  SIBLING_ID

2   20
3   21
4   25
4   22
20  2
21  3
25  4
22  4
22  25
25  22

我已经尝试过这个:

SELECT name
FROM person p , person_sibling s
where p.id=s.sibling_id and p.age <=(select age from person where id=s.sibling_id)

但是我无法得到它。有人可以帮忙吗

Answer 1

SELECT p.name
FROM person p
inner join person_sibling s on p.id = s.id
inner join person sp on s.sibling_id = sp.id
where p.age > sp.age