我们希望在一行中显示周一至周日的收入

日期:2020-03-26编辑作者:北京pk10官网-数据库

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

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

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

INSERT INTO WEEK_INCOME SELECT '星期一',1000UNION ALLSELECT '星期二',2000UNION ALLSELECT '星期三',3000UNION ALLSELECT '星期四',4000UNION ALLSELECT '星期五',5000UNION ALLSELECT '星期六',6000UNION ALLSELECT '星期日',7000

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

SELECT WEEK,INCOME FROM WEEK_INCOME

得到如下的查询结果集:

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

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

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

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

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

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

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

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

(v=sql.105).aspx

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

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

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

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

行转列后是这样:

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

也就是说,行转列后,原来的某个列的值变做了列名,在这里就是原来WEEK列的值“星期一”,"星期二"..."星期日"边做了列名,而我们需要做的另一个工作就是计算这些列的值

现在结合注释来分析一下PIVOT语法(在这之前最好看看我上面提到博文:,里面说到的PIVOT语法的三个步骤挺重要):

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

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

总结

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

本文由北京pk赛车官网下载发布于北京pk10官网-数据库,转载请注明出处:我们希望在一行中显示周一至周日的收入

关键词:

那么要如何正确的删除这些Windows认证账号呢

前言 在SQLServer数据库中,有时候会建立一些Windows认证的账号,例如,我们公司习惯给开发人员和Support同事开通NT账号...

详细>>

直接改数据库的肯定不止我一个

前言 项目中一般分测试环境,生产环境,当我们的项目经历了一次周期跨度较长的更新后,当我们发布到生产环境时...

详细>>

增加分批删除一个表指定数据的话只需要增加几

博主做过比较多项目的archive脚本编写,对于这种删除数据的脚本开发,肯定是一开始的话用最简单的一个delete语句,...

详细>>

并且该帐户caching

引言 用SQLyog连接MySQL8.0(社区版:mysql-installer-community-8.0.15.0.msi卡塔尔,现身谬误2058(Plugincaching_sha2_password could not bel...

详细>>