T-SQL 经典行专列、列转行,分页及存储过程(转)

简介: T-SQL 经典编程 说明:本实例是以 SQL Server 2005 为运行环境的。 准备工作:创建一个名为 DB 的数据库(CREATE DATABASE DB)。   一、T-SQL 行转列 1、创建如下表 CREATE  TABLE [Scores]  (       [ID]...

T-SQL 经典编程

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

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

 

一、T-SQL 行转列

1、创建如下表

CREATE  TABLE [Scores]  (

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

      [StuNo] INT,                 --学号

      [Subject] NVARCHAR(30), --科目

      [Score] FLOAT                --成绩

)

GO

 

INSERT INTO [Scores]

SELECT 100, '语文', 80 UNION

SELECT 100, '数学', 75 UNION

SELECT 100, '英语', 70 UNION

SELECT 100, '生物', 85 UNION

SELECT 101, '语文', 80 UNION

SELECT 101, '数学', 90 UNION

SELECT 101, '英语', 70 UNION

SELECT 101, '生物', 85

 

CREATE  TABLE [Student]  (

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

      [StuName] NVARCHAR(30),         --姓名

      [Sex] NVARCHAR(30),             --性别

      [Age] CHAR(2)                    --年龄

)

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

 

注意:该存储过程是扩展的,还可以改为万能分页存储过程,只需再加上一些参数,再改改就好了。

http://wenku.baidu.com/view/280ba5d226fff705cc170a29.html

目录
相关文章
|
2月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第18天】SQL Server 存储过程具有提高性能、增强安全性、代码复用和易于维护等优点。它可以减少编译时间和网络传输开销,通过权限控制和参数验证提升安全性,支持代码共享和复用,并且便于维护和版本管理。然而,存储过程也存在可移植性差、开发和调试复杂、版本管理问题、性能调优困难和依赖数据库服务器等缺点。使用时需根据具体需求权衡利弊。
|
1月前
|
SQL 缓存 Java
【详细实用のMyBatis教程】获取参数值和结果的各种情况、自定义映射、动态SQL、多级缓存、逆向工程、分页插件
本文详细介绍了MyBatis的各种常见用法MyBatis多级缓存、逆向工程、分页插件 包括获取参数值和结果的各种情况、自定义映射resultMap、动态SQL
【详细实用のMyBatis教程】获取参数值和结果的各种情况、自定义映射、动态SQL、多级缓存、逆向工程、分页插件
|
2月前
|
存储 SQL 缓存
SQL Server存储过程的优缺点
【10月更文挑战第22天】存储过程具有代码复用性高、性能优化、增强数据安全性、提高可维护性和减少网络流量等优点,但也存在调试困难、移植性差、增加数据库服务器负载和版本控制复杂等缺点。
121 1
|
2月前
|
存储 SQL 数据库
Sql Server 存储过程怎么找 存储过程内容
Sql Server 存储过程怎么找 存储过程内容
132 1
|
2月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第17天】SQL Server 存储过程是预编译的 SQL 语句集,存于数据库中,可重复调用。它能提高性能、增强安全性和可维护性,但也有可移植性差、开发调试复杂及可能影响数据库性能等缺点。使用时需权衡利弊。
|
2月前
|
存储 SQL 数据库
SQL Server 临时存储过程及示例
SQL Server 临时存储过程及示例
60 3
|
7月前
|
存储 SQL 数据库
数据库sql语句-----游标和存储过程
数据库sql语句-----游标和存储过程
61 1
|
2月前
|
存储 SQL 安全
|
2月前
|
存储 SQL 数据库
使用SQL创建视图和存储过程
使用SQL创建视图和存储过程
24 0
|
3月前
|
SQL JSON 分布式计算
ODPS SQL ——列转行、行转列这回让我玩明白了!
本文详细介绍了在MaxCompute中如何使用TRANS_ARRAY和LATERAL VIEW EXPLODE函数来实现列转行的功能。