表Task: taskID,taskName
表Person: personID, personName
表TaskPerson: taskID,personID
每个任务有多个人担当,每个人同时也担当多人任务。
求教SQL文,查询1:每个任务对应的担当者
查询2:每个人承担的任务
谢谢!
1、创建测试表,
create table test_task(taskID varchar2(20),taskName varchar2(200));
create table test_person(personID varchar2(20),personName varchar2(200));
create table test_task_person(taskID varchar2(20),personID varchar2(200));
2、插入测试数据;
insert into test_task values (101,'Task_101');
insert into test_task values (102,'Task_102');
insert into test_task values (103,'Task_103');
insert into test_person values (1001,'Person_1001');
insert into test_person values (1002,'Person_1002');
insert into test_person values (1003,'Person_1003');
insert into test_task_person values (101,1001);
insert into test_task_person values (101,1002);
insert into test_task_person values (102,1001);
insert into test_task_person values (102,1003);
insert into test_task_person values (103,1001);
insert into test_task_person values (103,1003);
commit;
3、查询1,查询每个任务对应的担当者;select t.*, b.personName from test_task_person t, test_person b where t.personID = b.personID;
4、查询2,查询每个人承担的任务;select t.*, b.taskName from test_task_person t, test_task b where t.taskID = b.taskID;