找到每个人的任务

id	name
1 2	fh tm

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

id	person_id	content
1 2	2 2	tm1 works well tm2 works well

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

id	name	content
1 2 2	fh tm tm	NULL tm1 works well tm2 works well

示例1

输入:

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');

输出:

1|fh|None
2|tm|tm works well
2|tm|tm works well

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3372KB, 提交时间: 2021-09-26

select p.id,p.name,t.content from person p left join task t on p.id=t.person_id
order by p.id asc

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3376KB, 提交时间: 2021-08-08

select T.id, name, content
from person T
left join task T2 on T2.person_id = T.id
order by T.id

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3408KB, 提交时间: 2020-11-01

select p.id, p.name, t.content
from person p left join task t on p.id = t.person_id
order by p.id

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3480KB, 提交时间: 2021-09-10

select p.id,
       p.name,
       t.content
       /*case when content is null then null
       else content end as content*/
from person p
left join task t on p.id=t.person_id
order by 1

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3492KB, 提交时间: 2021-09-14

select p.id,name,content
from person p 
left join task t 
on p.id=t.person_id
order by p.id

上一题

下一题

详情