PIVOT与用法解释

2019-11-14 17:12 来源:未知

深入显出简单来说:PIVOT正是行转列,UNPIVOT就是列传行

1,数据库的生命周期:
供给深入分析(必要表明书卡塔 尔(阿拉伯语:قطر‎ -- 逻辑设计(E-CR-V卡塔尔 -- 物理设计(表结构) -- 数据库达成(范式化卡塔 尔(阿拉伯语:قطر‎ -- 数据校勘(索引/约束卡塔 尔(阿拉伯语:قطر‎ -- 数据库监控(维护卡塔尔国
详尽可仿照效法:

在数据库操作中,有个别时候大家相见需求落到实处“行转列”的急需,举个例子一下的表为某商铺的12日收入情状表:

2,E-Escort图学习:列出一些骨干消息,小编也是此处上学的

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

3,主键:唯生龙活虎标志列
特点:字段非空
字段具有唯黄金时代性
该值差别意校正(提议|【ps:改照旧有办法改的】卡塔 尔(阿拉伯语:قطر‎
各样实体只能有一个主键(提出|【ps:也得以有联手主键】卡塔 尔(阿拉伯语:قطر‎
外键:连接差别实体
成效:保持数据完整性

我们先插入一些效仿数据:

4,数据库的三大范式(调整数据的冗余卡塔 尔(英语:State of Qatar)
先是范式(1NF卡塔 尔(阿拉伯语:قطر‎:确认保证每列保持原子性
其次范式(2NF卡塔 尔(阿拉伯语:قطر‎: 确定保证表中的每列都和主键相关
其三范式(3NF卡塔尔: 确定保障每列都和主键列直接相关,并不是直接相关
详细可参看:
【PS:博主有话说>>通过特有提供冗余数据,减少连接的复杂度,获取更加快的查询时间。
那儿,撤销标准化也是必需的。
看来,品质 > 标准】

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

5,增删改查格式:
增:INSERT INTO 表名(列名) VALUES (值)
删:DELETE FROM 表名 WHERE 条件 【PS:条件意气风发旦没加,则删除整张表数据。稳重操作】
改:UPDATE 表名 SET 字段 = '值' WHERE 条件 【PS:条件豆蔻梢头旦没加,则校订整张表数据。严慎操作】

诚如大家最平日选取的查询是查询12日中每天或某几天的收入,举例查询礼拜三至周天任何的收益:

查:SELECT * FROM 表名 WHERE 条件

【PS * : 表示检索钦命表中的富有列】

6, GROUP BY 子句1:用于分组输骑行
ORDER BY 子句2:排序【DESC | ASC】
语句1:
--解释:依据name来分组,而且依据name来排序

SELECT WEEK,INCOME FROM WEEK_INCOME

--【PS:使用group by 时,查询的非聚合函数都要在group by中】

SELECT NAME FROM USERS GROUP BY NAME ORDER BY NAME DESC

7,PIVOT: 将行旋转成列

拿到如下的询问结果集:

语句2:

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列的值,"以值变列"卡塔 尔(英语:State of Qatar)。
--一句话来讲,SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])
--那句的意思借使直译出来,
--就是说:将列[week]值为"星期二","礼拜二","礼拜风流倜傥","星期二","星期二","礼拜六","星期六"分别转变到列,
--那个列的值取income的总和。

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

)TBL--外号必必要写

而是在有的场所下(往往是有个别报表中卡塔尔国,我们期望在后生可畏行中展现星期二至周六的受益,那个时候查询结果集应该是这么的:

【PS:以下语句as的乐趣是给列起小名的意趣】

语句3:
select [1] as 'v1',[2],[3],[4],[5],[6],[7],[8]
from (
select PublisherId
from books
) as tb_source
pivot(
count(PublisherId) for PublisherId in ([1],[2],[3],[4],[5],[6],[7],[8])
) as tb_pivot

8,ROW_NUMBEEvoque(): 返回四个独一的行列号
【ps:要配合over(order by 列名);】

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

语句4:

SELECT ROW_NUMBER() OVER(ORDER BY ID),* FROM Books

9,PARTITION BY 子句3:用于将结果集划分为使用了ROW_NUMBE昂科雷()函数的分区
各种分区的第豆蔻梢头行都以从1发端的

这种状态下,SQL查询语句能够如此写:

语句5:

SELECT ROW_NUMBER() OVER(PARTITION BY PublisherId ORDER BY PublisherId) as rownumber,*
FROM Books WHERE PublisherId in (1,2,3)

10,通配符:% _ [] [^]

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

语句6:查找客商名以L最先的顾客音信

SELECT * FROM USERS WHERE USERNAME LIKE 'L%'

11,聚合函数:SUM() --求和
COUNT() --求数量
MIN() -- 最小值
MAX() -- 最大值
AVG() -- 平均数
ROW_NUMBE陆风X8() -- 获取类别号

12,日期函数:
CURRENT_TIMESTAMP --2017-11-23 19:11:03.320
GETDATE() --2017-11-23 19:11:03.320
DATEPART(YEAR, GETDATE()) --2017
YEAR(GETDATE()) --2017

13,表连接:
13.1 内连接: 语法1:SELECT * FROM A INNER JOIN B ON A.ID = B.AID;

可是,在SQL SE奥迪Q7VELacrosse二〇〇七中提供了一发便捷的不二秘技,那正是"PIVOT"关系运算符。(相反的“列转行”是UNPIVOT卡塔 尔(阿拉伯语:قطر‎,以下是选取PIVOT实现“行转列”的SQL语句

语法2:SELECT * FROM A, B WHERE A.ID = B.ID

【PS:以下语句是为表起小名,况且询问内定表的指定字段音信】

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

SELECT a.c1, b.c1 FROM A a INNER JOIN B b ON A.ID = B.AID;

13.2 外连接:
13.2.1 左连接:SELECT * FROM A LFET JOIN B ON A.ID = B.AID;
13.2.2 右连接:SELECT * FROM A RIGHT JOIN B ON A.ID = B.AID;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
【PS:左连接 与 右连接 是有分别的,】详细情形可查看:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
13.3 全连接 :SELECT * FROM 表1 FULL JOIN 表2 ON 表1.ID = 表2.表1_ID;

14,UNION JOIN :创设叁个带有四个表中全部行的表
SELECT * FROM A union JOIN B ON A.ID = B.AID;
等价于:
SELECT * FROM A UNION ALL SELECT * FROM B
【PS:两表必得有相通数量的列,对应的数据类型要协作】

15,关于空行:

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

语句7:(查询客商名不为空的数据行)【IS NULL : 与 IS NOT NULL 相反】

SELECT * FROM USERS WHERE NAME IS NOT NULL
【PS: NAME = NULL 与 NAME IS NULL 是不相同等的】

语句8:查询客商表中的客商名,若为空,则用‘未知’代替

SELECT ISNULL(Name, '未知') FROM USERS

16,BETWEEN...AND... / IN() / NOT IN() --范围性条件

17,条件逻辑运算符:AND / O奇骏 / NOT

18,成立新表1
语句9:成立三个有的时候表【#temp: 表名加#,注明创设一时表,不然为世代表|

而是MSDN上的叙说太过头正统得体,作者看了半天还未弄精晓怎么样利用PIVOT,搞不清楚PIVOT里面包车型地铁语法的意义。于是又google了超级多材质,以致经过地方提到的WEEK_INCOME表例子作了试验,最后搞通晓了其用法。在网络有篇博文解释的很好:T-SQL PIVOT語法剖析與實戰,基本上作者要写的正是参照该博文,再加上本人一点民用理解。

临时表的周期:查询窗口关闭即销毁】

SELECT ID, USERNAME, LOGINID, EMAIL INTO #TEMP FROM USERS

语句10:用处:能够复制二个表结构:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * INTO USERS_L FROM USEXC60S --复制表结构,同期复制表数据,不过Infiniti定,无主键
SELECT * INTO USERS_L FROM USE福睿斯S WHERE 1=0 --复制表结构,不过不复制表数据,无束缚
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

19,操作数据:
创立表后,插入数据:使用第5点INSERT INTO;
【PS:插入的数目中,有外键列存在的话,则插入的数码在外键表中必得存在,否则插入战败。--[保障数据完整性]】

履新数据:
立异一条数据中的多少个字段:

要掌握PIVOT语法,正是要了然微软何以如此设计PIVOT,但自己深信是实际必要催生设计思路,所以算是我们依旧要弄了解什么是“行转列”:

语句11:

UPDATE A SET NAME='JAKE',LOGINID='10001' WHERE ID = 1

去除数据:

好端端境况下的查询结果是那般:

语句12:

DELETE FROM USERS WHERE ID = 1;
数据量大时,可用:TRUNCATE TABLE
【PS:TRUNCATE不施行日志操作,不补助恢复生机删除的数据】
【英特网说采用delete误删除的: 能够运用mdfview程序回复,下载地址:
【作者没试过...】

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

行转列后是那样:

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

也等于说,行转列后,原本的有个别列的值变做了列名,在这里处就是原先WEEK列的值“星期二”,"周三"..."星期天"边做了列名,而大家供给做的另一个行事就是计量这几个列的值(这里的“总括”其实正是PIVOT里面包车型大巴聚合函数(sum,avg等)卡塔 尔(阿拉伯语:قطر‎

当今整合注释来剖判一下PIVOT语法(在这里以前最棒看看自家上边提到博文:T-SQL 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--别名一定要写

如上是本人对PIVOT的通晓,笔者尽所能表明出来。可是话说回来,个人的通晓的点子也不一致,有如自身起头看了大多篇博文,都并未有搞精通PIVOT用法。结果要么硬的通过例子和别人的博文再增进考虑才弄懂了,所以风流洒脱旦各位看了本篇之后仍不可能精通,那很正规,合作例子再增多自身动脑筋,慢慢的定能精通。

 

TAG标签:
版权声明:本文由金沙澳门唯一官网发布于数据库管理,转载请注明出处:PIVOT与用法解释