Server之纵表与横表互转,经典行专列

1,纵表转横表

1,纵表转横表

T-SQL
经典编程

sqlserver的行转列 列转行难点

纵表结构 Table_A:

纵表结构 Table_A:

评释:本实例是以
SQL Server 2005 为运行环境的。

行转列:
1 使用Case when 方式

997755.com澳门葡京 1

997755.com澳门葡京 2

准备工作:创立一个名为 DB
的数据库(CREATE DATABASE DB)。

CREATE TABLE [StudentScores]
(
[UserName] NVARCHAR(20), –学生姓名
[Subject] NVARCHAR(30), –科目
[Score] FLOAT, –成绩
)

改换后的结构:

转换后的组织:

 

INSERT INTO [StudentScores] SELECT ‘Nick’, ‘语文’, 80

997755.com澳门葡京 3

997755.com澳门葡京 4

一、T-SQL
行转列

INSERT INTO [StudentScores] SELECT ‘Nick’, ‘数学’, 90

纵表转横表的SQL示例:

纵表转横表的SQL示例:

1、创设如下表

INSERT INTO [StudentScores] SELECT ‘Nick’, ‘英语’, 70

SELECT  Name ,
        SUM(CASE WHEN Course = N’语文’ THEN Grade
                 ELSE 0
            END) AS Chinese ,
        SUM(CASE WHEN Course = N’数学’ THEN Grade
                 ELSE 0
            END) AS Mathematics ,
        SUM(CASE WHEN Course = N’英语’ THEN Grade
                 ELSE 0
997755.com澳门葡京 ,            END) AS English
FROM    dbo.Table_A
GROUP BY Name

SELECT  Name ,
        SUM(CASE WHEN Course = N’语文’ THEN Grade
                 ELSE 0
            END) AS Chinese ,
        SUM(CASE WHEN Course = N’数学’ THEN Grade
                 ELSE 0
            END) AS Mathematics ,
        SUM(CASE WHEN Course = N’英语’ THEN Grade
                 ELSE 0
            END) AS English
FROM    dbo.Table_A
GROUP BY Name

CREATE  TABLE
[Scores]  (

INSERT INTO [StudentScores] SELECT ‘Nick’, ‘生物’, 85

2,横表转纵表

2,横表转纵表

      [ID] INT
IDENTITY(1,1),    –自增标识

INSERT INTO [StudentScores] SELECT ‘Kent’, ‘语文’, 80

横表结构Table_B:

横表结构Table_B:

      [StuNo]
INT,                 –学号

INSERT INTO [StudentScores] SELECT ‘Kent’, ‘数学’, 90

997755.com澳门葡京 5

997755.com澳门葡京 6

      [Subject]
NVARCHAR(30), –科目

INSERT INTO [StudentScores] SELECT ‘Kent’, ‘英语’, 70

改换后的表结构:

转换后的表结构:

      [Score]
FLOAT                –成绩

INSERT INTO [StudentScores] SELECT ‘Kent’, ‘生物’, 85

997755.com澳门葡京 7

997755.com澳门葡京 8

)

Server之纵表与横表互转,经典行专列。SELECT * FROM [StudentScores]
–如果自身想精晓诸位学生的每科战绩,而且每个学生的全套成就排成一行,那样方便
本人翻看、总括,导出数据

横表转纵表的SQL示例:

横表转纵表的SQL示例:

GO

SELECT
UserName,
MAX(CASE Subject WHEN ‘语文’ THEN Score ELSE 0 END) AS ‘语文’,
MAX(CASE Subject WHEN ‘数学’ THEN Score ELSE 0 END) AS ‘数学’,
MAX(CASE Subject WHEN ‘英语’ THEN Score ELSE 0 END) AS ‘英语’,
MAX(CASE Subject WHEN ‘生物’ THEN Score ELSE 0 END) AS ‘生物’
FROM dbo.[StudentScores]
GROUP BY UserName
2 使用PIVOT 、UNPIVOT运算符

