原文:sql pivot、unpivot和partition by用法
演示脚本
IF not exists(SELECT 1 from sys.sysobjects where name = 'Student' AND type = 'U')
BEGIN
CREATE table Student(
ID int identity primary key,
ClassID int default 0 , --班级ID
CourseName nvarchar(20) , --课程
Name nvarchar(20) , --姓名
Score tinyint default 0 --成绩
)
END
GO IF not exists(SELECT 1 from Student)
begin
INSERT INTO Student(ClassID,CourseName,Name,Score)
SELECT 1,'数学','小红',80
UNION ALL
SELECT 1,'语文','小红',90
UNION ALL
SELECT 1,'英语','小红',88
UNION ALL
SELECT 1,'数学','小明',91
UNION ALL
SELECT 1,'语文','小明',78
UNION ALL
SELECT 1,'英语','小明',82
UNION ALL
SELECT 2,'数学','小强',67
UNION ALL
SELECT 2,'语文','小强',76
UNION ALL
SELECT 2,'英语','小强',58
UNION ALL
SELECT 2,'数学','小丽',87
UNION ALL
SELECT 2,'语文','小丽',94
UNION ALL
SELECT 2,'英语','小丽',98
end
GO
一 行列互转pivot和unpivot
方法1：
SELECT Name,
sum(CASE CourseName WHEN '数学' THEN Score ELSE 0 END) as '数学',
sum(CASE CourseName WHEN '语文' THEN Score ELSE 0 END) as '语文',
sum(CASE CourseName WHEN '英语' THEN Score ELSE 0 END) as '英语'
from Student
group BY Name
方法2:
SELECT t.Name,
sum(t.数学) as 数学,
sum(t.语文) as 语文,
sum(t.英语) as 英语
from (
SELECT Name,数学,语文,英语
from Student
PIVOT(
SUM(Score) FOR CourseName IN(数学,语文,英语)
) tb
) t
GROUP BY t.Name
查询结果：
Name 数学 语文 英语
-------------------- ----------- ----------- -----------
小红 80 90 88
小丽 87 94 98
小明 91 78 82
小强 67 76 58
二 partition by
如按每门课分数从高到低排
SELECT CourseName,Name,Score,
row_number() over(partition by CourseName order by Score desc) as Num
from Student
查询结果：
CourseName Name Score Num
-------------------- -------------------- ----- --------------------
数学 小明 91 1
数学 小丽 87 2
数学 小红 80 3
数学 小强 67 4
英语 小丽 98 1
英语 小红 88 2
英语 小明 82 3
英语 小强 58 4
语文 小丽 94 1
语文 小红 90 2
语文 小明 78 3
语文 小强 76 4