仓酷云

标题: MSSQL网页设计在SQL Server 2005中完成表的行列转换 [打印本页]

作者: 萌萌妈妈 时间: 2015-1-16 22:16
标题: MSSQL网页设计在SQL Server 2005中完成表的行列转换
线上或者测试环境经常出现的误操作总是让DBA同学那么闹心。　　PIVOT和UNPIVOT干系运算符是SQLServer2005供应的新增功效，因而，对晋级到SQLServer2005的数据库利用PIVOT和UNPIVOT时，数据库的兼容级别必需设置为90（可使用sp_dbcmptlevel存储历程设置兼容级别）。
　　在查询的FROM子句中利用PIVOT和UNPIVOT，能够对一个输出表值表达式实行某种操纵，以取得另外一种情势的表。PIVOT运算符将输出表的行扭转为列，并能同时对行实行聚合运算。而UNPIVOT运算符则实行与PIVOT运算符相反的操纵，它将输出表的列扭转为行。
　　在FROM子句中利用PIVOT和UNPIVOT干系运算符时的语法格局以下：
　　[FROM{＜table_source＞}[,...n]]
＜table_source＞::={
　table_or_view_name[[AS]table_alias]
　＜pivoted_table＞|＜unpivoted_table＞
}
＜pivoted_table＞::=table_sourcePIVOT＜pivot_clause＞table_alias
＜pivot_clause＞::=(aggregate_function(value_column)
　FORpivot_column
　　IN(＜column_list＞)
)
＜unpivoted_table＞::=table_sourceUNPIVOT＜unpivot_clause＞table_alias
＜unpivot_clause＞::=(value_columnFORpivot_columnIN(＜column_list＞))
＜column_list＞::=column_name[,...]table_sourcePIVOT＜pivot_clause＞
　　指定对table_source表中的pivot_column列举行透视。table_source能够是一个表、表表达式或子查询。
　　aggregate_function
　　体系或用户界说的聚合函数。注重：不同意利用COUNT(*)体系聚合函数。
　　value_column
　　PIVOT运算符用于举行盘算的值列。与UNPIVOT一同利用时，value_column不克不及是输出table_source中的现有列的称号。
　　FORpivot_column
　　PIVOT运算符的透视列。pivot_column必需是可隐式或显式转换为nvarchar()的范例。
　　利用UNPIVOT时，pivot_column是从table_source中提取输入的列称号，table_source中不克不及有该称号的现有列。
　　IN(column_list)
　　在PIVOT子句中，column_list列出pivot_column中将成为输入表的列名的值。
　　在UNPIVOT子句中，column_list列出table_source中将被提取到单个pivot_column中的一切列名。
　　table_alias
　　输入表的别号。
　　UNPIVOT＜unpivot_clause＞
　　指定将输出表中由column_list指定的多个列的值缩减为名为pivot_column的单个列。
　　罕见的大概会用到PIVOT的情况是：必要天生交织表格报表以汇总数据。交织表是利用较为普遍的一种表格局，比方，-4所示的产物发卖表就是一个典范的交织表，个中的月份和产物品种都能够持续增加。可是，这类格局在举行数据表存储的时分却其实不简单办理，要存储-4如许的表格数据，数据表一般必要计划为-5如许的布局。如许就带来一个成绩，用户既但愿数据简单办理，又但愿可以天生一种可以简单浏览的表格数据。幸亏PIVOT为这类转换供应了便当。

