如下面一个视图写法 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
CAST(CASE ISNULL(COUNT(DISTINCT g.reward_id), 0) WHEN 0 THEN 0 ELSE ISNULL(COUNT(f.reward_student_id), 0) * 100.00 / ISNULL(COUNT(DISTINCT g.reward_id), 0) / 100.00 END AS decimal(20, 2)) AS reward_average_person 这句能为你的小数点保留两位 如果要位数多点可以修改一下
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
WHEN 0 THEN 0 ELSE ISNULL(COUNT(f.reward_student_id), 0)
* 100.00 / ISNULL(COUNT(DISTINCT g.reward_id), 0) / 100.00 END AS decimal(20, 2))
AS reward_average_person
这句能为你的小数点保留两位
如果要位数多点可以修改一下