oracle sql

 

TrainInfo_TrainInfoManageInside.aspx
sql语句：
gridview 显示
select t.*, org.organ_short_name, p.person_name, sp.specialty_name
  from (select 'a1b77d84-2099-420c-9fec-68392da75f36' as specialty_id
          from dual
        union
        select tsp.specialty_id
          from t_com_specialty tsp
         start with tsp.parent_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) empt,
       t_train_data t,
       t_com_organ_info org,
       t_com_person_info p,
       t_com_specialty sp
 where t.organ_id = org.organ_id
   and t.report_person = p.person_id
   and t.speciality_id = sp.specialty_id
   and t.month_plan_content_id is not null
   and sp.specialty_id = empt.specialty_id

treeview
根节点：
select *
  from (select *
          from t_com_specialty tsp
         start with parent_id = 'a1b77d84-2099-420c-9fec-68392da75f36'
        connect by prior tsp.specialty_id = tsp.parent_id) temp
 where temp.specialty_type_id = '11'

根据父亲查出子节点：
select t1.*, t2.specialty_name as parent_name
  from t_com_specialty t1
  left join t_com_specialty t2 on t1.parent_id = t2.specialty_id
 where t1.parent_id = '3c0cbc8d-2c21-4093-824f-8d2ac159cfc3'
   and (t1.used_flag = '1')

TrainCountInside.aspx
gridview统计:
select temp.*,
       tsp.specialty_name,
       p.organ_short_name,
       pi.person_name as 填表人
  from (select sp.speciality_id,
               sp.organ_id,
               sp.report_time,
               sp.report_person,
               count(*) as sum
          from (select *
                  from t_train_data d
                 where d.speciality_id in
                       (select specialty_id
                          from t_com_specialty s
                         start with s.specialty_id =
                                    'a1b77d84-2099-420c-9fec-68392da75f36'
                        connect by prior s.specialty_id = s.parent_id)) sp
         where sp.month_plan_content_id is not null
         group by sp.speciality_id,
                  sp.organ_id,
                  sp.report_time,
                  sp.report_person) temp,
       t_com_specialty tsp,
       t_com_organ_info p,
       t_com_person_info pi
 where temp.speciality_id = tsp.specialty_id
   and temp.organ_id = p.organ_id
   and temp.report_person = pi.person_id
 order by temp.report_time desc

TrainInfoSearchInside.aspx
select t.*, org.organ_short_name, p.person_name, sp.specialty_name
  from (select 'a1b77d84-2099-420c-9fec-68392da75f36' as specialty_id
          from dual
        union
        select tsp.specialty_id
          from t_com_specialty tsp
         start with tsp.parent_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) empt,
       t_train_data t,
       t_com_organ_info org,
       t_com_person_info p,
       t_com_specialty sp
 where t.organ_id = org.organ_id
   and t.report_person = p.person_id
   and t.speciality_id = sp.specialty_id
   and t.month_plan_content_id is not null
   and sp.specialty_id = empt.specialty_id

TrainCountList.aspx gridview统计
select t.*,
       y.organ_short_name as organ_short_name,
       s.specialty_name as speciality_name,
       sp.person_name as person_name
  from t_train_data      t,
       t_com_organ_info  y,
       t_com_specialty   s,
       t_com_person_info sp
 where t.organ_id = y.organ_id
   and s.specialty_id = t.speciality_id
   and sp.person_id = t.report_person
   and t.month_plan_content_id is not null
   and t.organ_id = 'CDT-JSXTPC'
   and t.speciality_id = 'b0c02bd0-578b-4292-a360-9b3c804018c0'
 order by t.speciality_id desc

TrainTeacherMsg.aspx

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
  left join t_com_organ_info o on o.organ_id = tt.organ_name
 where o.organ_name like '%0%'
    or tt.organ_name like '%0%'
 order by tt.birthday desc

CheckBoxList绑定
select sp.specialty_name, sp.specialty_id
  from (select *
          from t_com_specialty s
         start with s.specialty_id = 'a1b77d84-2099-420c-9fec-68392da75f36'
        connect by prior s.specialty_id = s.parent_id) sp
 where sp.used_flag = 1
   and sp.parent_id is not null

select p.*, sp.specialty_name
  from T_TRAIN_TEACHER_SPECIAL s, t_train_teacher p, t_com_specialty sp
 where s.teacher_id = p.teacher_id
   and s.speciality_id = sp.specialty_id
   and p.teacher_id = 'ed14e9f1-b0fd-45dc-a4b9-e8ec752ae6fb'

 

select tsp.*,
       sp.names,
       decode(tsp.state,
              '0',
              '草稿',
              '1',
              '会议提交',
              '2',
              '会议确定',
              '3',
              '会议结束') as t_sp
  from (select wm_concat(p.person_name) as names, mp.mm_id
          from t_ts_meeting_persons mp, t_com_person_info p
         where mp.person_id = p.person_id
         group by mp.mm_id) sp,
       t_ts_meeting_management tsp
 where tsp.mm_id = sp.mm_id

 

 

 

select tt.*, temp.specialty_name
  from (select t.teacher_id, wm_concat(tem.specialty_name) as specialty_name
          from (select y.specialty_name,y.specialty_id, ss.specialty_type_id
                  from (select x.*
                          from t_com_specialty x
                         start with x.parent_id is null
                        connect by prior x.specialty_id = x.parent_id) y
                 inner join (select t.specialty_type_id, t.specialty_id
                              from t_com_spe_typ_rel t) ss on (ss.specialty_id =
                                                              y.specialty_id)
                 inner join t_code_specialty_type p on (p.specialty_type_id =
                                                       ss.specialty_type_id)
                                                   and ss.specialty_type_id = '11'
                                                   and y.used_flag = 1) 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
 order by tt.birthday desc