数据库一步步处理行列转换-白红宇

数据库一步步处理行列转换

阅读量：6594 次

发布时间：2019-06-24

本文共 10452 字，大约阅读时间需要 34 分钟。

做个东西,发现了些问题,又解决了,很Happy!

数据库有2个表:

[省份表],2列(provinceId--省的id,provinceName--省名字),

[公司表],好几列(companyId--公司Id, provinceId--公司所在省id, recruitType--这个招聘会类型(只有1,2,3这三个值,每个值一个含义),......其他问题无关列)

------------------------------------------------------------------------------------------------

方法一:(自己写的)

------------------------------------------------------------------------------------------------

我想获得的结果,即每个省按那三个招聘会类型统计各种类型公司的总数,如下:

------------------------------------------------------------------------------------------------

先获得了下面表,省id,招聘会类型,公司总数,这是在[公司表]里面按(省id,招聘会类型)group by和count()得到的,注意这里有个问题,就是[公司表]里面不是什么省份的都有,所以provinceId是不连续的,所以要在后面处理.

------------------------------------------------------------------------------------------------

然后联合[省份表],得到下面的表,这个很容易,只要注意一下 left join,就可以显示所有的省份了

------------------------------------------------------------------------------------------------

现在问题来了,我想显示的目标图是第一个图,就是这么把那些相同省的行合并为同一行,并且有三个列分别显示那三种招聘会类型的公司总数?

我想了半天,因为最终是要在Web上显示的,我想要不就在数据库搞好,把结果直接返回,Web端就不要处理什么了,要不就这样返回吧,在Web端合并相同的行,

但最后发现还是数据库端好处理,因为T-SQL很强大,并且Web端处理这些表格很麻烦,我不知道它那些DataTable啊什么的类似怎么样构造的,遂选择在数据库端处理.

------------------------------------------------------------------------------------------------

1,建立最后想要结果的表,把目标列写好,然后要不insert,要不update

2,用游标一个一个处理上面的那个表,provinceId一样的就update那一行几个值,否则insert新行

3,最后处理一些数据,得到想要结果:

代码量大啊!!!主要用游标一行一行处理的......

