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 语法的主要...
### SQL 2008 行列转换 (Pivot) 的动态实现 #### 知识点一:行列转换(Pivot)的概念与应用场景 在数据库查询中,有时我们需要将数据表中的行转换为列,或者将列转换为行,这种操作被称为行列转换。行列转换在报表...
PowerPivot 与 Excel 和 SharePoint 集成。在 Excel 环境中,PowerPivot for Excel 提供熟悉的工作站式的创作和分析体验。在 SharePoint 场中,PowerPivot for SharePoint 添加了服务器端应用程序和功能,支持对您...
### SQL学习心得与技术要点解析 #### 一、SQL Server 中的数据类型改进 - **`nvarchar(max)` 和 `varchar(max)` 类型**:这两种类型的最大引入显著提升了编程效率,特别是对于处理大型文本数据(CLOB 类型)时,...
首先,"SQL参考手册.chm"可能是一个关于SQL语法和函数的综合指南,它通常包含以下内容: 1. **SQL基本概念**:介绍SQL的起源、用途以及关系数据库模型。 2. **数据操作语言(DML)**:包括INSERT、UPDATE、DELETE...
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
在标题中提到的"SQLPrompt_7.2.2.273_SQL2008R2可用.zip",表明这是SQLPrompt的第7.2.2.273版本,并且特别强调它兼容SQL Server 2008 R2。 SQL Server 2008 R2是Microsoft推出的一个关系数据库管理系统,属于SQL ...
- **Power Query和Power Pivot集成**: 在Excel中可以直接连接到SQL Server,使用Power Query进行数据发现和Power Pivot进行数据分析。 4. **安装过程**: - 首先,运行`SQLEXPR_x64_CHS.exe`安装SQL Server ...
EXEC ('SELECT * FROM (SELECT * FROM tb) a PIVOT(MAX() FOR γ IN (' + @sql + ')) b') ``` #### 三、扩展功能 除了基本的行转列之外,还可以进一步添加统计计算,如平均值、总和等。 ##### 3.1 SQL Server 2000...
带条件查询的参数化动态PIVOT行转列,方便快速完成行转列数据处理。
1. **T-SQL增强**:SQL Server 2005引入了新的T-SQL语言特性,如Cursors的改进、窗口函数、 Common Table Expressions (CTE)、Table Value Parameters 和 PIVOT/UNPIVOT操作等,使得查询编写更为灵活和强大。...
在SQL Server中,"行转列...附带的两个文件"作业.docx"可能是关于这个话题的练习或说明文档,而"作业数据库脚本.sql"可能包含了具体的SQL语句示例,用于演示或测试上述方法。如果需要进一步了解,可以查阅这两个文件。
### SQL语句在SAS中的应用 #### 课程概述 本中级课程主要聚焦于SQL作为数据查询与操作工具的应用。学员将学会如何利用SAS程序...通过本课程的学习,学员将能够充分利用SQL的强大功能,在SAS平台中进行高效的数据分析。
本文主要介绍如何使用SQL Server的PIVOT功能来实现这一操作。 首先,我们看一个简单的例子。假设有一个名为`WEEK_INCOME`的表,存储了一家店铺一周内每一天的收入情况,表结构如下: ``` WEEK_INCOME (WEEK VARCHAR...
本篇文章将深入探讨如何使用聚合函数Pivot和Unpivot来实现这一目标,特别是针对SQL Server数据库。 首先,让我们了解什么是行转列。行转列就是将表格中的某列值变为新的列名,而原本的行数据则对应到这些新列中。举...
首先,我们来探讨SQL Server的基础——SQL语句。SQL(Structured Query Language)是用于管理关系数据库的标准语言,包括了数据查询、数据更新、数据插入和数据删除等操作。掌握基础的SELECT、INSERT、UPDATE和...
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用