　　-4产物发卖表-5数据表布局
　　假定Sales.Orders表中包括有ProductID（产物ID）、OrderMonth（发卖月份）和SubTotal（发卖额）列，并存储有如表5-2所示的内容。
　　表5-2Sales.Orders表中的内容
ProductIDOrderMonthSubTotal15100.0016100.0025200.0026200.0027300.0035400.0035400.00　　实行上面的语句：
　　SELECTProductID,[5]AS蒲月,[6]AS六月,[7]AS七月
FROM
Sales.OrdersPIVOT
(
SUM(Orders.SubTotal)
FOROrders.OrderMonthIN
([5],[6],[7])
)ASpvt
ORDERBYProductID;
　　在下面的语句中，Sales.Orders是输出表，Orders.OrderMonth是透视列（pivot_column），Orders.SubTotal是值列（value_column）。下面的语句将按上面的步骤取得输入了局集：
　　a．PIVOT起首按值列以外的列（ProductID和OrderMonth）对输出表Sales.Orders举行分组汇总，相似实行上面的语句：
　　SELECTProductID,
OrderMonth,
SUM(Orders.SubTotal)ASSumSubTotal
FROMSales.Orders
GROUPBYProductID,OrderMonth;
　　这时候候将失掉一个如表5-3所示的两头了局集。个中只要ProductID为3的产物因为在5月有2笔发卖纪录，被累加到了一同（值为800）。
　　表5-3Sales.Orders表经分组汇总后的了局
ProductIDOrderMonthSumSubTotal15100.0016100.0025200.0026200.0027300.0035800.00　　b．PIVOT依据FOROrders.OrderMonthIN指定的值5、6、7，起首在了局会合创建名为5、6、7的列，然后从-3所示的两头了局中掏出OrderMonth列中掏出符合合的值，分离安排到5、6、7的列中。此时失掉的了局集的别号为pvt（见语句中ASpvt的指定）。了局集的内容如表5-4所示。
　　表5-4利用FOROrders.OrderMonthIN([5],[6],[7])后失掉的了局集
ProductID5671100.00100.00NULL2200.00200.00200.003800.00NULLNULL　　c．最初依据SELECTProductID,[5]AS蒲月,[6]AS六月,[7]AS七月FROM的指定，从别号pvt了局会合检索数据，并分离将名为5、6、7的列在终极了局会合从头定名为蒲月、六月、七月。这里必要注重的是FROM的寄义，其暗示从经PIVOT干系运算符失掉的pvt了局会合检索数据，而不是从Sales.Orders中检索数据。终极失掉的了局集如表5-5所示。
　　表5-5由表5-2所示的Sales.Orders表将行转换为列失掉的终极了局集
ProductID蒲月六月七月1100.00100.00NULL2200.00200.00200.003800.00NULLNULL　　UNPIVOT与PIVOT实行几近完整相反的操纵，将列转换为行。可是，UNPIVOT其实不完整是PIVOT的逆操纵，因为在实行PIVOT过程当中，数据已被举行了分组汇总，以是利用UNPIVOT其实不会重现原始表值表达式的了局。假定表5-5所示的了局集存储在一个名为MyPvt的表中，如今必要将列标识符“蒲月”、“六月”和“七月”转换到对应于响应产物ID的行值（即前往到表5-3所示的格局）。这意味着必需别的标识两个列，一个用于存储月份，一个用于存储发卖额。为了便于了解，仍然分离将这两个列定名为OrderMonth和SumSubTotal。参考上面的语句：
　　CREATETABLEMyPvt(ProductIDint,蒲月int,六月int,七月int);--创建MyPvt表
GO
　　--将表5-5中所示的值拔出到MyPvt表中
INSERTINTOMyPvtVALUES(1,100,100,0);
INSERTINTOMyPvtVALUES(2,200,200,200);
INSERTINTOMyPvtVALUES(3,800,0,0);
　　--实行UNPIVOT
SELECTProductID,OrderMonth,SubTotal
FROM
　MyPvtUNPIVOT
　(SubTotalFOROrderMonthIN
　　(蒲月,六月,七月)
　)ASunpvt;
　　下面的语句将按上面的步骤取得输入了局集：
　　a．起首创建一个一时了局集的布局，该布局中包括MyPvt表中除IN(蒲月,六月,七月)以外的列，和SubTotalFOROrderMonth中指定的值列（SubTotal）和透视列（OrderMonth）。
　　b．将在MyPvt中逐行检索数据，将表的列称号（在IN(蒲月,六月,七月)中指定）放进OrderMonth列中，将响应的值放进到SubTotal列中。最初失掉的了局集如表5-6所示。
　　表5-6利用UNPIVOT失掉的了局集
ProductIDOrderMonthSubTotal1蒲月1001六月1001七月02蒲月2002六月2002七月2003蒲月8003六月03七月0</p>MyISAMMysql的默认数据库，最为常用。拥有较高的插入，查询速度，但不支持事务

作者: 小魔女 时间: 2015-1-19 06:35
其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

作者: 若相依 时间: 2015-1-26 16:20
只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

作者: 不帅 时间: 2015-2-4 20:21
分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。

作者: 海妖 时间: 2015-2-10 06:45
同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

作者: 飘灵儿 时间: 2015-3-1 02:29
对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

作者: 蒙在股里 时间: 2015-3-10 12:27
至于淘汰的问题，只能说在你的项目周期之内，微软应该都不会倒闭。

作者: 深爱那片海 时间: 2015-3-17 07:10
索引视图2k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

作者: 飘飘悠悠 时间: 2015-3-24 01:46
SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.

欢迎光临仓酷云 (http://ckuyun.com/)