table account(id number, pid number, name varchar2(100))id 字段存放学生和老师的id.
pid 存放老师的id, 代表id是属于这个老师的学生. 现在要查出来都有哪些学生属于哪些老师, 并且可能存在某些学生不属于某个老师,某些老师不属于某个学生.
这个sql怎么写,我用的是oracle db,请大家多帮忙!!
pid 存放老师的id, 代表id是属于这个老师的学生. 现在要查出来都有哪些学生属于哪些老师, 并且可能存在某些学生不属于某个老师,某些老师不属于某个学生.
这个sql怎么写,我用的是oracle db,请大家多帮忙!!
select pid,wm_concat(id)
from account
group by pid
老师1 Id 老师name
属于这个老师1的学生id 学生name
.................................
老师2 Id 老师name
属于这个老师2的学生id 学生name
.................................
.................................
没有学生的老师id 老师name
.................................
.................................
没有老师的学生id 学生name
.................................
.................................
SELECT 1 ID ,NULL PID ,'A1234' NAME FROM DUAL UNION ALL
SELECT 2 A ,1 A ,'A1243' B FROM DUAL UNION ALL
SELECT 3 A ,1 A ,'A1423' B FROM DUAL UNION ALL
SELECT 4 A ,NULL A ,'A123' B FROM DUAL UNION ALL
SELECT 5 A ,4 A ,'A1323' B FROM DUAL UNION ALL
SELECT 6 A ,4 A ,'A1523' B FROM DUAL
)
SELECT ID,PID,NAME,ID C FROM TB WHERE PID IS NULL UNION ALL
SELECT ID,PID,NAME,PID C FROM TB WHERE PID IS NOT NULL
ORDER BY C,PID DESCID PID NAME C
1 A1234 1
2 1 A1243 1
3 1 A1423 1
4 A123 4
5 4 A1323 4
6 4 A1523 4
table a(id, pid, name)id 字段存放学生和老师的id.
pid 存放老师的id, 代表id是属于这个老师的学生.
现在我只要查出来所有的老师学生有关联的数据.结果如何,怎么写sql:老师1Id 老师name
老师1的学生id 学生name
老师2Id 老师name
老师2的学生id 学生name
.................................
.................................
with a as(
select 1001 id,11 pid,'tom' name from dual union all
select 1002,11,'hello' from dual union all
select 11,11,'teacher1' from dual union all
select 12,12,'teacher2' from dual union all
select 2001,12,'student1' from dual union all
select 2002,12,'student2' from dual)
--以上为提供数据的语句
select id,name
from a
order by pid,id
ID NAME
---------- --------
11 teacher1
1001 tom
1002 hello
12 teacher2
2001 student1
2002 student2
select 1001 id,11 pid,'tom' name from dual union all
select 1002,11,'hello' from dual union all
select 11,11,'teacher1' from dual union all
select 12,12,'teacher2' from dual union all
select 2001,12,'student1' from dual union all
select 2002,12,'student2' from dual union allselect 2010,13,'student1' from dual union all
select 2012,13,'student2' from dual union allselect 2017,2005,'student1' from dual union all
select 2018,2005,'student2' from dual
)--以上为提供数据的语句
select id,name
from a
order by pid,id
pid 存放老师的id, 代表id是属于这个老师的学生.
现在我只要查出来所有的老师学生有关联的数据.结果如何,怎么写sql:老师1Id 老师name
老师1的学生id 学生name
老师2Id 老师name
老师2的学生……
说的不明确吗...
with a as(
select 1001 id,11 pid,'tom' name from dual union all
select 1002,11,'hello' from dual union all
select 11,11,'teacher1' from dual union all
select 12,12,'teacher2' from dual union all
select 2001,12,'student1' from dual union all
select 2002,12,'student2' from dual union all
select null,10,'teacher3' from dual union all
select null,13,'teacher4' from dual union all
select 1004,null,'student5' from dual union all
select 2018,null,'student6' from dual
)
select * from (select id,name
from a
where id is not null
order by pid nulls last,id) --用nulls last 把没有老师的学生放到最后
union all --没有学生的老师
select id,name
from a
where id is null ID NAME
--------- --------
11 teacher1
1001 tom
1002 hello
12 teacher2
2001 student1
2002 student2
1004 student5
2018 student6
teacher3
teacher4
with a as(
select 1001 id,11 pid,'tom' name from dual union all
select 1002,11,'hello' from dual union all
select 11,11,'teacher1' from dual union all
select 12,12,'teacher2' from dual union all
select 2001,12,'student1' from dual union all
select 2002,12,'student2' from dual union all
select 44,null,'teacher3' from dual union all
select 45,null,'teacher4' from dual union all
select 1004,null,'student5' from dual union all
select 2018,null,'student6' from dual
)
select 44,null,'teacher3' from dual union all
select 45,null,'teacher4' from dual union all
-- 代表没有老师的学生....
select 1004,null,'student5' from dual union all
select 2018,null,'student6' from dual
from a
order by pid nulls last,id
from a
order by pid nulls last,id,account_type
select 1002,11,'hello' from dual union all
select 11,11,'teacher1' from dual union all
select 12,12,'teacher2' from dual union all
select 2001,12,'student1' from dual union all
select 2002,12,'student2' from dual union all
select 3001,102,'student1' from dual union all
select 3002,103,'student2' from dual union all
select 102,null,'teacher12123' from dual union all
select 103,null,'teacherewqeq4' from dual union all
select 44,null,'teacher3' from dual union all
select 45,null,'teacher4' from dual union all
select 1004,null,'student5' from dual union all
select 2018,null,'student6' from dual
)
select id,name
from a
order by pid nulls last,id
你用我的代码试下:
select id,name,fl from
(select id,pid,name,0 fl from account where pid is not null
union all
select id,pid,name,1 fl from account where pid is null)
order by pid;
fl=0 代表他肯定是老师
fl=1 代表他可能是老师可能四学生,在老师后面的肯定是学生