• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    一个简单的SQL 行列转换语句
    一个简单的SQL 行列转换
    Author: eaglet
    在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表
    部门编号 部门名称 合计 正式员工 临时员工 辞退员工
    1 A 30 20 10 1
    这种问题咋一看摸不着头绪,不过把思路理顺后再看,本质就是一个行列转换的问题。下面我结合这个简单的例子来实现行列转换。
    下面3张表
    复制代码 代码如下:

    if exists ( select * from sysobjects where id = object_id ( ' EmployeeType ' ) and type = ' u ' )
    drop table EmployeeType
    GO
    if exists ( select * from sysobjects where id = object_id ( ' Employee ' ) and type = ' u ' )
    drop table Employee
    GO
    if exists ( select * from sysobjects where id = object_id ( ' Department ' ) and type = ' u ' )
    drop table Department
    GO
    create table Department
    (
    Id int primary key ,
    Department varchar ( 10 )
    )
    create table Employee
    (
    EmployeeId int primary key ,
    DepartmentId int Foreign Key (DepartmentId) References Department(Id) , -- DepartmentId ,
    EmployeeName varchar ( 10 )
    )
    create table EmployeeType
    (
    EmployeeId int Foreign Key (EmployeeId) References Employee(EmployeeId) , -- EmployeeId ,
    EmployeeType varchar ( 10 )
    )

    描述部门,员工和员工类型之间的关系。
    插入测试数据
    复制代码 代码如下:

    insert Department values ( 1 , ' A ' );
    insert Department values ( 2 , ' B ' );
    insert Employee values ( 1 , 1 , ' Bob ' );
    insert Employee values ( 2 , 1 , ' John ' );
    insert Employee values ( 3 , 1 , ' May ' );
    insert Employee values ( 4 , 2 , ' Tom ' );
    insert Employee values ( 5 , 2 , ' Mark ' );
    insert Employee values ( 6 , 2 , ' Ken ' );
    insert EmployeeType values ( 1 , ' 正式 ' );
    insert EmployeeType values ( 2 , ' 临时 ' );
    insert EmployeeType values ( 3 , ' 正式 ' );
    insert EmployeeType values ( 4 , ' 正式 ' );
    insert EmployeeType values ( 5 , ' 辞退 ' );
    insert EmployeeType values ( 6 , ' 正式 ' );

    看一下部门、员工和员工类型的列表
    Department EmployeeName EmployeeType
    ---------- ------------ ------------
    A Bob 正式
    A John 临时
    A May 正式
    B Tom 正式
    B Mark 辞退
    B Ken 正式
    现在我们需要输出这样一个列表
    部门编号 部门名称 合计 正式员工 临时员工 辞退员工
    这个问题我的思路是首先统计每个部门的员工类型总数
    这个比较简单,我把它做成一个视图
    复制代码 代码如下:

    if exists ( select * from sysobjects where id = object_id ( ' VDepartmentEmployeeType ' ) and type = ' v ' )
    drop view VDepartmentEmployeeType
    GO
    create view VDepartmentEmployeeType
    as
    select Department.Id, Department.Department, EmployeeType.EmployeeType, count (EmployeeType.EmployeeType) Cnt
    from Department, Employee, EmployeeType where
    Department.Id = Employee.DepartmentId and Employee.EmployeeId = EmployeeType.EmployeeId
    group by Department.Id, Department.Department, EmployeeType.EmployeeType
    GO

    现在 select * from VDepartmentEmployeeType
    Id Department EmployeeType Cnt
    ----------- ---------- ------------ -----------
    2 B 辞退 1
    1 A 临时 1
    1 A 正式 2
    2 B 正式 2
    有了这个结果,我们再通过行列转换,就可以实现要求的输出了
    行列转换采用 case 分支语句来实现,如下:
    复制代码 代码如下:

    select Id as ' 部门编号 ' , Department as ' 部门名称 ' ,
    [ 正式 ] = Sum ( case when EmployeeType = ' 正式 ' then Cnt else 0 end ),
    [ 临时 ] = Sum ( case when EmployeeType = ' 临时 ' then Cnt else 0 end ),
    [ 辞退 ] = Sum ( case when EmployeeType = ' 辞退 ' then Cnt else 0 end ),
    [ 合计 ] = Sum ( case when EmployeeType > '' then Cnt else 0 end )
    from VDepartmentEmployeeType
    GROUP BY Id, Department

    看一下结果
    部门编号 部门名称 正式 临时 辞退 合计
    ----------- ---------- ----------- ----------- ----------- -----------
    1 A 2 1 0 3
    2 B 2 0 1 3
    现在还有一个问题,如果员工类型不可以应编码怎么办?也就是说我们在写程序的时候并不知道有哪些员工类型。这确实是一个
    比较棘手的问题,不过不是不能解决,我们可以通过拼接SQL的方式来解决这个问题。看下面代码
    复制代码 代码如下:

    DECLARE
    @s VARCHAR ( max )
    SELECT @s = isnull ( @s + ' , ' , '' ) + ' [ ' + ltrim (EmployeeType) + ' ] = ' +
    ' Sum(case when EmployeeType = ''' +
    EmployeeType + ''' then Cnt else 0 end) '
    FROM ( SELECT DISTINCT EmployeeType FROM VDepartmentEmployeeType ) temp
    EXEC ( ' select Id as 部门编号, Department as 部门名称, ' + @s +
    ' ,[合计]= Sum(case when EmployeeType > '''' then Cnt else 0 end) ' +
    ' from VDepartmentEmployeeType GROUP BY Id, Department ' )

    执行结果如下:
    部门编号 部门名称 辞退 临时 正式 合计
    ----------- ---------- ----------- ----------- ----------- -----------
    1 A 0 1 2 3
    2 B 1 0 2 3
    这个结果和前面硬编码的结果是一样的,但我们通过程序来获取了所有的员工类型,这样做的好处是如果我们新增了一个员工类型,比如“合同工”,我们不需要修改程序,就可以得到我们想要的输出。

    如果你的数据库是SQLSERVER 2005 或以上,也可以采用SQLSERVER2005 通过的新功能 PIVOT
    复制代码 代码如下:

    SELECT Id as ' 部门编号 ' , Department as ' 部门名称 ' , [ 正式 ] , [ 临时 ] , [ 辞退 ]
    FROM
    ( SELECT Id,Department,EmployeeType,Cnt
    FROM VDepartmentEmployeeType) p
    PIVOT
    ( SUM (Cnt)
    FOR EmployeeType IN ( [ 正式 ] , [ 临时 ] , [ 辞退 ] )
    ) AS unpvt

    结果如下
    部门编号 部门名称 正式 临时 辞退
    ----------- ---------- ----------- ----------- -----------
    1 A 2 1 NULL
    2 B 2 NULL 1
    NULL 可以通过 ISNULL 函数来强制转换为0,这里我就不写出具体的SQL语句了。这个功能感觉还是不错,不过合计好像用这种方法不太好搞。不知道各位同行有没有什么好办法。
    您可能感兴趣的文章:
    • mysql 行转列和列转行实例详解
    • mssql 数据库表行转列,列转行终极方案
    • SQL行转列和列转行代码详解
    • sql语句实现行转列的3种方法实例
    • SQLServer行转列实现思路记录
    • MySQL存储过程中使用动态行转列
    • 数据库实现行列转换(mysql示例)
    • 深入SQL中PIVOT 行列转换详解
    • mysql 列转行,合并字段的方法(必看)
    • SQL行转列、列转行的简单实现
    上一篇:Linq to SQL 插入数据时的一个问题
    下一篇:SQLServer 常用语句(按功能分)
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    一个简单的SQL 行列转换语句 一个,简单,的,SQL,行列,转换,