仓酷云

标题: SQLServer中(行列转换)行转列及列转行且加均匀值及汇总值 [打印本页]

作者: 愤怒的大鸟 时间: 2015-1-16 14:18
标题: SQLServer中(行列转换)行转列及列转行且加均匀值及汇总值
CSV逻辑上由逗号分割数据的存储引擎成绩：假定有张先生成就表(tb)以下:
姓名课程分数
张三语文74
张三数学83
张三物理93
李四语文74
李四数学84
李四物理94
想酿成(失掉以下了局)：
姓名语文数学物理
----------------
李四748494
张三748393
-------------------
*/

createtabletb(姓名varchar(10),课程varchar(10),分数int)
insertintotbvalues(张三,语文,74)
insertintotbvalues(张三,数学,83)
insertintotbvalues(张三,物理,93)
insertintotbvalues(李四,语文,74)
insertintotbvalues(李四,数学,84)
insertintotbvalues(李四,物理,94)
go

--SQLSERVER2000静态SQL,指课程只要语文、数学、物理这三门课程。(以下同)
select姓名as姓名,
max(case课程when语文then分数else0end)语文,
max(case课程when数学then分数else0end)数学,
max(case课程when物理then分数else0end)物理
fromtb
groupby姓名

--SQLSERVER2000静态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare@sqlvarchar(8000)
set@sql=select姓名
select@sql=@sql+,max(case课程when+课程+then分数else0end)[+课程+]
from(selectdistinct课程fromtb)asa
set@sql=@sql+fromtbgroupby姓名
exec(@sql)

--SQLSERVER2005静态SQL。
select*from(select*fromtb)apivot(max(分数)for课程in(语文,数学,物理))b

--SQLSERVER2005静态SQL。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+],[,)+课程fromtbgroupby课程
set@sql=[+@sql+]
exec(select*from(select*fromtb)apivot(max(分数)for课程in(+@sql+))b)

/*
成绩：在上述了局的基本上加均匀分，总分，失掉以下了局：
姓名语文数学物理均匀分总分
--------------------------
李四74849484.00252
张三74839383.33250
*/

--SQLSERVER2000静态SQL。
select姓名姓名,
max(case课程when语文then分数else0end)语文,
max(case课程when数学then分数else0end)数学,
max(case课程when物理then分数else0end)物理,
cast(avg(分数*1.0)asdecimal(18,2))均匀分,
sum(分数)总分
fromtb
groupby姓名

--SQLSERVER2000静态SQL。
declare@sqlvarchar(8000)
set@sql=select姓名
select@sql=@sql+,max(case课程when+课程+then分数else0end)[+课程+]
from(selectdistinct课程fromtb)asa
set@sql=@sql+,cast(avg(分数*1.0)asdecimal(18,2))均匀分,sum(分数)总分fromtbgroupby姓名
exec(@sql)

--SQLSERVER2005静态SQL。
selectm.*,n.均匀分,n.总分from
(select*from(select*fromtb)apivot(max(分数)for课程in(语文,数学,物理))b)m,
(select姓名,cast(avg(分数*1.0)asdecimal(18,2))均匀分,sum(分数)总分fromtbgroupby姓名)n
wherem.姓名=n.姓名

--SQLSERVER2005静态SQL。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+,,)+课程fromtbgroupby课程
exec(selectm.*,n.均匀分,n.总分from
(select*from(select*fromtb)apivot(max(分数)for课程in(+@sql+))b)m,
(select姓名,cast(avg(分数*1.0)asdecimal(18,2))均匀分,sum(分数)总分fromtbgroupby姓名)n
wherem.姓名=n.姓名)

droptabletb

/*
成绩：假如上述两表相互换一下：即表布局和数据为：
姓名语文数学物理
张三74　　83　　93
李四74　　84　　94
想酿成(失掉以下了局)：
姓名课程分数
------------
李四语文74
李四数学84
李四物理94
张三语文74
张三数学83
张三物理93
--------------
*/

createtabletb(姓名varchar(10),语文int,数学int,物理int)
insertintotbvalues(张三,74,83,93)
insertintotbvalues(李四,74,84,94)
go

--SQLSERVER2000静态SQL。
select*from
(
select姓名,课程=语文,分数=语文fromtb
unionall
select姓名,课程=数学,分数=数学fromtb
unionall
select姓名,课程=物理,分数=物理fromtb
)t
orderby姓名,case课程when语文then1when数学then2when物理then3end

--SQLSERVER2000静态SQL。
--挪用体系表静态生态。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+unionall,)+select姓名,[课程]=+quotename(Name,)+,[分数]=+quotename(Name)+fromtb
fromsyscolumns
wherename!=N姓名andID=object_id(tb)--表名tb，不包括列名为姓名的别的列
orderbycolidasc
exec(@sql+orderby姓名)

--SQLSERVER2005静态SQL。
select姓名,课程,分数fromtbunpivot(分数for课程in([语文],[数学],[物理]))t

--SQLSERVER2005静态SQL，同SQLSERVER2000静态SQL。
--------------------
/*
成绩：在上述的了局上加个均匀分，总分，失掉以下了局：
姓名课程分数
----------------
李四语文74.00
李四数学84.00
李四物理94.00
李四均匀分84.00
李四总分252.00
张三语文74.00
张三数学83.00
张三物理93.00
张三均匀分83.33
张三总分250.00
------------------
*/

select*from
(
select姓名as姓名,课程=语文,分数=语文fromtb
unionall
select姓名as姓名,课程=数学,分数=数学fromtb
unionall
select姓名as姓名,课程=物理,分数=物理fromtb
unionall
select姓名as姓名,课程=均匀分,分数=cast((语文+数学+物理)*1.0/3asdecimal(18,2))fromtb
unionall
select姓名as姓名,课程=总分,分数=语文+数学+物理fromtb
)t
orderby姓名,case课程when语文then1when数学then2when物理then3when均匀分then4when总分then5end

droptabletb
但我们知道，若使用statement，并没有上述需要的数据。试想binlog中记录了一句updatetsetf1=3whereid=3。怎么恢复呢？

作者: 第二个灵魂 时间: 2015-1-17 10:45
你可以简单地认为适合的就是好，不适合就是不好。

作者: 爱飞 时间: 2015-1-20 18:18
对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

作者: 变相怪杰 时间: 2015-1-29 14:14
所以你总能得到相应的升级版本，来满足你的需求。

作者: 柔情似水 时间: 2015-2-6 02:00
可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

作者: 乐观 时间: 2015-2-14 23:46
对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

作者: admin 时间: 2015-3-4 10:46
同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

作者: 简单生活 时间: 2015-3-11 18:28
对于微软系列的东西除了一遍遍尝试还真没有太好的办法

作者: 冷月葬花魂 时间: 2015-3-27 14:09
从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

欢迎光临仓酷云 (http://ckuyun.com/)