开发者社区> awbeci> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
在mongodb服务器上存储和执行 js 函数 - 存储过程
虽然官方不推荐使用将业务逻辑存储在数据库中,并且提示在 mongodb 中执行 javascript 存在性能限制。 但实际上,将 javascript 函数存储在 mongodb 中执行,还是非常有必要的,更方便,许多场景下性能会更好(在执行大量查询处理时不需要将数据传回客户端引擎)。 在目前的版本中,我们任然可以将 javascript 函数存储在 mongodb 内置的一个特殊集合 db.system.js 中,然后这些变量就可以在任何 mongodb 的 javascript 上下文中调用,包括:db.eval()、$where子句、mapReduce。 自从 mongodb 3
279 0
论数据库redo/data存储规划与SSD写倾斜
背景 SSD以其良好的IOPS和读写带宽,正在逐渐取代原来的主流存储,成为企业存储市场的新宠。 在一些对存储IOPS和读写带宽需要较大的重要应用中,例如数据库,SSD的使用也越来越普遍。 但是SSD的寿命和写入量有关,如果没有规划好,可能会拉高故障率和成本。 另一方面,SSD还存
2476 0
MySQL启用SSD存储
版权声明:本文为博主chszs的原创文章,未经博主允许不得转载。 https://blog.csdn.net/chszs/article/details/48049127 MySQL启用SSD存储 作者:chszs,版权所有,未经同意,不得转载。
613 0
MySQL启用SSD存储
MySQL启用SSD存储 有时OS读写慢会降低MySQL服务器的性能,尤其是OS与MySQL使用同一磁盘时。故最好是让MySQL使用单独的磁盘,能使用SSD更好。
698 0
分页存储过程
IF EXISTS(SELECT Name FROM SYSOBJECTS WHERE Name='PrcPager' AND TYPE='P')DROP PROCEDURE PrcPager...
522 0
+关注
awbeci
我的名字叫张威(多好听的名字啊),毕业于哈弗大学(之前和比尔&middot;盖次是同学,自从那家伙创立了威软,我和他的关系就不太好,我发誓要打败他,然后进入安徽机电职业技术学院学习软件技术,你们觉得我会成功么), 现在在一家世界五百强公司任职UFO(想问我是什么公司?叫合肥优尔电子科技有限公司
590
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载