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

2010-06-30 02:41:44 516 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL Server行列转换实战：从PIVOT到动态SQL的完整指南在数据库开发与数据分析领域，行列转换是一项基础且关键的数据重塑技术，它涉及将数据从行结构转换为列结构（行转列）或反之（列转行）。其核心原理是通过聚合函数与条件逻辑，重组数据维度以满足不同业务视图需求。这项技术的价值在于能够高效适配报表展示、数据接口规范以及下游分析模型的数据格式要求，广泛应用于金融报表生成、电商销售分析、运营数据汇总等场景。在SQL Server中，实现行列转换主要依赖PIVOT/UNPIVOT运算符、CASE WHEN条件聚合等方案，并需根据数据特征在静态转换与动态转换间做出选择。本文聚焦阅读详情

在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

得到的结果是

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

...全文

SQL SERVER透视转换（行旋转列）所谓的透视转化通俗点就是吧数据库的行转成列的一种处理方式，透视转换是一种常用的技巧，在生活中人们更喜欢看经过透视转化的数据表，这种表更加直观和简洁，下面将介绍两种处理方式。样例数据： USE tempdb; IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;GO CREATE TABLE dbo.Ord 阅读详情

cooolchen

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

cooolchen 2010-06-30

打赏
举报

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

永生天地 2010-06-30

打赏
举报

改成这样更好些



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 2010-06-30

打赏
举报

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

cooolchen 2010-06-30

打赏
举报

[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 子句中。

Sharon_liu 2010-06-30

打赏
举报

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

永生天地 2010-06-30

打赏
举报

上面少了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 2010-06-30

打赏
举报

 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

cooolchen 2010-06-30

打赏
举报

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

永生天地 2010-06-30

打赏
举报

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 2010-06-30

打赏
举报

[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
不能对包含聚合或子查询的表达式执行聚合函数。

流氓兔 2010-06-30

打赏
举报

永生天地 2010-06-30

打赏
举报

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

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

相关推荐

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