前言

前言

在数据库操作中,有些时候我们遇到需要实现“行转列”的需求,例如一下的表为某店铺的一周收入情况表:

未来的一个月时间中,会总结一系列SQL知识点,一次只总结一个知识点,尽量说明白,下面来说说SQL
中常用Pivot
函数(这里是用的数据库是SQLSERVER,与其他数据库是类似的,大家放心看就好)

这是总结SQL知识点的第二篇文章,一次只总结一个知识点,尽量说明白。上次我们谈到行转列,用的是Pivot函数,这次我们来谈谈Unpivot函数。(这里是用的数据库是SQLSERVER,与其他数据库是类似的,大家放心看就好)

WEEK_INCOME,INCOME DECIMAL)

让我们先从一个虚构的场景中来着手吧

先看一个小问题CustomerCustomer

我们先插入一些模拟数据:

万国来朝,很多供应商每天都汇报各自的收入情况。先来创建一个DailyIncome 表

在这张图中,表示的是顾客用不同手机号给Phone1、Phone2、Phone3拨打电话的情况,但是机灵的你,想变个花样来看看,比如下面这样的。

INSERT INTO WEEK_INCOME SELECT '星期一',1000UNION ALLSELECT '星期二',2000UNION ALLSELECT '星期三',3000UNION ALLSELECT '星期四',4000UNION ALLSELECT '星期五',5000UNION ALLSELECT '星期六',6000UNION ALLSELECT '星期日',7000
create table DailyIncome, IncomeDay nvarchar, IncomeAmount int)--VendorId 供应商ID,--IncomeDay 收入时间--IncomeAmount 收入金额

UnpivotUnpivot

一般我们最经常使用的查询是查询一周中每天或某几天的收入,例如查询周一至周日全部的收入:

(留意看下,有的供应商某天中会有多次收入,应该是分批进账的)

大家想想看如何实现呢?想下,2分钟后再看哟

SELECT WEEK,INCOME FROM WEEK_INCOME
insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values 
 CREATE TABLE dbo.CustomerPhones( CustomerID INT PRIMARY KEY, -- FK Phone1 VARCHAR, Phone2 VARCHAR, Phone3 VARCHAR;INSERT dbo.CustomerPhones (CustomerID, Phone1, Phone2, Phone3)VALUES (1,'705-491-1111', '705-491-1110', NULL), (2,'613-492-2222', NULL, NULL), (3,'416-493-3333', '416-493-3330', '416-493-3339'); select * from dbo.CustomerPhones

得到如下的查询结果集:

让我们先来看看前十行数据:

DataDataUpivot 实现行转列

WEEK INCOME星期一 1000星期二 2000星期三 3000星期四 4000星期五
5000星期六 6000星期日 7000

select top 10 * from DailyIncome
 select * from dbo.CustomerPhones -----数据源 unpivot ( Phone FOR Phones IN (Phone1, Phone2, Phone3) ##Phone1、Phone2、Phone3这些列的结果转为行上的结果,成为一列, ##并且有了新的列名为Phone) up

但是在一些情况下,我们希望在一行中显示周一至周日的收入,这时候查询结果集应该是这样的:

DailyIncome

ResultResult

星期一 星期二 星期三 星期四 星期五 星期六 星期日1000 2000 3000 4000
5000 6000 7000

虽然数据是能够完全给展示了,但好像一眼望去不能得到对我们用处更大的信息,比如说我们想得到每个供应商的每天的总收入,这时我们应该做一些数据形式的转变了,平常的所用的是这样的。

参考文献:

这种情况下,SQL查询语句可以这样写:

select VendorId ,sum(case when IncomeDay='MoN' then IncomeAmount else 0 end) MON,sum(case when IncomeDay='TUE' then IncomeAmount else 0 end) TUE,sum(case when IncomeDay='WED' then IncomeAmount else 0 end) WED,sum(case when IncomeDay='THU' then IncomeAmount else 0 end) THU,sum(case when IncomeDay='FRI' then IncomeAmount else 0 end) FRI,sum(case when IncomeDay='SAT' then IncomeAmount else 0 end) SAT,sum(case when IncomeDay='SUN' then IncomeAmount else 0 end) SUNfrom DailyIncome group by VendorId

1.Use SQL Server’s UNPIVOT operator to help normalize output

SELECT SUM(CASE WEEK WHEN '星期一' THEN INCOME END) AS [星期一],SUM(CASE WEEK WHEN '星期二' THEN INCOME END) AS [星期二],SUM(CASE WEEK WHEN '星期三' THEN INCOME END) AS [星期三],SUM(CASE WEEK WHEN '星期四' THEN INCOME END) AS [星期四],SUM(CASE WEEK WHEN '星期五' THEN INCOME END) AS [星期五],SUM(CASE WEEK WHEN '星期六' THEN INCOME END) AS [星期六],SUM(CASE WEEK WHEN '星期日' THEN INCOME END) AS [星期日]FROM WEEK_INCOME

case when结果

2.SQL之行转列Pivot用法

home88一必发,但是,在SQL SERVER
2005中提供了更为简便的方法,这就是”PIVOT”关系运算符。,以下是使用PIVOT实现“行转列”的SQL语句

如果大家仔细看结果的话,会有这样的发现,这是把VendorID进行了分组,并且对于每组中IncomeDay这一列中的值都变成了新的列名字,然后对IncomeAmount进行求和操作。

总结

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]FROM WEEK_INCOMEPIVOT for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]))TBL

