sql pivot、unpivot和partition by用法

最新推荐文章于 2024-04-24 08:48:00 发布

weixin_30527323

最新推荐文章于 2024-04-24 08:48:00 发布

阅读量105

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lonelyxmas/p/4382903.html

本文介绍SQL中Pivot和Unpivot操作的应用场景及实现方法，并演示如何使用Partition By进行排名查询。通过具体实例展示了如何将行列互换，以及如何按每门课程的成绩进行排名。

sql pivot、unpivot和partition by用法

原文: 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

View Code

一行列互转pivot和unpivot

　　msdn参考

方法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