USE [Statistics]GO/****** Object:  StoredProcedure [dbo].[procAnalyseProvince]    Script Date: 2013/12/11 16:22:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        
      
       -- Create date: 
       
        -- Description:    按招聘单位地域统计的数据源-- =============================================ALTER PROCEDURE [dbo].[procAnalyseProvince]@timeFrom char(10),@timeTo char(20)ASBEGIN/*下面是第一部分,获得临时表: [provinceId,recruitType,companySum] ,注意这里的provinceId可能只有一部分,即来招聘的那部分*/begin    if OBJECT_ID(N'#tableTemp',N'U') is not null    drop table #tableTempendselect [Companys].provinceId , [Companys].recruitType ,COUNT(*) as companySuminto #tableTemp from [Companys]   --到临时表-----------------------------表的列:[provinceId,recruitType,companySum]where [Companys].recruitTime between @timeFrom and @timeTogroup by [Companys].provinceId,[Companys].recruitType  --根据省份和招聘会类型分组,不管哪个是哪个团体order by [Companys].provinceId/*下面也是一部分,获得临时表: [provinceId,provinceName,recruitType,companySum] ,注意这里的provinceId包含数据库中全部省*/begin    if OBJECT_ID(N'#newTable',N'U') is not null    drop table #newTableendselect [Provinces].provinceId,[Provinces].provinceName,#tableTemp.recruitType,#tableTemp.companySuminto #newTable  --到临时表----------------------------------表的列:[provinceId,provinceName,recruitType,companySum]from [Provinces] left join #tableTemp on [Provinces].provinceId=#tableTemp.provinceIdorder by [Provinces].provinceId ASC, #tableTemp.recruitType ASC/*下面作处理,先创建最后结果表:*/begin    if OBJECT_ID(N'#lastTable',N'U') is not null    drop table #lastTableendcreate table #lastTable   --创建表最终要返回应用程序的表!!!!注意这里的强大之处!!!(provinceId int primary key,provinceName nvarchar(20) not null,type1Sum int default 0,  --默认值为0,省去null处理的麻烦type1Ratio varchar(10),type2Sum int default 0,type2Ratio varchar(10),type3Sum int default 0,type3Ratio varchar(10),allTypeSum int default 0,allTypeRatio varchar(10))/*下面用游标处理表,第一次处理,只是在最终表填了[provinceId,provinceName,type1Sum,type2Sum,type3Sum]*/begin  --这里定义一些变量declare @preProvinceId int --上一个省的idset @preProvinceId=0  --初始为0declare @provinceId int,@provinceName nvarchar(20),@recruitType int,@companySum int  --定义一个游标所对应的一行值enddeclare curToNewTable scroll cursor for select * from #newTableopen curToNewTablefetch first from curToNewTable into @provinceId,@provinceName,@recruitType,@companySum  --取第一条while @@FETCH_STATUS=0  --取成功了begin  if(@provinceId!=@preProvinceId)  --跟上一次的不一样,所以要插入新行    begin      set @preProvinceId=@provinceId  --先设置这个      if(@recruitType is null) --注意,这里因为之前的原因,@recruitType可能为null,这样这一行只录入省        insert into #lastTable (provinceId,provinceName) values (@provinceId,@provinceName)      else if(@recruitType=1)        insert into #lastTable (provinceId,provinceName,type1Sum) values(@provinceId,@provinceName,@companySum)      else if(@recruitType=2)        insert into #lastTable (provinceId,provinceName,type2Sum) values(@provinceId,@provinceName,@companySum)      else  --3,@recruitType只有null,1,2,3,这四种值        insert into #lastTable (provinceId,provinceName,type3Sum) values(@provinceId,@provinceName,@companySum)    end  else  --一样,就update那一行    begin      if(@recruitType=1)  --这里就不要管@recruitType是null了,那不处理        update #lastTable set #lastTable.type1Sum=@companySum where #lastTable.provinceId=@provinceId      else if(@recruitType=2)        update #lastTable set #lastTable.type2Sum=@companySum where #lastTable.provinceId=@provinceId      else if(@recruitType=3)        update #lastTable set #lastTable.type3Sum=@companySum where #lastTable.provinceId=@provinceId    end  fetch next from curToNewTable into @provinceId,@provinceName,@recruitType,@companySum  --取下一条endclose curToNewTable  --关闭游标deallocate curToNewTable  --释放游标资源/*下面用游标对最后结果表作第二次处理,即把表的全部项填满除了总计的比例项*/declare @allSingleTotal int,@allMediumTotal int,@allBigTotal int,@allTypeTotal int --整体的,这几个值是放到最后一行"总计"的set @allSingleTotal=(select sum(type1Sum) from #lastTable)set @allMediumTotal=(select sum(type2Sum) from #lastTable)set @allBigTotal=(select sum(type3Sum) from #lastTable)set @allTypeTotal=@allSingleTotal+@allMediumTotal+@allBigTotal  --其实这个值也为这一列的所有值的和declare @lineType1Sum int,@lineType2Sum int,@lineType3Sum int,@lineTypeAllSum int--一行的declare curToFillRatio scroll cursor for select type1Sum,type2Sum,type3Sum from #lastTable  --游标open curToFillRatiofetch first from curToFillRatio into @lineType1Sum,@lineType2Sum,@lineType3Sumwhile(@@FETCH_STATUS=0)begin  set @lineTypeAllSum=@lineType1Sum+@lineType2Sum+@lineType3Sum  --三者之和,这个值也是待填上的    /*下面获得一行的4个比例的值,4位数,3位小数的形式*/  declare @decml1 decimal(4,3),@decml2 decimal(4,3),@decml3 decimal(4,3),@decml4 decimal(4,3)   --小数,共4位,小数点后有3位(就1.000 0.223 0.022 0.003这几种情况)    if(@allSingleTotal=0)  set @decml1=0  --每行的专场比例  else  set @decml1=@lineType1Sum*1.0/@allSingleTotal  --注意*1.0    if(@allMediumTotal=0)  set @decml2=0  --每行的中型比例  else  set @decml2=@lineType2Sum*1.0/@allMediumTotal  --注意*1.0      if(@allBigTotal=0)  set @decml3=0  --大型  else  set @decml3=@lineType3Sum*1.0/@allBigTotal  if(@allTypeTotal=0)  set @decml4=0  --总计  else  set @decml4=@lineTypeAllSum*1.0/@allTypeTotal  /*下面对小数进行处理,得到百分比形式的除了百分号的值*/  declare @temp1 decimal(4,1),@temp2 decimal(4,1),@temp3 decimal(4,1),@temp4 decimal(4,1)--  --转化成共四位,一位小数的百分比前面的数值,即 100.0  40.3  5.4  0.4  set @temp1=CAST(100*@decml1 as decimal(4,1))  set @temp2=CAST(100*@decml2 as decimal(4,1))  set @temp3=CAST(100*@decml3 as decimal(4,1))  set @temp4=CAST(100*@decml4 as decimal(4,1))  /*好了,可以更新了*/  update #lastTable set type1Ratio=CAST(@temp1 as varchar(10))+'%',                        type2Ratio=CAST(@temp2 as varchar(10))+'%',                        type3Ratio=CAST(@temp3 as varchar(10))+'%',                        allTypeSum=@lineTypeAllSum,                        allTypeRatio=CAST(@temp4 as varchar(10))+'%'                    where current of curToFillRatio  fetch next from curToFillRatio into @lineType1Sum,@lineType2Sum,@lineType3Sumendclose curToFillRatiodeallocate curToFillRatio/*插入总计的行*/insert into #lastTable values(40, '总计',@allSingleTotal,'100.0%',                                          @allMediumTotal,'100.0%',                                          @allBigTotal,'100.0%',                                          @allTypeTotal,'100.0%')/*最后只获取一个结果表*/select * from(  select top 100 * from #lastTable where provinceId!=40 order by allTypeSum desc  --注意这里必须要top这样的语句)Tunion allselect * from #lastTable where provinceId=40END

