左联接和和与群的奇数行为

Question

我有两张桌子：

student attendance table - student_id, campus_section
campus table -  campus_section, number_of_students, campus_name 

样本数据：

Student Table: 
Student_Id, campus_section
1, ddr1
2, ddr1
3, ddr2 
4, ddr3
5, ddv1
6, ddv2
7, ddv6

Campus Table
Campus_Section, Number_Of_Students, Campus_Name
ddr1, 10, ddr
ddr2, 5, ddr
ddr3, 5, ddr
ddv1, 5, ddv
ddv2, 10, ddv
ddv3, 10, ddv
ddv6, 10, ddv

因此，预期的行将是

Campus, current_students, campus_students    
ddr, 4, 20
ddv, 3, 35

每个campus_section可以有多个campus_name行。下面的查询列出校园名称、该校园的学生人数和该校园的学生总数。

select d.[campus_name] as campus_name, 
       cast(count(s.student_id) as int) as current_students, 
       sum(cast (d.[number_of_students] as int)) as campus_students 
from campus d 
left join student s 
on s.campus_section = d.campus_section  
group by d.[campus_name]

对于某些校园名称，section_students列中的结果大于以下内容：

select d.[campus_name] as campus_name, 
           sum(cast (d.[number_of_students] as int)) as section_students 
    from campus d 
    group by d.[campus_name]  

这意味着左联接在做某些行不应该做的事情。或者第二个查询不正确。

编辑:例如，第一个查询将为一个特定的校园名称提供18个，而第二个查询将给出10个。

有人能说明一下发生了什么事吗？这是sql server 2008。

Answer 1

有了“左加入”，sum(cast (d.[number_of_students] as int))将成为校园中的number_of_students*number_of_students。去掉group by语句，您就会发现原因。

select d.[campus_name] as campus_name, 
       s.student_id, 
       d.[number_of_students]
from campus d 
left join student s 
on s.campus_section = d.campus_section 

所以正确的方法是：

select d.[campus_name] as campus_name, 
       cast(count(s.student_id) as int) as current_students, 
       cast (d.[number_of_students] as int) as section_students 
from campus d 
left join student s 
on s.campus_section = d.campus_section  
group by d.[campus_name],cast (d.[number_of_students] as int)

根据以后发布的数据更新，需要先按校园分组得到section_students号和，然后按campus_name加入学生表，得到current_students号。

with campus_t as (select d.[campus_name] as campus_name, 
       sum(cast (d.[number_of_students] as int)) as campus_students 
     from campus d
     group by d.[campus_name])
select d.campus_name,
       d.campus_students,
       cast(count(s.student_id) as int) as current_students,
from campus_t d 
left join campus section
on section.campus_name = d.campus_name
left join student s 
on s.campus_section = section.campus_section  
group by d.campus_name,d.campus_students

注:尚未测试。请检查一下。

你的问题来自于非正规化设计。校园表应分为两个校园表和campus_section表。这就是为什么我必须添加一个名为campus_t的CTE表来获取校园实体的信息。原始校园表的数据表示campus_section实体。如果将模型规范化为三个表，则应该更容易查询。