SQL 关于pivot
解释: PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式,并在必要时对最终输出中所需的任何其余的列值执行聚合。
还是通过实例来了解用法和作用:
先建表一([dbo].[Customers] 字段说明:customerid -- 消费者id , city -- 所在城市):
CREATE TABLE [dbo].[Customers]( [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL, [city] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, PRIMARY KEY CLUSTERED ( [customerid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
向表一插入数据:
insert into dbo.Customers values('FISSA','Madrid'); insert into dbo.Customers values('FRNDO','Madrid'); insert into dbo.Customers values('KRLOS','Madrid'); insert into dbo.Customers values('MRPHS','Zion');
查询所插入的数据:
select * from dbo.Customer
结果如图:
再建表二([dbo].[Orders] 字段说明:orderid -- 订单id , customerid -- 消费者id):
CREATE TABLE [dbo].[Orders]( [orderid] [int] NOT NULL, [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NULL, PRIMARY KEY CLUSTERED ( [orderid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
向表二插入数据:
insert into dbo.Orders values(1,'FRNDO'); insert into dbo.Orders values(2,'FRNDO'); insert into dbo.Orders values(3,'KRLOS'); insert into dbo.Orders values(4,'KRLOS'); insert into dbo.Orders values(5,'KRLOS'); insert into dbo.Orders values(6,'MRPHS'); insert into dbo.Orders values(7,null);
查询插入的数据:
select * from dbo.orders
结果如图:
操作:
在了解PIVOT之前先看这一个题目:
题目1:每个所在城市的消费者的订单情况(请用“没有订单”,“小于两个订单”,“超过两个订单”)Sql语句如下:
select c.customerid as '消费者', city as '所在城市', case when count(orderid) = 0 then '没有订单' when count(orderid) <= 2 then '小于两个订单' when count(orderid) > 2 then '超过两个订单' end as '种类' from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid group by c.customerid,city
结果如图:
这个很容易理解不作详解
再引入题目2:每个城市的每种订单情况(请用“没有订单”,“小于两个订单”,“超过两个订单”)的数
方法一:用普通的sql语句进行写:
select 所在城市 , count(case when 种类 = '没有订单' then 消费者 end) as [没有订单], count(case when 种类 = '小于两个订单' then 消费者 end) as [小于两个订单], count(case when 种类 = '超过两个订单' then 消费者 end) as [超过两个订单] from ( select c.customerid as '消费者', city as '所在城市', case when count(orderid) = 0 then '没有订单' when count(orderid) <= 2 then '小于两个订单' when count(orderid) > 2 then '超过两个订单' end as '种类' from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid group by c.customerid,city ) as d group by 所在城市
结果如图:
方法二:用pivot看它的神奇之处:
select 所在城市,没有订单,小于两个订单,超过两个订单 from ( select c.customerid as '消费者', city as '所在城市', case when count(orderid) = 0 then '没有订单' when count(orderid) <= 2 then '小于两个订单' when count(orderid) > 2 then '超过两个订单' end as '种类' from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid group by c.customerid,city ) as d pivot(count(消费者) for 种类 in (没有订单,小于两个订单,超过两个订单)) as p
结果如图:
分析(在看分析时请结合方法一,便于理解):
在看方法二的sql语句可以发现pivot把d的两列(消费者,种类 )作为输入参数,而没有作为输入参数(所在城市 )的列作了一个隐式的分组(相当于方法一最后的:group by 所在城市 ),其次它又对 in 子句作了如下操作sql操作:
case when 种类 = '没有订单' then 消费者 end, case when 种类 = '小于两个订单' then 消费者 end, case when 种类 = '超过两个订单' then 消费者 end
最后对每个case表达式作指定聚合函数sql代码如下:
count(case when 种类 = '没有订单' then 消费者 end) as [没有订单], count(case when 种类 = '小于两个订单' then 消费者 end) as [小于两个订单], count(case when 种类 = '超过两个订单' then 消费者 end) as [超过两个订单]
好了通过结合方法一与方法二 就应该能够理解pivot的用法了!
相关推荐
SQL Server 关于 Pivot 详解 在本文中,我们将详细介绍 SQL Server 中的 Pivot 语句,并结合实例分析和实验题目,帮助读者快速掌握其使用。 Pivot 语法 Pivot 语句的基本语法如下: ``` SELECT <非透视的列>, ...
《SQL Server 2005深入内幕——开发人员,最大的赢家》这本书主要探讨了SQL Server 2005的新特性和提升,对于开发人员来说,这些改进极大地提升了开发效率和性能。以下是书中提及的一些关键点: 1. **CLR集成**:...
在SQL Server 2005中,PIVOT是一个重要的新特性,它使得数据的行列转换更为便捷。在SQL Server 2000及其以前版本,如果需要将数据的某列值转换为行对应的列,通常需要使用复杂的CASE语句配合聚合函数(如SUM)来实现...
SQL 行列转换 Pivot UnPivot SQL 行列转换是指将表格中的行数据转换为列数据,或者将列数据转换为行数据。Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要...
PowerPivot 与 Excel 和 SharePoint 集成。在 Excel 环境中,PowerPivot for Excel 提供熟悉的工作站式的创作和分析体验。在 SharePoint 场中,PowerPivot for SharePoint 添加了服务器端应用程序和功能,支持对您...
动态 实现 SQL 2008行列转换的pivot
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
首先,"SQL参考手册.chm"可能是一个关于SQL语法和函数的综合指南,它通常包含以下内容: 1. **SQL基本概念**:介绍SQL的起源、用途以及关系数据库模型。 2. **数据操作语言(DML)**:包括INSERT、UPDATE、DELETE...
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
带条件查询的参数化动态PIVOT行转列,方便快速完成行转列数据处理。
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
《21天精通SQL》是一本旨在帮助读者在短时间内掌握SQL核心技术的教程,特别适合有一定英语基础的学习者。SQL(Structured Query Language),结构化查询语言,是用于管理和处理关系数据库的标准语言。通过21天的学习...
SQL行转列学习谢谢!
在SQL中,PIVOT运算符是一个强大的工具,它允许数据分析师和数据库管理员将数据的行转换为列,从而提供了一种更直观的方式来查看和分析数据。这种转换在数据分析和报表制作中尤其有用,因为它可以帮助将大量行数据...
枢纽表达式PIVOT和UNPIVOT是SQL Server 2005中新增的关系运算符,用于实现行列的旋转。本篇文章将介绍PIVOT和UNPIVOT的简单用法,并提供实例代码。 一、枢纽表达式PIVOT和UNPIVOT的概念 PIVOT和UNPIVOT是SQL ...
在SQL试题中,你可以找到关于查询、更新、插入和删除数据的各种问题,以及更高级的概念如联接、子查询、聚合函数等。本资料集合包含了这些知识点的具体实例,帮助你深化对SQL的理解并提升实践能力。 一、SQL试题及...
在SQL中,Pivot函数是一种强大的工具,用于将行数据转换为列数据,这对于数据分析和报表展示特别有用。在本文中,我们将深入探讨SQL基础教程中的Pivot函数,特别是针对SQLSERVER数据库,尽管其他数据库系统如Oracle...
主要给大家介绍了关于行转列之SQL SERVER PIVOT与用法的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用SQL SERVER具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b') ``` 这个查询将生成一个新的表格,其中每一行对应一个学生,列对应不同的课程,单元格中的值对应该学生在该课程中的...