解决方案 »
- 误删undo表空间数据文件,删除不了undo表空间,求解!
- Oracle 10g下,如何将JDBC 10.2.0.1.0升级到11.1.0.7.0?
- 急求:一存储过程写法(分栏写法) 总共就这么多分了
- 求Oracle 11g补丁
- 请问在oracle里如果同时对一个表上锁,什么出现什么情况?
- 从本地exp表到远程oracle,提示 只有 DBA 才能导入由其它 DBA 导出的文件,有什么解决办法?
- [求助]XP下的FORM BUILDER 和REPORT BUILDER的安装问题!!!!
- 强烈建议csdn取消给分制!
- 怎么样在触发器中动态得到当前表名
- 菜鸟的问题:oracle怎么样备份数据表啊,怎样才能连上OMS? 需要怎样配置呢?
- oracle)PL/sql存储过程中直接使用insert...select语句,统计数据分别变0和1?
- 求一SQL写法
insert ta select 1, 'A', 4
union all select 1, 'A', 1
union all select 1, 'B', 2
union all select 1, 'B', 1
union all select 1, 'C', 5
union all select 6, 'A', 2
union all select 6, 'A', 1
union all select 6, 'B', 4
union all select 6, 'B', 3
union all select 6, 'C', 1
union all select 6, 'C', 2
declare @s varchar(4000)
set @s=''
select @s=@s+','+quotename(ClassType)+'=sum(case ClassType when '+quotename(ClassType,'''')
+' then [sum] else 0 end)'
from ta group by ClassType
set @s='select id'+@s+' from ta group by id'
select @s
exec(@s)
SQL> with t as(
2 select '刘洋' name,'语文' subject,80 grade from dual union all
3 select '刘洋','语文',85 from dual union all
4 select '刘洋','语文',86 from dual union all
5 select '张忠','语文',90 from dual union all
6 select '张忠','数学',95 from dual union all
7 select '张忠','英语',99 from dual)
8 select name,
9 max(decode(subject,'语文',grade,0)) 语文,
10 max(decode(subject,'数学',grade,0)) 数学,
11 max(decode(subject,'英语',grade,0)) 英语
12 from t
13 group by name
14 /
NAME 语文 数学 英语
---- ---------- ---------- ----------
刘洋 86 0 0
张忠 90 95 99
select name,
max(decode(subject,'语文',grade,0)) 语文,
max(decode(subject,'数学',grade,0)) 数学,
max(decode(subject,'英语',grade,0)) 英语
from tablename
group by name;