- 浏览: 122314 次
- 性别:
- 来自: 西安
文章分类
最新评论
-
DearGrandpa:
sarrow 写道您好,我在使用msys-mingw的时候,遇 ...
Windows下的新玩具 MSYS -
zealotds:
你好!我没有用过msys-cn,但我刚刚看了一下它们的网站。它 ...
Windows下的新玩具 MSYS -
sarrow:
晕掉,缩进没了;重新贴一次。
---------------- ...
Windows下的新玩具 MSYS -
sarrow:
您好,我在使用msys-mingw的时候,遇到了一个疑问,麻烦 ...
Windows下的新玩具 MSYS -
chcan1:
,老师,能把后面几章 的感想和笔触写一下吗,
【Microsoft SQL Server 2008 技术内幕:T-SQL语言基础】五、透视和分组
sql2008 t-sql
基本概念
Pivoting(透视变换)
是把数据从行状态旋转为列状态,处理过程中需要对数值进行聚合
Unpivoting(逆透视变换)
是把数据从列的状态旋转为行的状态。
它通常涉及查询数据的透视状态,将来自单个记录的多个列的值扩展为单个列中具有相同值得多个记录。
换句话说,把透视表中每个源行潜在地转换成多个行,每行代表原透视表的一个指定的列值。
Grouping (分组)
这个不多说了
透视部分的例子
分组部分的例子
基本概念
Pivoting(透视变换)
是把数据从行状态旋转为列状态,处理过程中需要对数值进行聚合
Unpivoting(逆透视变换)
是把数据从列的状态旋转为行的状态。
它通常涉及查询数据的透视状态,将来自单个记录的多个列的值扩展为单个列中具有相同值得多个记录。
换句话说,把透视表中每个源行潜在地转换成多个行,每行代表原透视表的一个指定的列值。
Grouping (分组)
这个不多说了
透视部分的例子
--------------------------------------------------------------------- -- 1. create test table --------------------------------------------------------------------- use tempdb; if OBJECT_ID('dbo.Orders', 'u') is not null drop table dbo.Orders; create table dbo.Orders ( orderid int not null , orderdate date not null -- use datetime type in earlier Sql version , empid int not null , custid varchar(5) not null , qty int not null , constraint PK_Orders primary key(orderid) ) insert into dbo.Orders(orderid, orderdate, empid, custid, qty) values (30001, '20070802', 3, 'A', 10) , (10001, '20071224', 2, 'A', 12) , (10005, '20071224', 1, 'B', 20) , (40001, '20080109', 2, 'A', 40) , (10006, '20080118', 1, 'C', 14) , (20001, '20080212', 2, 'B', 12) , (40005, '20090212', 3, 'A', 10) , (20002, '20090216', 1, 'C', 20) , (30003, '20090418', 2, 'B', 15) , (30004, '20070418', 3, 'C', 22) , (30007, '20090907', 3, 'D', 30); select * from dbo.Orders; --------------------------------------------------------------------- -- 2. standard T-SQL pivoting --------------------------------------------------------------------- select empid , sum(case when custid = 'A' then qty end) as A , sum(case when custid = 'B' then qty end) as B , sum(case when custid = 'C' then qty end) as C , sum(case when custid = 'D' then qty end) as D from dbo.Orders group by empid; --------------------------------------------------------------------- -- 3. pivot operator pivoting --------------------------------------------------------------------- /* grammar * see FROM(Transact-SQL) select ... from <source_table_or_table_expression> pivot(<agg_func>(<agg_element>) for <spreading_element> in (<list_of_target_columns>) ) as <result_talbe_alias>; */ select empid , A, B, C, D -- same with column list in IN clause below from ( select empid, custid, qty from dbo.Orders ) as D pivot (sum(qty) for custid in (A,B,C,D)) as P; /* 1. Pivot运算符不需要显示指定分组元素,即亦不需要指定Group By子句。 Pivot会隐式地将不是聚合、扩展元素的那些元素作为分组元素。 2. 所以一般不会把PIVOT算符应用于源表,而是应用于表表达式。 该表表达式中应该只包含聚合元素,扩展元素和分组元素 如上例D中只包含了聚合元素qty,扩展元素custid,剩下的empid会自然被当作分组元素 3. 即使该源表中只包含这三类元素,还是应该把Pivot应用于表表达式。 因为你不知道什么时候会添加新的列 */ --------------------------------------------------------------------- -- 4. create test table2, a pivot table --------------------------------------------------------------------- use tempdb; if OBJECT_ID('dbo.EmpCustOrders', 'u') is not null drop table dbo.EmpCustOrders; select empid, A, B, C, D into dbo.EmpCustOrders from ( select empid, custid, qty from dbo.Orders ) as D pivot(sum(qty) for custid in (A, B, C, D)) as P; select * from dbo.EmpCustOrders; --------------------------------------------------------------------- -- 5. standard T-SQL unpivoting --------------------------------------------------------------------- select * from ( select empid, custid , case custid when 'A' then A when 'B' then B when 'C' then C when 'D' then D end as qty --select * from dbo.EmpCustOrders cross join ( -- Sql ealier than 2008, need to use select + union below values('A'), ('B'), ('C'), ('D') ) as Custs(custid) ) as D -- remove null rows that represents no relationship in JOIN where qty is not null; --------------------------------------------------------------------- -- 6. unpivot operator unpivoting --------------------------------------------------------------------- /* grammar * see FROM(Transact-SQL) select ... from <source_table_or_table_expression> unpivot(<target_col_to_hold_source_col_values> for <target_col_to_hold_source_col_names> in (<list_of_source_columns>) ) as <result_talbe_alias>; */ select empid , custid, qty -- same as columns in IN clause below from dbo.EmpCustOrders unpivot(qty for custid in (A, B, C, D)) as U; /* 1. Unpivot运算符需要在圆括号内指定: 保存源表列值的目标列名(qty) 保存源表列名的目标列名(custid) 源表的列名列表(A, B, C, D) 2. Unpivot运算符会经历和前边第4个例子(t-sql unpivoting)中相同的三个步骤: 生成副本 提取元素 删除交叉位置上的null值 (此步骤相对于t-sql方案不可选) 3. 经过透视变换后再逆透视不能得到相同的源表。 因为透视变换中的聚合操作丢失了源表中的详细信息,而逆透视不会丢失信息 */
分组部分的例子
use tempdb; --------------------------------------------------------------------- -- 1. grouping examples --------------------------------------------------------------------- select empid, custid, SUM(qty) as sumqty from dbo.Orders group by empid, custid union all select empid, null, SUM(qty) as sumqty from dbo.Orders group by empid union all select null, custid, SUM(qty) as sumqty from dbo.Orders group by custid union all select null, null, SUM(qty) as sumqty from dbo.Orders --------------------------------------------------------------------- -- 2. grouping sets sub clause --------------------------------------------------------------------- -- logically equals to example 1 -- advantages: -- a. less table scan -- b. less code select empid, custid, SUM(qty) as sumqty from dbo.Orders group by GROUPING sets ( (empid, custid) , (empid) , (custid) , () ) --------------------------------------------------------------------- -- 3. cube sub clause --------------------------------------------------------------------- -- equals to example 2 -- CUBE(a,b,c) means the power set of (a, b, c): -- all the combination sets of a, b and c select empid, custid, SUM(qty) as sumqty from dbo.Orders group by cube(empid, custid) -- T-SQL standard --group by empid, custid with cube -- Sql Server standard --------------------------------------------------------------------- -- 4. rollup sub clause --------------------------------------------------------------------- -- ROLLUP(a,b,c) differs from CUBE(a,b,c), it only returns such sub sets: -- (a, b, c), (a, b), (a), () -- which means the level of input sets: a > b > c select YEAR(orderdate), MONTH(orderdate), DAY(orderdate) , SUM(qty) as sumqty from dbo.Orders group by rollup( YEAR(orderdate), MONTH(orderdate), DAY(orderdate) ); -- T-SQL standard --group by YEAR(orderdate), MONTH(orderdate), DAY(orderdate) --with rollup; -- Sql server standard /* equals to: group by GROUPING sets ( (YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) , (YEAR(orderdate), MONTH(orderdate)) , (YEAR(orderdate)) , () ) */ --------------------------------------------------------------------- -- 5. grouping function --------------------------------------------------------------------- -- Indicates whether a specified column expression in GROUP BY is -- aggregated or not. select grouping(empid) as grpemp , grouping(custid) as grpcust , empid, custid, SUM(qty) as sumqty from dbo.Orders group by cube(empid, custid); /* a. 如果像本文中的例子一样,所有的列都设定为not null,那么在结果中出现的 null仅仅为占位符,此时可以根据这些null判断该列是否参与了分组。 b. 但是如果定义了允许为null的列就无法用上边的方法了,此时只能使用GROUPING 函数来显式地指出各个列是否参与了分组运算:返回1表示参与,0则是没有参与 c. GROUPING函数在早期Sql版本中可以和WITH CUBE、WITH ROLLUP搭配使用 */ --------------------------------------------------------------------- -- 5. grouping_id function --------------------------------------------------------------------- -- Sql 2008 -- Returns a integer bitmap representing the input columns' presence -- in grouping operation. The left most bit corresponds to the first -- (left most)column in the input list... select grouping_id(empid, custid) as groupingset , empid, custid, SUM(qty) as sumqty from dbo.Orders group by cube(empid, custid);
发表评论
-
Oracle 11gr2 XE on Linux64
2013-03-19 17:39 1805XE is free to download and use ... -
Installing DB2 Manually
2012-09-15 23:23 1118Summarized from DB2 9.7 Officia ... -
Installing Oracle XE 11g On Redhat Enterpise Linux
2012-06-19 16:10 14681. download rpm zip from Oracle ... -
Installing db2 express c in Ubuntu
2012-05-17 08:57 16370. update your apt database: su ... -
SQL Server 2008 Information Schema Views
2010-04-29 12:04 1149tag: sql server 2008 metadata v ... -
【Microsoft SQL Server 2008 技术内幕:T-SQL语言基础】四、集合运算
2010-03-19 15:46 2910sql2008 t-sql 集合运算 (Set Opera ... -
SQL Server 2008 Metadata Functions
2010-03-18 16:39 942tag: sql server 2008 functions ... -
【Microsoft SQL Server 2008 技术内幕:T-SQL语言基础】三、表表达式
2010-03-15 14:50 2674sql2008 t-sql Sql Server四 ... -
【Microsoft SQL Server 2008 技术内幕:T-SQL语言基础】二、查询篇
2010-03-11 16:04 2905sql2008 t-sql 单表查询 TO ... -
【Microsoft SQL Server 2008 技术内幕:T-SQL语言基础】一、基础篇
2010-03-10 17:51 1880sql2008 t-sql 写在前边 这 ... -
判断连接型临时表是否存在的问题
2009-10-26 13:58 9331.获取表的id object_id(N'dbname.dbo ... -
关于N' '
2009-10-26 13:46 867用于标注Unicode字串。 看到有人说是中文的SQL就不需 ...
相关推荐
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
阅读《Microsoft SQL Server 2008技术内幕:T-SQL查询》,可以充分地理解T-SQL语言和良好的编程实践,学会如何编写更加有效而强大的查询语句。 序言 I 致谢III 前言 V 第1章 逻辑查询处理 1.1 逻辑查询处理的...
本书全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》适合须要学习T-SQL的各级程序员和数据库专业人员,是他们快速掌握T-SQL的必备参考图书。 致谢 引言 第1章 T-SQL查询和编程基础 1.1 理论背景 1.2 ...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑查询...
文件列表中的"Microsoft SQL Server 2005技术内幕:T-SQL查询.part3.rar"和"Microsoft SQL Server 2005技术内幕:T-SQL查询.part4.rar"是该书的后续部分,将覆盖更多高级主题和实践案例,进一步深化对T-SQL的理解。
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》适合须要学习T-SQL的各级程序员和数据库专业人员,是他们快速掌握T-SQL的必备参考图书。 致谢 引言 第1章 T-SQL查询和编程基础 1.1 理论背景 1.2 SQL ...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威指南。该书涵盖了从基本概念到高级特性的全方位知识,旨在帮助读者理解和掌握SQL Server 2008的...
### Microsoft SQL Server 2008技术内幕:T-SQL查询 #### 书籍概述 《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨Microsoft SQL Server 2008中T-SQL查询及其性能优化的专业书籍。本书由Lubor ...
主要包括SQL的基础理论、逻辑查询处理、SELECT查询、连接和子查询、表表达式、过滤和分组、透视转换、修改数据、事务和一致性的处理、可编程对象等内容。 书中并非系统地罗列T-SQL的各种语法元素,而是结合实践中的...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
阅读《Microsoft SQL Server 2008技术内幕:T-SQL查询》,可以充分地理解T-SQL语言和良好的编程实践,学会如何编写更加有效而强大的查询语句。 目录 ------------------------------------------------------------...
第1章 T-SQL查询和编程基础 1.1 理论背景 1.2 SQL SERVER体系结构 1.3 创建表和定义数据完整性 1.4 总结 第2章 单表查询 2.1 SELECT语句的元素 2.2 谓词和运算符 2.3 CASE表达式 2.4 NULL...
本书全面深入地介绍了Microsoft SQL Server 2008 中高级T-SQL 查询、性能优化等方面的内容,以及SQLServer 2008 新增加的一些特性。主要内容包括SQL 的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
【概述】: 本书是Microsoft SQL Server 2008系列中的一本。...主要包括SQL的基础理论、逻辑查询处理、SELECT查询、连接和子查询、表表达式、过滤和分组、透视转换、修改数据、事务和一致性的处理、可编程对象等内容。
【概述】: 本书是Microsoft SQL Server 2008系列中的一本。...主要包括SQL的基础理论、逻辑查询处理、SELECT查询、连接和子查询、表表达式、过滤和分组、透视转换、修改数据、事务和一致性的处理、可编程对象等内容。
《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威书籍。T-SQL是SQL Server的主要编程语言,用于执行数据操作、管理数据库以及创建复杂的存储过程...
主要包括SQL的基础理论、逻辑查询处理、SELECT查询、连接和子查询、表表达式、过滤和分组、透视转换、修改数据、事务和一致性的处理、可编程对象等内容。 书中并非系统地罗列T-SQL的各种语法元素,而是结合实践中的...