sql pivot、unpivot和partition by用法

weixin_34126215發表於2015-04-01
SQL
原文: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

 

相關文章