SQL 多对多查询

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

select * from book a join (select cid,count(1) as [count] from category group by cid) b where a.id=b.cid

---