优化（1）大家一起来看看好么~？

[rabbitisme]

select a.IDCARD, a.sc_id, a.username,a.sex, a.birth,a.stature,a.avoir,a.marry,
a.faddress,a.PePhoto,a.faddress,A.native,b.ask_type,b.pay,b.dwell,
B.region1,B.region2,B.region3,c.ability_cert,c.university,c.college,
c.class_name,c.student_no,c.enroll_time,c.specname1,c.specname2, c.study_story,
c.px_story,d.chinese_degree,d.gd_degree,d.ab_other,d.ab_desc,f.working,
f.department,f.job,f.experience,f.work_desc,f.work_resume,g.tel1,
g.tel2,g.email,g.pc,g.address,g.bp,G.work_site,g.http,h.l_sight,h.r_sight,
h.family_mem,h.edc_length,h.sc_commend,

(select last_update from pe_log where pe_usename=@UserName) as last_update,

(select aa.nation from sy_nation aa,pe_base bb where bb.pe_usename=@UserName and aa.id=bb.nation) as nation,
/*
类似上面的查询语句改成这样是不是好些？
(select aa.nation from sy_nation aa left join pe_base bb on aa.id=bb.nation where bb.pe_usename=@UserName) as nation
*/
(select aa.Polity from Sy_Polity aa,pe_base bb where bb.pe_usename=@UserName and aa.id=bb.Polity) as Polity,
(select aa.TechLevel from Sy_TechLevel aa,pe_base bb where bb.pe_usename=@UserName and aa.id=bb.Tech_Level) as TechLevel,
A.residence,
(select aa.position from sy_position aa,Pe_Condition bb where bb.pe_usename=@UserName and aa.id=bb.position1) as position1,
(select aa.position from sy_position aa,Pe_Condition bb where bb.pe_usename=@UserName and aa.id=bb.position2) as position2,
(select aa.position from sy_position aa,Pe_Condition bb where bb.pe_usename=@UserName and aa.id=bb.position3) as position3,
(select aa.edclevel from sy_edcLevel aa,pe_edc bb where bb.pe_usename=@UserName and aa.id=bb.edc_level) as edc_level,
---------------------------------专业，根据在主站和了站注册的不同，选择不同的专业表
(
case when a.sc_id='0' or a.sc_id is null
then
(select aa.specialty from sy_specialty aa,pe_edc bb where Rtrim(ltrim(bb.pe_usename))= Rtrim(ltrim(@UserName)) and aa.id=bb.specialty1)
else
(select aa.spec_name from Sc_Specialty aa,pe_edc bb where Rtrim(ltrim(bb.pe_usename))= Rtrim(ltrim(@UserName)) and aa.id=bb.specialty1)
END
)
as specialty1,
(
case when a.sc_id='0' or a.sc_id is null
then
(select aa.specialty from sy_specialty aa,pe_edc bb where Rtrim(ltrim(bb.pe_usename))= Rtrim(ltrim(@UserName)) and aa.id=bb.specialty2)
else
(select aa.spec_name from Sc_Specialty aa,pe_edc bb where Rtrim(ltrim(bb.pe_usename))= Rtrim(ltrim(@UserName)) and aa.id=bb.specialty2)
END
)
as specialty2,
--－－－－－－－－－－－－－－－－－－end
(select aa.LangKind from Sy_LangKind aa,pe_ability bb where bb.pe_usename=@UserName and aa.id=bb.f_lang1) as f_lang1,
(select aa.LangKind from Sy_LangKind aa,pe_ability bb where bb.pe_usename=@UserName and aa.id=bb.f_lang2) as f_lang2,
(select aa.degree from Sy_Degree aa,pe_ability bb where bb.pe_usename=@UserName and aa.id=bb.degree1) as degree1,
(select aa.degree from Sy_Degree aa,pe_ability bb where bb.pe_usename=@UserName and aa.id=bb.degree2) as degree2,
(select aa.degree from sy_Degree aa,pe_ability bb where bb.pe_usename=@UserName and aa.id=bb.computer) as computer,
(select pe_desc from pe_desc where pe_usename=@UserName) as pe_desc
from pe_base a left join pe_condition b
on a.pe_usename=b.pe_usename
left join pe_edc c on a.pe_usename=c.pe_usename
left join pe_ability d on a.pe_usename=d.pe_usename
left join pe_work f on a.pe_usename=f.pe_usename
left join pe_contact g on a.pe_usename=g.pe_usename
left join pe_batch h on a.pe_usename=h.pe_usename
where
a.pe_usename=@UserName


除了要检查表的索引外，这段查询语句（放在存储过程里面的）还应该怎么改进，请大家教教我，

[netcoder]

说说你的表结构以及每个表存放的信息
然后说说你要实现的查询功能

[victorycyz]

同意一楼，最好再给些示例数据。

[rabbitisme]

涉及的表太多了，根本不能一一举例，我现在只能现在语句上优化一些。