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