这样写可能是有些麻烦,别着急,我们用Pivot函数进行行转列试下。

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

请参考MSDN中关于PIVOT的用法:

select * from DailyIncome ----第一步pivot  ----第三步for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) ---第二步) as AvgIncomePerDay

来解释下,要想用好Pivot函数,应该理解代码注释中的这几步。

但是MSDN上的描述太过于规范严肃,我看了半天还没弄清楚怎样使用PIVOT,搞不清楚PIVOT里面的语法的含义。于是又google了很多资料,以及通过上面提到的WEEK_INCOME表例子作了试验,最终搞清楚了其用法。在网上有篇博文解释的很好:

第一步:肯定是要明白数据源了,这里是DailyIncome

要理解PIVOT语法,就是要清楚微软为什么这样设计PIVOT,但我相信是现实需求催生设计思路,所以归根到底我们还是要弄清楚什么是“行转列”:

第二步:要明白要想让哪一列的值做新的列名字

正常情况下的查询结果是这样:

第三步:要明白对于这新的列要求那些值呢?

星期一 1000星期二 2000星期三 3000星期四 4000星期五 5000星期六
6000星期日 7000

星期一 星期二 星期三 星期四 星期五 星期六 星期日1000 2000 3000 4000
5000 6000 7000

下面有个练习题目,做之前不要看答案啊

也就是说,行转列后,原来的某个列的值变做了列名,在这里就是原来WEEK列的值“星期一”,”星期二”…”星期日”边做了列名,而我们需要做的另一个工作就是计算这些列的值(这里的“计算”其实就是PIVOT里面的聚合函数

问:对于SPIKE这家供应商来说,每天最大的入账金额。

现在结合注释来分析一下PIVOT语法(在这之前最好看看我上面提到博文:

select * from DailyIncomepivot  for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDaywhere VendorId in 
SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步这里可以用“*”表示选择所有列,也可以只选择某些列FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果,因为PIVOT是对一个原始的查询结果集进行转换操作,所以先查询一个结果集出来)这里可以是一个select子查询,但为子查询时候要指定别名,否则语法错误PIVOT for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--这里是PIVOT第一步骤,也是核心的地方,进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值,是总和还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”,其中一条的income是1000,另一条income是500,那么在这里使用sum,行转列后“星期一”这个列的值当然是1500了。后面的for [week] in中 for [week]就是说将week列的值分别转换成一个个列,也就是“以值变列”。但是需要转换成列的值有可能有很多,我们只想取其中几个值转换成列,那么怎样取呢?就是在in里面了,比如我此刻只想看工作日的收入,在in里面就只写“星期一”至“星期五”(注意,in里面是原来week列的值,"以值变列")。总的来说,SUM for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来,就是说:将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列,这些列的值取income的总和。)TBL--别名一定要写

参考链接如下:

以上是我对PIVOT的理解,我尽所能表达出来。不过话说回来,个人的理解的方式也不同,就如我开始看了很多篇博文,都没有搞清楚PIVOT用法。结果还是硬的通过例子和别人的博文再加上思考才弄懂了,所以如果各位看了本篇之后仍不能理解,那很正常,配合例子再加上自己思考,慢慢的定能理解。

1.Pivot tables in SQL Server. A simple sample

总结

2.行转列:SQL SERVER PIVOT与用法解释

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

相关文章