SQLite - 具有多个连接的复杂选择



我有以下示例表,其中包含相应的列:

PEOPLE: PERSON_ID, FIRST_NAME, LAST_NAME, GUARDIAN_ID
STATUS: PERSON_ID, STATUS

示例数据:

PEOPLE:
2345, George, Foreman, null
4567, Joseph, Galagar, 2345
5632, Jenner, Shooter, 2345
STATUS:
2345, DISPATCHED
4567, PENDING
5632, PENDING

鉴于上述情况,我希望从PEOPLE中检索PERSON_ID,其中FIRST_NAME是"乔治",也是那些人STATUS来自STATUS,最后是这些人中每个人的孩子数量(换句话说,PERSON_IDGUARDIAN_IDPEOPLE)。

我该怎么做?我在这方面非常生疏。这是一个不好的例子,但应该有助于解决我的实际问题。

鉴于上述情况,我应该得到以下结果:

2345, DISPATCHED, 2

试试这个:

CREATE TABLE PEOPLE(PERSON_ID INT, FIRST_NAME VARCHAR(25), LAST_NAME VARCHAR(25), GUARDIAN_ID INT);
INSERT INTO PEOPLE VALUES(2345, 'George', 'Foreman', null);
INSERT INTO PEOPLE VALUES(4567, 'Joseph', 'Galagar', 2345);
INSERT INTO PEOPLE VALUES(5632, 'Jenner', 'Shooter', 2345);
CREATE TABLE STATUS(PERSON_ID INT, STATUS VARCHAR(25));
INSERT INTO STATUS VALUES(2345, 'DISPATCHED');
INSERT INTO STATUS VALUES(4567, 'PENDING');
INSERT INTO STATUS VALUES(5632, 'PENDING');

使用查询,我们得到结果:

SELECT P.PERSON_ID
,MAX(S.STATUS)STATUS
,MAX(D.Counts)[No of Children]
FROM PEOPLE P
JOIN STATUS S ON S.PERSON_ID = P.PERSON_ID
JOIN(SELECT GUARDIAN_ID, COUNT(1)Counts FROM PEOPLE GROUP BY GUARDIAN_ID) D ON D.GUARDIAN_ID = P.PERSON_ID
WHERE P.FIRST_NAME = 'George'
GROUP BY P.PERSON_ID

输出:

PERSON_ID   STATUS      No of Children
2345        DISPATCHED  2

在 #SQL 小提琴中检查这一点

或者为了更简单并保存嵌套选择,

select p.PERSON_ID, max(s.STATUS) as status, 
sum (case when kids.person_id is null then 0 else 1 end) as num_children
from PEOPLE p
left join PEOPLE kids on kids.GUARDIAN_ID = p.PERSON_ID
join STATUS s on s.PERSON_ID = p.PERSON_ID
where p.FIRST_NAME = 'George'
group by p.PERSON_ID

最新更新