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。怎么恢复呢？ 你可以简单地认为适合的就是好，不适合就是不好。 对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。 所以你总能得到相应的升级版本，来满足你的需求。 可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。 对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。 同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。 对于微软系列的东西除了一遍遍尝试还真没有太好的办法 从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。
页:
[1]