一、行列转换(将表旋转90度) ,这条SQL语句怎么写?
表T:
姓名 语文 数学 物理
----------------------------------
张三 80 90 85
李四 85 92 82 要求结果:
课程 张三 李四
----------------------
语文 80 85
数学 90 92
物理 85 82
二、ORALCE使用索引能否分别提升SELECT、UPDATE和INSERT命令的执行性能吗?why?
表T:
姓名 语文 数学 物理
----------------------------------
张三 80 90 85
李四 85 92 82 要求结果:
课程 张三 李四
----------------------
语文 80 85
数学 90 92
物理 85 82
二、ORALCE使用索引能否分别提升SELECT、UPDATE和INSERT命令的执行性能吗?why?
解决方案 »
- oracle附加数据库的问题
- 如何在存储过程中调用别的存储过程生成的记录集?
- 请教4句 sql 语句,是面试题,谢谢。
- 如果对一个表进行两次左关联呢?
- %%ORACLE_SID%%T%TS%S.ARC 是什么?
- 请问有没有办法获取数据库中的所有unique或者主键的创建文本
- 各位帮忙,关于在Oracle中同时执行两个sql的问题
- 很简单的一个语句转换问题,在线等待!
- 请问哪有oracle for dos下载
- 谁用过oracle的oo4o code wizard?--救命啊
- 使用instead of 触发器 往视图插入数据更新两个基表时失败!!求助~~
- Error: Too many arguments in call to "sqlcxt()"
对于第一个问题:我的想法是把数据变成下面的形式:name course score
-------------------------
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
然后再变成LZ想要的结果,但是把列名变成数据(语文变成数据值)似乎没有什么好的办法了,等其他的高手好方法出现 ..
对于第二个问题:提出索引这个概念的目的是: 加快查询速度,减少I/O操作和消除磁盘排序. 所以对相关字段建索引可以提高查询的速度, 具体长考下面这篇文章: http://oracle.chinaitlab.com/optimize/38502.html
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/-------------------------------------------------------------------
/*加个平均分,总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下:即姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1
将表数据旋转90度(2007-11-19于海南三亚)将下表数据:
A b c d e
-------------------- ----------- ----------- ----------- -----------
x 1 2 3 4
y 5 6 7 8
z 9 10 11 12转化成如下结果:
a x y z
-------------------- ---------- ---------- ----------
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12*/--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
go--生成中间数据表
declare @s varchar(8000)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ',' + A + ' varchar(10)' from test1
set @s = @s + ')'
exec(@s)
print @s
--借助中间表实现行列转换
declare @name varchar(20)declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid > 1 order by colidopen t_cursorfetch next from t_cursor into @namewhile @@fetch_status = 0
begin
exec('select ' + @name + ' as t into test3 from test1')
set @s='insert into test2 select ''' + @name + ''''
select @s = @s + ',''' + rtrim(t) + '''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor--查看行列互换处理结果
select * from test1
select * from test2--删除表
drop table test1
drop table test2
----------------------------------------------------------------------------
/*固定的写法:*/
select t1.* , t2.y , t3.z from
(select a = 'b' , x = b from test1 where a = 'x') t1,
(select a = 'b' , y = b from test1 where a = 'y') t2,
(select a = 'b' , z = b from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'c' , x = c from test1 where a = 'x') t1,
(select a = 'c' , y = c from test1 where a = 'y') t2,
(select a = 'c' , z = c from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'd' , x = d from test1 where a = 'x') t1,
(select a = 'd' , y = d from test1 where a = 'y') t2,
(select a = 'd' , z = d from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'e' , x = e from test1 where a = 'x') t1,
(select a = 'e' , y = e from test1 where a = 'y') t2,
(select a = 'e' , z = e from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a----------------------------------------------------------------------------
/*
表tb,数据如下:
项目种类 业绩 提成
洗吹类 200 10
外卖 100 5
合计 300 15
转换成:
项目种类 洗吹类 外卖 合计
业绩 200 100 300
提成 10 5 15
*/create table tb
(
项目种类 varchar(10),
业绩 int,
提成 int
)insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)
insert into tb(项目种类,业绩,提成) values('外卖' ,100,5)
insert into tb(项目种类,业绩,提成) values('合计' ,300,15)
goselect 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from
(
select 项目种类 = '业绩',
洗吹类 = case when 项目种类 = '洗吹类' then 业绩 else 0 end,
外卖 = case when 项目种类 = '外卖' then 业绩 else 0 end,
合计 = case when 项目种类 = '合计' then 业绩 else 0 end
from tb
union all
select 项目种类 = '提成' ,
洗吹类 = case when 项目种类 = '洗吹类' then 提成 else 0 end,
外卖 = case when 项目种类 = '外卖' then 提成 else 0 end,
合计 = case when 项目种类 = '合计' then 提成 else 0 end
from tb
) m
group by 项目种类
order by 项目种类 descdrop table tb/*
项目种类 洗吹类 外卖 合计
-------- ----------- ----------- -----------
业绩 200 100 300
提成 10 5 15(所影响的行数为 2 行)
*/--------------------------------------------------------------------------
/*
数据库中tb表格如下
月份 工资 福利 奖金
1月 100 200 300
2月 110 210 310
3月 120 220 320
4月 130 230 330我想得到的结果是项目 1月 2月 3月 4月
工资 100 110 120 130
福利 200 210 220 230
奖金 300 310 320 330就是说完全把表格的行列颠倒,有点像那种旋转矩阵,请问如何用sql 语句实现?
*/if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
@s3 varchar(8000) , @s4 varchar(8000),
@s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
@s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
else @new_fdname + '=' end + '''''' + name + '''''''',
@s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
@s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
@s5 = @s5 + '+'' union all ''+@' + @i,
@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000),
@s2=substring(@s2,2,8000),
@s4=substring(@s4,2,8000),
@s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go--用上面的存储过程测试:create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
goexec p_zj 'Test', '月份' , '项目'drop table Test
drop proc p_zj/*
项目 1月 2月 3月 4月
---- ----------- ----------- ----------- -----------
福利 200 210 220 230
工资 100 110 120 130
奖金 300 310 320 330(所影响的行数为 3 行)
*//*
静态写法(SQL2005)
*/
--测试环境
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go
--测试语句
SELECT * FROM
(
SELECT 考核月份,月份,金额 FROM
(SELECT 月份, 工资, 福利, 奖金 FROM Test) p
UNPIVOT
(金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt--测试结果/*
考核月份 1月 2月 3月 4月
------- ----- ----- ------ -------
福利200210220230
工资100110120130
奖金300310320330
*/--删除环境
Drop table Test
因为update,insert会更新索引,增加了I/O操作!!
insert into test_tab values('张三',80,90,85);
insert into test_tab values('李四',85,92,82);
commit;NAME A B C
---------- ---------- ---------- ----------
张三 80 90 85
李四 85 92 82
select column_name,
sum(decode(name, '张三', decode(column_name, 'A', a, 'B', b, 'C', c), null)) "张三",
sum(decode(name, '李四', decode(column_name, 'A', a, 'B', b, 'C', c), null)) "李四"
from test_tab a, user_tab_cols b
where table_name = 'TEST_TAB'
and b.column_name <> 'NAME'
group by column_name;COLUMN_NAME 张三 李四
------------------------------ ---------- ----------
A 80 85
B 90 92
C 85 82如果动态的,得写过程或者函数了。