问题背景：
给学校的网站添加一个问卷调查系统，新加功能主要包含三张表
Survey 问卷表（用来存储问卷名）
Survey_Question 问卷问题表（用来存储问卷问题）
Survey_Answer 问卷答案表（用来存储问卷的答案）
系统是用ASP来写的，我应该算是二次开发吧（本人工作经验尚浅如果名词表达错误请见谅，到现在还是很佩服做这个系统的人，竟然能用ASP写出一个这么复杂的系统，但维护性。。。真的纠结）
由于本人没学过ASP。所以一开始还是比较纠结的
最终耗时二个星期的时间完成了以下功能：
1.问卷的添加、修改、删除
2.问卷问题的添加、修改、删除
3.开启问卷调查、关闭问卷调查（每次只允许开启一张问卷）
4.限制个别专业的学生填写问卷
5.问卷的统计（包含按专业统计问卷、按系统计问卷、按问题统计问卷、查看不满意问卷的不满意原因）PS：问题就出在按专业统计问卷，当然其他统计也应该有类似的问题只是结果较少所以没有出现这么严重的问题。
(不知道小弟的工作效率怎么样。。。。)
正题（问题出现）：
Survey_Answer表的表结构是
ID Student_No SQ_Id(问题ID) A_Content( 问题答案) A_Reason(不满意问题的理由)
当时统计各专业问卷数据的时 是想着 东西出来就好了所以写SQL的时候也没有太在意。能拼出来就很高兴了
当时的SQL 是这么写的(简写)（具体的SQL还是比较复杂的。也可能是我写复杂了，我在文章结尾会附上完整SQL语句）
Select 系，专业，满意，不满意，一般，基本满意，满意度 ,
from (
left jion (从Survey_Answer里查询出各专业满意的数量
left join (从Survey_Answer里查询出各专业不满意的数量)
。。。。。。。。。。。。）)
就这样一直将各专业的满意数量、不满意数量等一个个查出来然后left join 或right join 进去
当时的想法如下图
将查询的结果一个个拼进去。
操作引发的结果：
当有2000个学生填写后 survey_answer表的数据达到了2W的量（非常小的数据，一个学生填写11个问题）
当进行查询的时候发现查询专业数据统计时竟然需要40秒到1分钟的时间，
纠结啊
但是放到SQL2008里面则完全不会出现这样的问题
一样的查询语句，一样的数据 查询秒查。(SQL2008真心做了不少优化)
解决办法：
一开始考虑学习原系统的办法（建立专门建一张表用来存统计数据，然后加一个更新按钮，点击更新按钮就更新数据，否则直接查询该表的数据）
可是这种方法。。。显然是很。。。个人认为。。。低级的方案。。
自然不能考虑了。
果断修改SQL语句，一步步剥离子查询。然后分析，最终将查询缩短到了3-5秒
具体操作是这样的
Select 系，专业，满意，不满意，一般，基本满意，满意度 ,
from
(从Survey_Answer里查询出各专业满意的数量) as t1,
(从Survey_Answer里查询出各专业不满意的数量)as t2,
......
通过这样的拼接来查询
因为有些数据为0，但你又必须查询出来，所以我才想到了用join 来做的
问题分析：
个人分析了下应该是这样的原因
因为学校有37个系
所以在使用join 语句的时候。。。4种结果（满意，不满意，基本满意、一般）应该会做
37*37*37*37 大约200W次吧。。效率自然低下了
而统计系的时候应该是8*8*8*8 的关系所以还不是特别明显，只是感觉有点慢
（本人的分析可能是有误，如果知道正确原因的大牛请指点，小弟万分感谢）
而用了新的SQL语句后。。
效率只是 “+”的关系了，，自然就快了
总结：
小小的总结一下。。。以后写SQL要注意呀。。。。
------------华丽的分割线-------------
老SQL完整语句：
1 Select isnull((T5.Satisfaction_Count+T5.BSatisfaction_Count)*1.0/nullif(T5.General_Count+T5. BSatisfaction_Count+T5.UnSatisfaction_Count+T5.Satisfaction_Count,0),0) as lastinfo, 2 T6.Faculty_Name as Faculty_Name, T5.Subject_Name as Subject_Name, T5. UnSatisfaction_Count as UnSatisfaction_Count , T5.Satisfaction_Count as Satisfaction_Count, T5.BSatisfaction_Count as BSatisfaction_Count, T5.General_Count as General_Count, T5.Survey_Count as Survey_Count 3 4 from ( 5 select * from Faculty where Faculty.Faculty_Flag=1)as T6 6 -------------------------t5 7 left join 8 ( 9 Select [Subject].Subject_Faculty as Faculty_Id, isnull([Subject].Subject_Name,T4.Subject_Name )as Subject_Name, isnull(T4. UnSatisfaction_Count,0)as UnSatisfaction_Count , 10 isnull(T4.Satisfaction_Count,0)as Satisfaction_Count, isnull(T4.BSatisfaction_Count,0)as BSatisfaction_Count, isnull(T4.General_Count,0)as General_Count, isnull(T4.Survey_Count,0)as Survey_Count 11 from [Subject] 12 -------------------------t4 13 left join 14 ( 15 16 Select isnull(Survey.Subject_Name,T3.Subject_Name )as Subject_Name, (T3. UnSatisfaction_Count) , (T3.Satisfaction_Count) , (T3.BSatisfaction_Count) , (T3.General_Count), Survey.Survey_Count 17 from ( 18 select [Subject].Subject_Name as Subject_Name, count(DISTINCT Survey_Answer.Student_No) as Survey_Count from Student,VClass,[Subject],Survey_Answer,Survey,Survey_Question 19 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 20 and Survey_Answer.Student_No=Student.Student_No and VClass.VClass_Years='2013' and Survey.ID=1 Group by [Subject].Subject_Name 21 ) as Survey 22 -------------------------t3 23 Right join 24 ( 25 select isnull(UnSatisfaction.Subject_Name,T2.Subject_Name )as Subject_Name, isnull (UnSatisfaction.UnSatisfaction_Count,0) as UnSatisfaction_Count, T2.Satisfaction_Count, T2.BSatisfaction_Count, T2.General_Count From (SELECT [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) as UnSatisfaction_Count 26 from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 27 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id and VClass.VClass_Years='2013' and Survey.ID=1 and A_Content='不满意' Group by Subject_Name 28 ) as UnSatisfaction 29 ------------------------T2 30 Right join 31 ( 32 Select isnull(General. Subject_Name,T1.Subject_Name )as Subject_Name, isnull (General.General_Count,0) as General_Count, T1.Satisfaction_Count, T1.BSatisfaction_Count 33 From ( 34 SELECT [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) as General_Count 35 from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 36 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id and VClass.VClass_Years='2013' and Survey.ID=1 and A_Content='一般' Group by Subject_Name 37 ) as General 38 39 40 -------------------------T1 41 right join 42 ( 43 select isnull(BSatisfaction.BSatisfaction_Count,0) as BSatisfaction_Count, isnull(Satisfaction. Satisfaction_Count,0) as Satisfaction_Count, isnull(Satisfaction. Subject_Name,BSatisfaction.Subject_Name )as Subject_Name 44 from ( 45 SELECT [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) as BSatisfaction_Count 46 from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 47 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id and VClass.VClass_Years='2013' and Survey.ID=1 and A_Content='基本满意' Group by Subject_Name 48 ) as BSatisfaction 49 50 right join 51 ( 52 SELECT [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) as Satisfaction_Count 53 from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 54 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id and VClass.VClass_Years='2013' and Survey.ID=1 and A_Content='满意' Group by Subject_Name 55 ) as Satisfaction 56 57 on Satisfaction.Subject_Name=BSatisfaction.Subject_Name )as T1 58 ------------------------------T1end 59 60 on T1.Subject_Name=General.Subject_Name )as T2 61 -----------------------------T2END 62 63 on T2.Subject_Name=UnSatisfaction.Subject_Name )As T3 64 65 ---------------------------------t3end 66 67 ON T3.Subject_Name=Survey.Subject_Name )As T4 68 69 -------------------------t4end 70 ON T4.Subject_Name=[Subject].Subject_Name )as T5 71 -------------------------t5end 72 on T5.Faculty_Id=T6.Faculty_Id
------------华丽的分割线-------------
新SQL完整语句
1 select Faculty_Name,T1.Subject_Name,Survey_Count, UnSatisfaction_Count,BSatisfaction_Count ,Satisfaction_Count ,General_Count , 2 isnull((Satisfaction_Count+BSatisfaction_Count)*1.0/nullif(General_Count+BSatisfaction_Count+UnSatisfaction_Count+Satisfaction_Count,0),0) as lastinfo 3 from ( 4 select [Subject].Subject_Name ,isnull(Survey_Count,0)as Survey_Count 5 from [Subject] 6 left join 7 8 (select [Subject].Subject_Name as Subject_Name, 9 count(DISTINCT Survey_Answer.Student_No) as Survey_Count 10 from Student,VClass,[Subject],Survey_Answer,Survey,Survey_Question 11 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass 12 and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 13 and Survey_Answer.Student_No=Student.Student_No and VClass.VClass_Years='2013' 14 and Survey.ID=1 Group by [Subject].Subject_Name )as T1 15 on T1.Subject_Name=[Subject].Subject_Name 16 )as T1, 17 18 19 (select [Subject].Subject_Name ,isnull(UnSatisfaction_Count,0) as UnSatisfaction_Count 20 from [Subject] 21 left join 22 (SELECT [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) 23 as UnSatisfaction_Count from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 24 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass 25 and Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No 26 and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 27 and VClass.VClass_Years='2013' and Survey.ID=1 and A_Content='不满意' 28 Group by Subject_Name)as T1 29 on T1.Subject_Name=[Subject].Subject_Name 30 ) as T2, 31 32 33 (select [Subject].Subject_Name ,isnull(BSatisfaction_Count,0) as BSatisfaction_Count 34 from [Subject] 35 left join 36 (SELECT [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) 37 as BSatisfaction_Count from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 38 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass 39 and Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No 40 and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 41 and VClass.VClass_Years='2013' and Survey.ID=1 and A_Content='基本满意' 42 Group by Subject_Name )as T1 43 on T1.Subject_Name=[Subject].Subject_Name 44 )as T3, 45 46 47 48 (select [Subject].Subject_Name ,isnull(Satisfaction_Count,0) as Satisfaction_Count 49 from [Subject] 50 left join 51 (SELECT [Subject].Subject_Name 52 as Subject_Name, count(Survey_Answer.ID) as Satisfaction_Count 53 from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 54 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass 55 and Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No 56 and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 57 and VClass.VClass_Years='2013' and Survey.ID=1 and A_Content='满意' Group by Subject_Name )as T1 58 on T1.Subject_Name=[Subject].Subject_Name 59 60 )as T4, 61 62 63 64 (select [Subject].Subject_Name ,isnull(General_Count,0) as General_Count 65 from [Subject] 66 left join 67 (SELECT [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) 68 as General_Count from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 69 where VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass 70 and Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No 71 and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 72 and VClass.VClass_Years='2013' and Survey.ID=1 and A_Content='一般' Group by Subject_Name 73 )as T1 74 75 on T1.Subject_Name=[Subject].Subject_Name 76 77 )as T5, 78 (select Faculty.Faculty_Name ,Subject_Name 79 from [Subject],Faculty 80 where Faculty.Faculty_Id =[Subject].Subject_Faculty)as T6 81 82 83 where T6.Subject_Name=T1.Subject_Name and 84 T6.Subject_Name=T2.Subject_Name and 85 T6.Subject_Name=T3.Subject_Name and 86 T6.Subject_Name=T4.Subject_Name and 87 T6.Subject_Name=T5.Subject_Name
后记：
问卷调查使用一周后 已经有2300人填写过了
下面附带一些统计的效果图