select ct.ct_id,sc.scid,tea_name,cau_name,ct_time from SetCause as sc,CauseTime as ct,CauseInfo as ci,TeaAccount as ta where ct.scid = sc.scid and sc.cau_id = ci.cau_id and sc.tea_id = ta.tea_id  and ci.cau_id not in (select ci.cau_id from SetCauseTime as sct,SetCause as sc,CauseTime as ct,CauseInfo as ci where sct.ct_id = ct.ct_id and ct.scid = sc.scid and sc.cau_id = ci.cau_id and sct.stu_id = @stu_id) and ci.cau_name like '%'+@causeValue+'%'

视图 索引

------解决方案--------------------
这么多表，视图处理

------解决方案--------------------
select ct.ct_id,sc.scid,tea_name,cau_name,ct_time from  
SetCause as sc,
CauseTime as ct,
(Select * From CauseInfo Where cau_name like '%'+@causeValue+'%')[ci],
TeaAccount as ta ,
(Select * From SetCauseTime Where stu_id <> @stu_id)[sct]
where  
sct.ct_id = ct.ct_id and
ct.scid = sc.scid and  
sc.cau_id = ci.cau_id and  
sc.tea_id = ta.tea_id
------------------------------