举例查询周一至周末全方位的收入

近期组成注释来剖判一下PIVOT语法(在那前边最佳看看本身上面提到博文:T-SQL
PIVOT語法剖判與實戰,里面说起的PIVOT语法的四个步骤挺首要):

SELECTSUM(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
INSERT INTO WEEK_INCOME 
SELECT '星期一',1000UNION ALLSELECT '星期二',2000UNION ALLSELECT '星期三',3000UNION ALLSELECT '星期四',4000UNION ALLSELECT '星期五',5000UNION ALLSELECT '星期六',6000UNION ALLSELECT '星期日',7000

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

浅显一句话来说:PIVOT正是行转列,UNPIVOT正是列传行

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

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

举例查询周一至周末全方位的收入。行转列后是那般:

以上是自个儿对PIVOT的明白,小编尽所能表明出来。然而话说回来,个人的精通的办法也分裂,就好像笔者起来看了多数篇博文,都未曾搞理解PIVOT用法。结果还是硬的经过例子和别人的博文再增多考虑才弄懂了,所以一旦各位看了本篇之后仍不可能清楚,那很健康,合作例子再增多本人思虑,慢慢的定能精晓。

举例查询周一至周末全方位的收入。请参考MSDN中关于PIVOT的用法:

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

健康情形下的询问结果是如此:

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

举例查询周一至周末全方位的收入。而是,在SQL SEENCOREVEOdyssey二零零六中提供了更进一步便利的措施,那正是”PIVOT”关系运算符。(相反的“列转行”是UNPIVOT),以下是利用PIVOT达成“行转列”的SQL语句

WEEK           INCOME
星期一           1000
举例查询周一至周末全方位的收入。举例查询周一至周末全方位的收入。星期二           2000
星期三           3000
星期四           4000
星期五           5000
星期六           6000
星期日           7000

只是MSDN上的陈说太过火正统得体,作者看了半天还没弄明白哪些利用PIVOT,搞不清楚PIVOT里面包车型的士语法的意思。于是又google了许多素材,以及由此地点提到的WEEK_INCOME表例子作了考试,最后搞驾驭了其用法。在网络有篇博文解释的很好:T-SQL
PIVOT語法分析與實戰,基本上自个儿要写的正是参照该博文,再增加自个儿一点民用通晓。

唯独在局地状态下(往往是少数报表中),大家愿意在一行中体现礼拜四至周天的收入,那时候查询结果集应该是那样的:

也正是说,行转列后,原本的某些列的值变做了列名,在此地正是原先WEEK列的值“周二”,”周二”…”周末”边做了列名,而我们必要做的另两个做事正是测算那个列的值(这里的“总结”其实正是PIVOT里面包车型大巴聚合函数(sum,avg等))

这种情景下,SQL查询语句能够如此写:

获得如下的询问结果集:

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

要知道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里面就只写“星期一”至“星期五”(注意,in里面是原来week列的值,"以值变列")。总的来说,SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来,就是说:将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列,这些列的值取income的总和。
)TBL--别名一定要写

在数据库操作中,有些时候大家相见供给贯彻“行转列”的急需,比方一下的表为某商号的一日收入情状表:

SELECT WEEK,INCOME FROM WEEK_INCOME

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图