dtsc

y112102

于 2011-10-11 10:04:18 发布

阅读量586

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： join

本文链接：https://blog.youkuaiyun.com/y112102/article/details/6861569

Oracle 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询用于获取教师及其所教授专业的详细信息。通过使用多个表连接、递归查询和分组函数，实现了从不同表中提取并整合数据的目标。此查询展示了在关系型数据库中进行高级数据检索的技术细节。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select tt.*, temp.specialty_name
from (select t.teacher_id,
               wm_concat(tem.specialty_name) as specialty_name
          from (select tsp.specialty_name, tsp.specialty_id
                  from t_com_specialty tsp
                 start with tsp.specialty_id =
                            (select specialty_id
                               from t_com_specialty ss
                              where ss.specialty_id =
                                    'a1b77d84-2099-420c-9fec-68392da75f36')
                connect by prior tsp.specialty_id = tsp.parent_id) tem
         inner join (select o.speciality_id, o.teacher_id
                      from T_TRAIN_TEACHER_SPECIAL o) t_sp on (t_sp.speciality_id =
                                                           tem.specialty_id)
         inner join T_TRAIN_TEACHER t on(t.teacher_id=t_sp.teacher_id)
         group by t.teacher_id) temp
         left join T_TRAIN_TEACHER tt on temp.teacher_id = tt.teacher_id