Sql Server 2008 行列转换 不会用case when？

[cooolchen]

在AdventureWorks数据库中统计 Department 表中各个部门男女各有多少人？
涉及到的表有：
AdventureWorks.HumanResources.Employee
AdventureWorks.HumanResources.Department
AdventureWorks.HumanResources.EmployeeDepartmentHistory
我写的sql（有问题）

select b.Name, case c.Gender when 'M' then COUNT(c.EmployeeID) end as m,
case c.Gender when 'F' then COUNT(c.EmployeeID) end as f
from HumanResources.EmployeeDepartmentHistory a inner join HumanResources.Department b
on b.DepartmentID = a.DepartmentID
inner join HumanResources.Employee c
on a.EmployeeID = c.EmployeeID
group by b.DepartmentID,b.Name,c.Gender

得到的结果是


显然不对。
我想得到的结果如下：

[永生天地]

select b.Name, 
max(case c.Gender when 'M' then COUNT(c.EmployeeID) end ) as m,
max( case c.Gender when 'F' then COUNT(c.EmployeeID) end ) as f
 from HumanResources.EmployeeDepartmentHistory a inner join HumanResources.Department b
 on b.DepartmentID = a.DepartmentID
 inner join HumanResources.Employee c  
 on a.EmployeeID = c.EmployeeID
 group by b.DepartmentID,b.Name,c.Gender

[流氓兔]

[Quote=引用 1 楼 xys_777 的回复:]
SQL code
select b.Name,
max(case c.Gender when 'M' then COUNT(c.EmployeeID) end ) as m,
max( case c.Gender when 'F' then COUNT(c.EmployeeID) end ) as f
from HumanResources.EmployeeDepartmentHist……
[/Quote]
需要加MAX

[cooolchen]

[Quote=引用 1 楼 xys_777 的回复:]
SQL code
select b.Name,
max(case c.Gender when 'M' then COUNT(c.EmployeeID) end ) as m,
max( case c.Gender when 'F' then COUNT(c.EmployeeID) end ) as f
from HumanResources.EmployeeDepartmentHist……
[/Quote]

Msg 130, Level 15, State 1, Line 2
不能对包含聚合或子查询的表达式执行聚合函数。

[永生天地]

with t1 as(
select b.Name, 
case c.Gender when 'M' then COUNT(c.EmployeeID) end  as m,
 case c.Gender when 'F' then COUNT(c.EmployeeID) end  as f
 from HumanResources.EmployeeDepartmentHistory a inner join HumanResources.Department b
 on b.DepartmentID = a.DepartmentID
 inner join HumanResources.Employee c  
 on a.EmployeeID = c.EmployeeID
 group by b.DepartmentID,b.Name,c.Gender)
select  name,max(m) 男,max(f) 女 from t1

[cooolchen]

是统计各个部门男女各有多少人。加个max是什么意思？和最大没关系哦。

[Sharon_liu]

 select b.Name, COUNT(case c.Gender when 'M' then c.EmployeeID ELSE NULL end) as m,
 COUNT(case c.Gender when 'F' then c.EmployeeID ELSE NULL end) as f
 from HumanResources.EmployeeDepartmentHistory a inner join HumanResources.Department b
 on b.DepartmentID = a.DepartmentID
 inner join HumanResources.Employee c 
 on a.EmployeeID = c.EmployeeID
 group by b.DepartmentID,b.Name,c.Gender

[永生天地]

上面少了group

with t1 as(
select b.Name, 
case c.Gender when 'M' then COUNT(c.EmployeeID) end  as m,
 case c.Gender when 'F' then COUNT(c.EmployeeID) end  as f
 from HumanResources.EmployeeDepartmentHistory a inner join HumanResources.Department b
 on b.DepartmentID = a.DepartmentID
 inner join HumanResources.Employee c  
 on a.EmployeeID = c.EmployeeID
 group by b.DepartmentID,b.Name,c.Gender)
select  name,max(m) 男,max(f) 女 from t1 group by name
/*
name                                               男           女
-------------------------------------------------- ----------- -----------
Document Control                                   4           1
Engineering                                        4           3
Executive                                          1           1
Facilities and Maintenance                         5           2
Finance                                            5           6
Human Resources                                    4           2
Information Services                               6           4
Marketing                                          5           5
Production                                         134         46
Production Control                                 6           NULL
Purchasing                                         9           4
Quality Assurance                                  6           1
Research and Development                           2           2
Sales                                              11          7
Shipping and Receiving                             4           2
Tool Design                                        3           1
警告: 聚合或其他 SET 操作消除了空值。

(16 行受影响)

*/

[Sharon_liu]

记得聚合函数一定要加到最外边，因为你用来判断的列不是分组列，所以放在外边会被认为是非法列

[cooolchen]

[Quote=引用 4 楼 xys_777 的回复:]
SQL code
with t1 as(
select b.Name,
case c.Gender when 'M' then COUNT(c.EmployeeID) end as m,
case c.Gender when 'F' then COUNT(c.EmployeeID) end as f
from HumanResources.EmployeeDepartmentH……
[/Quote]
Msg 8120, Level 16, State 1, Line 10
选择列表中的列 't1.Name' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

[cooolchen]

谢谢，我试着加个group by name也行了。

[永生天地]

改成这样更好些

select b.Name, 
sum(case when c.Gender ='M' and c.EmployeeID is not null then 1 else 0 end)  as m,
sum(case when c.Gender ='F'  and c.EmployeeID is not null then 1 else 0 end ) as f
from HumanResources.EmployeeDepartmentHistory a 
inner join HumanResources.Department b on b.DepartmentID = a.DepartmentID
inner join HumanResources.Employee c   on a.EmployeeID = c.EmployeeID
group by b.DepartmentID,b.Name

/*
Name                                               m           f
-------------------------------------------------- ----------- -----------
Engineering                                        4           3
Tool Design                                        3           1
Sales                                              11          7
Marketing                                          5           5
Purchasing                                         9           4
Research and Development                           2           2
Production                                         134         46
Production Control                                 6           0
Human Resources                                    4           2
Finance                                            5           6
Information Services                               6           4
Document Control                                   4           1
Quality Assurance                                  6           1
Facilities and Maintenance                         5           2
Shipping and Receiving                             4           2
Executive                                          1           1

(16 行受影响)
*/

[cooolchen]

这个看起来更简洁，更容易懂。很好！