日期:2014-05-18  浏览次数:20626 次

优化SQL语句
select tc.*,(select sum(studyTime) from Tb_My_Study_Course_Count_Backup where userId=1442 and courseId=tc.id) as studyCount  
from Tb_Course as tc 
where 1=1 and tc.id in (select courseId from Tb_My_Course where userId=1442) 
order by tc.id desc 


求这条语句的优化方法。
版本:MS-SQL Server 2005.

------解决方案--------------------
尽量不要用in
SQL code

select tc.*,(select sum(studyTime) from Tb_My_Study_Course_Count_Backup where userId=1442 and courseId=tc.id) as studyCount   
from Tb_Course as tc  
inner join Tb_My_Course as tb
on tc.id=tb.courseId
where tb.userId=1442
order by tc.id desc

------解决方案--------------------
探讨
select tc.*,(select sum(studyTime) from Tb_My_Study_Course_Count_Backup where userId=1442 and courseId=tc.id) as studyCount
from Tb_Course as tc
where 1=1 and tc.id in (select courseId from Tb_……

------解决方案--------------------
SQL code
select tc.*,sum(study.studyTime) as studyCount   
from Tb_Course as tc  
inner join (select courseId from Tb_My_Course where userId=1442) as tb
on tc.id=tb.courseId 
inner join (select courseId from Tb_My_Study_Course_Count_Backup where userId=1442 ) study on study.courseId=tc.id
order by tc.id desc

------解决方案--------------------
SQL code

select tc.*,a.studyCount
from Tb_Course as tc  
join 
(
select courseId,sum(studyTime) as studyCount  
from Tb_My_Study_Course_Count_Backup 
where userId=1442 and courseId  in (select courseId from Tb_My_Course where userId=1442) 
group by courseId
) a
on tc.id=a.courseid