SQL258 找到每个人的任务

题目描述

有一个person表，主键是id，如下:

id	name
1	fh
2	tm

有一个任务(task)表如下，主键也是id，如下:

id	person_id	content
1	2	tm works well
2	2	tm works well

请你找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序，输出情况如下:

id	name	content
1	fh	null
2	tm	tm works well
2	tm	tm works well

SQL Schema

drop table if exists person;
drop table if exists task;
CREATE TABLE `person` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `task` (
`id` int(4) NOT NULL,
`person_id` int(4) NOT NULL,
`content` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO person VALUES
(1,'fh'),
(2,'tm');

INSERT INTO task VALUES
(1,2,'tm works well'),
(2,2,'tm works well');

答案

SELECT p.id, p.name, t.content
FROM person p
         LEFT JOIN task t on p.id = t.person_id
ORDER BY p.id;