SELECT  Name ,
        ‘Chinese’ AS Course ,
        Chinese AS Score
FROM    dbo.Table_B
UNION ALL
SELECT  Name ,
        ‘Mathematics’ AS Course ,
        Mathematics AS Score
FROM    dbo.Table_B
UNION ALL
SELECT  Name ,
        ‘English’ AS Course ,
        English AS Score
FROM    dbo.Table_B
ORDER BY Name ,
        Course DESC

SELECT  Name ,
        ‘Chinese’ AS Course ,
        Chinese AS Score
FROM    dbo.Table_B
UNION ALL
SELECT  Name ,
        ‘Mathematics’ AS Course ,
        Mathematics AS Score
FROM    dbo.Table_B
UNION ALL
SELECT  Name ,
        ‘English’ AS Course ,
        English AS Score
FROM    dbo.Table_B
ORDER BY Name ,
        Course DESC

 

–方式一
DECLARE @cmdText VARCHAR(8000);
DECLARE @tmpSql VARCHAR(8000);

 

 

INSERT INTO
[Scores]

SET @cmdText = ‘SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS
CreateTime,’ + CHAR(10);
SELECT @cmdText = @cmdText + ‘ CASE PayType WHEN ”’ + PayType + ”’
THEN
SUM(Money) ELSE 0 END AS ”’ + PayType

SELECT 100, ‘语文’,
80 UNION

  • ”’,’ + CHAR(10) FROM (SELECT DISTINCT PayType FROM
    Inpours ) T

SELECT 100, ‘数学’,
75 UNION

SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2)
–注意那里,如若没有加CHAR
(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)

SELECT 100, ‘英语’,
70 UNION

SET @cmdText = @cmdText + ‘ FROM Inpours GROUP BY CreateTime, PayType
‘;

SELECT 100, ‘生物’,
85 UNION

SET @tmpSql =’SELECT CreateTime,’ + CHAR(10);
SELECT @tmpSql = @tmpSql + ‘ ISNULL(SUM(‘ + PayType + ‘), 0) AS ”’ +
PayType + ”’,’ + CHAR(10)
FROM (SELECT DISTINCT PayType FROM Inpours ) T

SELECT 101, ‘语文’,
80 UNION

SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ‘ FROM (‘ + CHAR(10);

SELECT 101, ‘数学’,
90 UNION

SET @cmdText = @tmpSql + @cmdText + ‘) T GROUP BY CreateTime ‘;
PRINT @cmdText
EXECUTE (@cmdText);

SELECT 101, ‘英语’,
70 UNION

