几个视图

前不久做水晶报表时觉得用　视图先处理数据再输出也是一种不错的想法，不一定非要在报表工具里面处理
于是写了几个视图，发现都能达到报表查询的要求，不过这都是静态的，如果要动态查询当然要存储过程或者直接在报表工具里面处理数据
一
SELECT TOP 100 PERCENT COUNT(e.student_id) AS  学生总数, a.academy_name
FROM dbo.academy a LEFT OUTER JOIN  dbo.department b ON  a.academy_id = b.department_relt_academy_id
　　　　　　　　　　LEFT OUTER JOIN dbo.specialty c ON b.department_id = c.specialty_relt_department_id
LEFT OUTER JOIN dbo.class d ON c.specialty_id = d.class_relt_specialty_id
LEFT OUTER JOIN   dbo.student e ON d.class_id = e.student_relt_class_id
GROUP BY a.academy_id, a.academy_name
ORDER BY a.academy_name　　ORDER BY　排序
说明：１学院　Ａ　left outer join　　系　Ｂ　ON    A.ID=B_R_AID,以此类推，一直到　班级．学生个人．左联结，左边的表字段是空也会显示字段名
　　　２GROUP BY　分组查询

二
CREATE VIEW dbo.view_analysis
AS 
SELECT dbo.view_activity.name,
dbo.view_activity.activity_count,     
dbo.view_activity.person_count,
dbo.view_activity.activity_average_person,   
 
dbo.view_reward.reward_count,
dbo.view_reward.reward_person_count,     
dbo.view_reward.reward_average_person,    
dbo.view_total_person.academy_totle_student, 

  cast(
CAST(
CASE ISNULL(dbo.view_total_person.academy_totle_student, 0) 
 WHEN 0 THEN 0 ELSE
 ISNULL(dbo.view_activity.activity_average_person, 0) * 10000.00 / ISNULL(dbo.view_total_person.academy_totle_student, 0)  / 100.00
END AS decimal(20, 2)　
)
AS varchar)
+ '%' 　AS　 bili_activity, 

cast(
CAST(
CASE ISNULL(dbo.view_total_person.academy_totle_student, 0) 
WHEN 0 THEN 0
ELSE ISNULL(dbo.view_reward.reward_average_person, 0) * 10000.00 / ISNULL(dbo.view_total_person.academy_totle_student, 0)  / 100.00 END AS decimal(20, 2)
) AS varchar)
+ '%' AS bili_reward

FROM dbo.view_activity INNER JOIN
      dbo.view_reward ON 
      dbo.view_activity.name = dbo.view_reward.academy_name INNER JOIN
      dbo.view_total_person ON 
      dbo.view_reward.academy_name = dbo.view_total_person.academy_name

三
CREATE VIEW dbo.view_activity
AS SELECT TOP 100 PERCENT a.academy_name AS name, COUNT(DISTINCT g.activity_id)
      AS activity_count, COUNT(f.activity_student_id) AS person_count, 

      CAST(
CASE　ISNULL(COUNT(DISTINCT g.activity_id), 0) 
      WHEN 0 THEN 0
ELSE
ISNULL(COUNT(f.activity_student_id), 0)
      * 100.00 / ISNULL(COUNT(DISTINCT g.activity_id), 0) / 100.00 END AS decimal(20, 2)　)
      AS activity_average_person

FROM dbo.academy a
LEFT OUTER JOIN
      dbo.department b ON
      a.academy_id = b.department_relt_academy_id LEFT OUTER JOIN
      dbo.specialty c ON
      b.department_id = c.specialty_relt_department_id LEFT OUTER JOIN
      dbo.class d ON c.specialty_id = d .class_relt_specialty_id LEFT OUTER JOIN
      dbo.student e ON d .class_id = e.student_relt_class_id LEFT OUTER JOIN
      dbo.activity_student f ON
      e.student_id = f.activity_student_relt_student_id LEFT OUTER JOIN
      dbo.activity g ON f.activity_student_relt_activity_id = g.activity_id
GROUP BY a.academy_id, a.academy_name
ORDER BY a.academy_name