我的一步步处理代码

------------------------------------------------------------------------------------------------

方法二:(得到的答案)

------------------------------------------------------------------------------------------------

这里的主要问题就是把有那个有多行省Id是一样的合并到一行中去,添加了列

神伤中......居然可以这么简单......

select provinceId,provinceName,sum(case recruitType when 1 then companySum end) as myType1Sum,sum(case recruitType when 2 then companySum end) as myType2Sum,sum(case recruitType when 3 then companySum end) as myType3Sum from #newTable group by provinceId,provinceName

分2步来理解这个,首先看这个:

select provinceId,provinceName,case recruitType when 1 then companySum end as typ1Sum,case recruitType when 2 then companySum end as typ2Sum,case recruitType when 3 then companySum end as typ3Sumfrom #newTable

结果是:

我想可以这样理解:select语句其实也是从头到尾一行行处理的,上面的select语句处理到每一行,选出省id,省名字,case测试此行的recruitType,若是1,then取此行[companySum]列的值当做[typ1Sum]列的值,若不是1的话则[typ1Sum]列的值就成了默认的null.

其他2个case语句同理.于是就得到了另外的三个列,并且只是在对应的列填了值,行还是一样的.

然后就根据这个中间表group by,再sum(),注意sum()里面的是要sum的列名,sum(case recruitType when 1 then companySum end) 的意思应该是按照(case recruitType when 1 then companySum end)列进行sum,因为sql语句case recruitType when 3 then companySum end as typ3Sum若没有as语句则显示为没指定列名的列

------------------------------------------------------------------------------------------------

附(group by说明)

1,值相等的为一组

2,聚合函数作用于每一组

3,显示的列只能为聚合函数产生或分组依据的列

附(case表达式)

语法格式:

CASE 测试表达式

When 简单表达式1 then 结果表达式1

When 简单表达式2 then 结果表达式2 ……..

When 简单表达式n then 结果表达式n

[else 结果表达式n+1]

END

------------------------------------------------------------------------------------------------

方法三:(得到的答案)

------------------------------------------------------------------------------------------------

sql server 提供了一个行列转换的东西 Pivot

pivot的介绍(引用):

select * from #newTableselect *   -- 第三步,呈现结果from #newTable  --第一步 原始查询,这里已有表pivot  -- 第二步 定义行转列(max(companySum)for recruitType in ([1],[2],[3]))as torder by provinceId

结果如下,本来的表为第一个,第二个为转换的结果表