–方式二
SELECT
CreateTime,
ISNULL(SUM([支付宝]) , 0) AS [支付宝] ,
ISNULL(SUM([手机短信]) , 0) AS [手机短信] ,
ISNULL(SUM([光大银行卡]), 0) AS [华夏银行卡] ,
ISNULL(SUM([华夏银行卡]), 0) AS [平安银行卡]
FROM
(
SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
CASE PayType WHEN ‘支付宝’ THEN SUM(Money) ELSE 0 END AS ‘支
付宝’ ,
CASE PayType WHEN ‘手机短信’ THEN SUM(Money) ELSE 0 END AS ‘手
机短信’,
CASE PayType WHEN ‘平安银行卡’ THEN SUM(Money) ELSE 0 END AS ‘工
商银行卡’,
CASE PayType WHEN ‘工行卡’ THEN SUM(Money) ELSE 0 END AS ‘建
设银行卡’
FROM Inpours
GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime

SELECT 101, ‘生物’,
85

–方式三
SELECT
CreateTime, [支付宝] , [手机短信],
[兴业银行卡] , [平安银行卡]
FROM
(
SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType,
Money
FROM Inpours
) P
PIVOT (
SUM(Money)
FOR PayType IN
([支付宝], [手机短信], [华夏银行卡], [华夏银行卡])
) AS T
ORDER BY CreateTime

 

 

CREATE  TABLE
[Student]  (

–列转行 主要透过 Union all ,max
来促成

      [ID] INT
IDENTITY(100,1),     –自增标识,学号

CREATE TABLE ProgrectDetail
(
ProgrectName NVARCHAR(20), –工程名称
OverseaSupply INT, –国外供应商须要数量
NativeSupply INT, –国内供应商需求数量
SouthSupply INT, –南方供应商需要数量
NorthSupply INT –北方供应商须求数量
)

      [StuName]
NVARCHAR(30),         –姓名

INSERT INTO ProgrectDetail
SELECT ‘A’, 100, 200, 50, 50
UNION ALL
SELECT ‘B’, 200, 300, 150, 150
UNION ALL
SELECT ‘C’, 159, 400, 20, 320
UNION ALL
SELECT ‘D’, 250, 30, 15, 15

      [Sex]
NVARCHAR(30),             –性别

— 使用 Union all 和max
SELECT ProgrectName, ‘OverseaSupply’ AS Supplier,
MAX(OverseaSupply) AS ‘SupplyNum’
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ‘NativeSupply’ AS Supplier,
MAX(NativeSupply) AS ‘SupplyNum’
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ‘SouthSupply’ AS Supplier,
MAX(SouthSupply) AS ‘SupplyNum’
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ‘NorthSupply’ AS Supplier,
MAX(NorthSupply) AS ‘SupplyNum’
FROM ProgrectDetail
GROUP BY ProgrectName

      [Age]
CHAR(2)                    –年龄

–用UNPIVOT方式
SELECT ProgrectName,Supplier,SupplyNum
FROM
(
SELECT ProgrectName, OverseaSupply, NativeSupply,
SouthSupply, NorthSupply
FROM ProgrectDetail
)T
UNPIVOT
(
SupplyNum FOR Supplier IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

)

 

GO

 

INSERT INTO
[Student]

SELECT ‘张三’,
‘男’, 80 UNION

SELECT ‘李四’,
‘女’, 75

 

两表的数码如下图:

 

2、通过CASE…WHEN
语句和GROUP BY…聚合函数 来达成行转列

SELECT

      StuNo AS
‘学号’,

      MAX(CASE
Subject WHEN ‘语文’ THEN Score ELSE 0 END) AS ‘语文’,

      MAX(CASE
Subject WHEN ‘数学’ THEN Score ELSE 0 END) AS ‘数学’,

      MAX(CASE
Subject WHEN ‘英语’ THEN Score ELSE 0 END) AS ‘英语’,

      MAX(CASE
Subject WHEN ‘生物’ THEN Score ELSE 0 END) AS ‘生物’,

      SUM(Score) AS
‘总分’,

      AVG(Score) AS
‘平均分’

FROM
dbo.[Scores]

GROUP BY
StuNo

ORDER BY StuNo
ASC

结果如下图:

 

3、通过 JOIN…ON
已毕两表联接,展现出学生姓名

SELECT

      MAX(StuNo) AS
‘学号’,

      StuName AS
‘姓名’,

      MAX(CASE
Subject WHEN ‘语文’ THEN Score ELSE 0 END) AS ‘语文’,

      MAX(CASE
Subject WHEN ‘数学’ THEN Score ELSE 0 END) AS ‘数学’,

      MAX(CASE
Subject WHEN ‘英语’ THEN Score ELSE 0 END) AS ‘英语’,

      MAX(CASE
Subject WHEN ‘生物’ THEN Score ELSE 0 END) AS ‘生物’,

      SUM(Score) AS
‘总分’,

      AVG(Score) AS
‘平均分’

FROM dbo.[Scores]
A join [Student] B on (A.StuNo=B.ID)

GROUP BY
StuName

ORDER BY StuName
ASC

结果如下图:

 

4、通过 PIVOT
落成行转列

SELECT

StuNo AS
‘学号’,

StuName AS
‘姓名’,

AVG(语文) AS
‘语文’,

AVG(数学) AS
‘数学’,

AVG(英语) AS
‘英语’,

AVG(生物) AS
‘生物’

FROM
[Scores]

PIVOT(

    AVG(Score) FOR
Subject IN

   
(语文,数学,英语,生物)

) AS
NewScores

JOIN [Student] ON
(NewScores.StuNo=Student.ID)

GROUP BY
NewScores.StuNo,StuName

ORDER BY StuName
ASC

结果如下图:

 

二、T-SQL列转行

1、创造数量表并插入
4 条数据

CREATE  TABLE
[StudentScores]  (

      [ID] INT
IDENTITY(1,1),        –自增标识

      [StuNo]
INT,                     –学号

      [Chinese]
NVARCHAR(30),     –语文

     
[Mathematics] NVARCHAR(30),   –数学

      [English]
NVARCHAR(30),     –英语

      [Biology]
NVARCHAR(30)         –生物

)

GO

 

INSERT INTO
[StudentScores]

SELECT 100, 80, 85,
75, 80 UNION

SELECT 101, 90, 80,
70, 75 UNION

SELECT 102, 95, 90,
80, 70 UNION

SELECT 103, 60, 70,
80, 85

数据如下图:

 

2、通过 UNION
ALL…MAX 落成列转行

SELECT StuNo,
‘Chinese’ AS Subject, 

       
MAX(Chinese) AS ‘Score’

FROM
[StudentScores] 

GROUP BY
[StuNo]

UNION ALL

SELECT StuNo,
‘Mathematics’ AS Subject, 

       
MAX(Mathematics) AS ‘Score’

FROM
[StudentScores] 

GROUP BY
[StuNo]

UNION ALL

SELECT StuNo,
‘English’ AS Subject, 

       
MAX(English) AS ‘Score’

FROM
[StudentScores] 

GROUP BY
[StuNo]

UNION ALL

SELECT StuNo,
‘Biology’ AS Subject, 

       
MAX(Biology) AS ‘Score’

FROM
[StudentScores] 

GROUP BY
[StuNo]

结果如下图:

      

3、用 UNPIVOT
达成列转行

       SELECT
StuNo, Subject, Score

FROM
[StudentScores]

UNPIVOT

(

    Score FOR
Subject IN

    ([Chinese],
[Mathematics], [English], [Biology])

) AS
NewStudentScores

 

三、T-SQL
分页

1、创设数据库并插入
40000 条数据

CREATE  TABLE
[Pagin]  (

      [ID] INT
IDENTITY(1,1),    –自增标识

      [Number]
INT,                –编号

      [Type]
NVARCHAR(30),       –类型

      [Count]
INT                  –数量

)

GO

 

declare @i
int

set @i = 0

while(@i<10000)

begin

    INSERT INTO
[Pagin] SELECT 10000+@i, ‘A类’, 80+@i%5

    INSERT INTO
[Pagin] SELECT 10000+@i, ‘B类’, 60+@i%10

    INSERT INTO
[Pagin] SELECT 10000+@i, ‘C类’, 70+@i%8

    INSERT INTO
[Pagin] SELECT 10000+@i, ‘D类’, 90+@i%3

    set @i = @i +
1

end

 

2、通过 TOP
达成分页

      方案一:两回TOP 完结,原型如下

SELECT * FROM
(

    SELECT TOP 5 *
FROM (

        SELECT TOP
25 * FROM [Pagin] WHERE ID>0 ORDER BY ID ASC

    ) AS TEMPTABLE1
ORDER BY ID DESC

) AS TEMPTABLE2
ORDER BY ID ASC

 

    SELECT TOP 5 *
FROM (

      SELECT TOP 25
* FROM [Pagin] WHERE ID>0 ORDER BY ID ASC

  ) AS TEMPTABLE1
ORDER BY ID DESC

证实:第三个 TOP
表示页面容量,第三个 TOP 表示页面容量*当前页码数。

弊病:   
1、强制排序,否则无法分页,固然方今基本上查询表都要排序。

2、排序字段不能有空值即null,否则分页结果不符实际境况。

3、多次order by
速度会快吧,有待自己进一步大数据量测试。

      

        
方案二:三遍 TOP 基于NOT IN 达成,原型如下

select top 5 *
from [Pagin]

    where ID not in
(select top 25 ID from [Pagin] order by ID)

order by ID

表达:第四个 TOP
表示页面容量,第四个 TOP 表示页面容量*现阶段页码数。

坏处:   
1、强制排序。

2、排体系必须是绝无仅有列,否则分页景况不符实际。

3、使用not  
in,速度慢。

      

      方案三:一遍TOP 基于MAX 或 MIN 完结,原型如下

         select top
5 * from [Pagin]

where ID >
(select max(p.ID) from (select top 25 ID from [Pagin] order by id) as
p)

order by ID

讲明:第三个 TOP
表示页面容量,第一个 TOP 表示页面容量*眼下页码数。

坏处:   
1、强制排序。

2、排连串必须是唯一列,否则分页境况不符实际。

      

终极总括:在
sqlserver
 分页中,第二第三种方案基本上是淘汰掉的,因为现在基本上什么表都是按照添加命宫来排序,所以那二种方案尚未用,真亏作者也敢公布出去,唯有首先种方案或者略微能用一下,但依然要复杂的拼sql
语句,不便利,要通用于具有表有点难度,象oracle 就很方便了,基于rownum
,传入一个sql
查询语句,那么些查询语句爱怎么写就怎么写,反正有限协理它得到一个结果集就行,不像sqlserver又是讲求唯一健又是讲求必须排序,把一个结出集颠来倒去,不慢才怪呢。

      

方案一的简练存储进程如下:

CREATE PROCEDURE
proc_page

    @pageIndex INT
= 0,              –页索引

    @pageSize INT =
10,              –页大小

    @recordCount
INT = 0 OUTPUT,    –重临纪录总数

    @pageCount INT
= 0 OUTPUT       –重返页总数

AS

    DECLARE @sql
NVARCHAR(1300)     –主sql语句

 

   
–得到记录总数–

    BEGIN

        DECLARE
@recordTotal INT

        SET @sql =
N’SELECT @recordTotal=COUNT(ID) FROM [Pagin] WHERE ID>0′

        EXEC
SP_EXECUTESQL @sql,N’@recordTotal INT OUTPUT’,@recordTotal OUTPUT
–@recordTotal = @recordCount OUTPUT

        SET
@recordCount = @recordTotal

    END

 

   
–总括页总数–

   
IF(@recordCount%@pageSize=0)–尽管记录总数除以页大小的余数为零

        SET
@pageCount = @recordCount/@pageSize

   
ELSE–借使记录总数除以页大小的余数不为零

        SET
@pageCount = @recordCount/@pageSize + 1

 

   
–依据页索引执行分页查询–

   
IF(@pageIndex<=1 OR
@pageIndex>@pageCount)–假诺是率先页,或者该页不设有,则默许也引得为1,即首先页

    BEGIN

        SET
@pageIndex = 1

        SET @sql =
‘SELECT TOP ‘+STR(@pageSize)+’ * FROM [Pagin] WHERE ID>0 ORDER BY
ID ASC’

        EXEC
SP_EXECUTESQL @sql

    END

   
ELSE–若是还是不是第一页,即其余页

    BEGIN

        SET @sql =
‘SELECT * FROM (SELECT TOP ‘+STR(@pageSize)+’ * FROM (SELECT TOP
‘+STR(@pageSize*@pageIndex)+’ * FROM [Pagin] WHERE ID>0 ORDER BY
ID ASC) AS TB1 ORDER BY ID DESC) AS TB2 ORDER BY ID ASC’

        EXEC
SP_EXECUTESQL @sql

    END

 

    PRINT
@sql–打印SQL 语句

GO

 

–测试

DECLARE
@recordCount INT,@pageCount INT

EXEC   proc_page
0,5,@recordCount OUTPUT,@pageCount OUTPUT

PRINT
@recordCount

PRINT
@pageCount

 

小心:该存储进度是扩展的,还是可以改为万能分页存储进程,只需再加上有些参数,再改改就好了。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website