原文:
sql pivot、unpivot和partition by用法
View Code
演示脚本
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
