例一: 在Sql Server的帮助文档中,对Pivot函数是这样解释的: 可以使用 PIVOT 和 UNPIVOT 关系运算符对表表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一转换为输出中的多个列来转换表表达式,并在必要时对最终输出中所需的任何其余的列执
例一:
在Sql Server的帮助文档中,对Pivot函数是这样解释的:
可以使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式,并在必要时对最终输出中所需的任何其余的列值执行聚合.
对第一次使用PIVOT函数的朋友来说,这样的解释很难让大家理解,下面编辑用PIVOT函数来实现一个行转列的功能,以便让读者更容易理解该函数.
注意:PIVOT是Sql Server2005的新函数,2005前行转列请参看本站:
SQLServer中(行列转换)行转列及列转行且加平均值及汇总值
先创建一个工资表:
Create Table Salary
(
HrName varchar(50),
Monthly varchar(50),
Money money
)
往表中插入数据:
insert into Salary(HrName,Monthly,[Money])
select '张三','一月','3000'
union all
select '张三','二月','3200'
union all
select '张三','三月','3500'
union all
select '李四','一月','3800'
union all
select '李四','二月','4200'
union all
select '李四','三月','3900'
union all
select '张三','一月','2000'
查看正常的数据:
select * from Salary
结果:
HrName Monthly Money
张三 一月 3000.00
张三 二月 3200.00
张三 三月 3500.00
李四 一月 3800.00
李四 二月 4200.00
李四 三月 3900.00
张三 一月 2000.00
查看行转列后的数据:
select HrName as '姓名',[一月],[二月],[三月] from Salary
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) as pvt
结果:
姓名 一月 二月 三月
李四 3800.00 4200.00 3900.00
张三 5000.00 3200.00 500.00
注意:
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) 中的sum([Money]),这里必须是聚合函数,比如是min,max等。
in ([一月],[二月],[三月])中的[一月],[二月],[三月]即为Monthly的Value,又为新结果集的列名.
如果我们将其中的一月改为四月,因为数据源中没有四月的记录,所以四月查询出来应该为Null.
测试:
select HrName as '姓名',[四月],[二月],[三月] from Salary
pivot(sum([Money]) for Monthly in ([四月],[二月],[三月])) as pvt
结果:
姓名 四月 二月 三月
李四 NULL 4200.00 3900.00
张三 NULL 3200.00 3500.00
例二:
在SQLServer 2000环境中,如果要实现交叉表格报表,主要是靠一系列复杂的 SELECT...CASE 语句.
其实现过程请参阅这里T-SQL 交叉报表(行列互换) 交叉查询 旋转查询
在SQLServer 2005中我们可以使用PIVOT关系运算符来实现行列转换.
还是以学生成绩表来举例:
id姓名 科目 成绩
1 张三 语文 60
2 张三 数学 65
3 张三 外语 70
4 李四 语文 80
5 李四 数学 90
6 李四 外语 85
7 王五 语文 70
8 王五 数学 71
9 王五 外语 75
10 赵六 语文 64
11 赵六 数学 67
12 赵六 外语 76
查询后得出:
姓名 语文数学外语
李四 80 90 85
王五 70 71 75
张三 60 65 70
赵六 64 67 76
--准备数据:
select * from sysobjects where [xtype]='u'
go
if exists(select id from sysobjects where name='studentscore')
drop table studentscore--删除与实验冲突的表
go
create table studentscore--创建实验表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
subject nvarchar(20) not null,
score int not null
)
go
select * from studentscore
go
--添加实验数据
insert studentscore values ('张三','语文','60');
insert studentscore values ('张三','数学','65');
insert studentscore values ('张三','外语','70');
insert studentscore values ('李四','语文','80');
insert studentscore values ('李四','数学','90');
insert studentscore values ('李四','外语','85');
insert studentscore values ('王五','语文','70');
insert studentscore values ('王五','数学','71');
insert studentscore values ('王五','外语','75');
insert studentscore values ('赵六','语文','64');
insert studentscore values ('赵六','数学','67');
insert studentscore values ('赵六','外语','76');
go
select * from studentscore
go
使用 SELECT...CASE 语句实现代码如下
select [name],
语文=max(case
when subject='语文' then score else 0
end),
数学=max(case
when subject='数学' then score else 0
end),
外语=max(case
when subject='外语' then score else 0
end)
from studentscore
group by [name]
结果:
下面我们使用PIVOT关系运算符来实现行列转换
select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'
from (select score,subject,[name] from studentscore) as ss
pivot
(
sum(score) for subject in([语文],[数学],[外语])
) as pvt
结果:用较少的代码完成了交叉表格报表
============================
对于这种方法要注意的一点是,我们使用sum()聚合函数,表面上没有指定按什么方式分组,但是自动按照name列分组了.
怎么做到的呢?原来pivot关系运算符会根据前面的对象中的列来自行判断,在这个例子中pivot前面的对象是ss,是个子查询,这个子查询中只有三列,score,subject和[name],但是pivot运算符内部使用了score和subject这两列,那么肯定是对[name]分组.
所以我们得出,pivot运算符的分组规则是,跟随对象中的那些不在pivot运算符内部的列:
为了好理解我们再写一个例子:
--在ss这个子查询中,多加一列id
--那么pivot应该按照name和id进行分组
select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'
from (select score,subject,[name],id from studentscore) as ss
pivot
(
sum(score) for subject in([语文],[数学],[外语])
) as pvt
结果:验证了我们的设想
UNPIVOT关系运算符从字面上来看,就知道它的用途正好和PIVOT相反,下面举例说明:
if exists(select id from sysobjects where name='studentscore')
drop table studentscore--删除与实验冲突的表
go
create table studentscore--创建实验表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
yuwen int not null,
shuxue int not null,
waiyu int not null
)
go
select * from studentscore
go
--添加实验数据
insert studentscore values ('张三','60','65','70');
insert studentscore values ('李四','80','90','86');
insert studentscore values ('王五','70','71','75');
insert studentscore values ('赵六','64','67','76');
go
select * from studentscore
go
结果:
SELECT id, [name],subject, score
FROM
(SELECT id,[name], 语文=yuwen, 数学=shuxue, 外语=waiyu
FROM studentscore) as ss
UNPIVOT
(score FOR subject IN
(语文, 数学, 外语)
)AS unpvt
结果:

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Dreamweaver Mac version
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version
Useful JavaScript development tools