年月 values
A001 200502 10
A001 200503 20
A001 200505 30
B001 200503 5
B001 200504 33
B001 200505 15要生成新表如下
200505 200504 200503 200502
A001 30 20
B001 15 33 5
sql 怎么写,谢谢
A001 200502 10
A001 200503 20
A001 200505 30
B001 200503 5
B001 200504 33
B001 200505 15要生成新表如下
200505 200504 200503 200502
A001 30 20
B001 15 33 5
sql 怎么写,谢谢
解决方案 »
- shell脚本 导出sequence,如何把执行结果导成sql文件
- 用select查询currval和nextval时,两个值一样
- 怎么我没找到报表开发相关的版块啊?
- 关于sum函数的效率问题?
- 大批量导入数据问题(ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'')
- 怎么得到用户下的所有表名和注释
- 大虾大虾,我是菜鸟菜鸟,看到请留言(关于导出问题)
- 谁知道国内哪有下载oracle9i的
- 为什么我的奔四装不上oracle817
- oracle日期问题
- 求数据库开发方面的试卷?
- @ @ @ 急急急,求一个 难 SQL 刚刚工作,望多多帮助! 谢谢! 100分
---------------------------------------------------------------------
create table TabName(ITEM VARCHAR2(10),年月 VARCHAR2(10),values INT)
insert into TabName VALUES('A001','200502',10);
insert into TabName VALUES('A001','200503',20);
insert into TabName VALUES('A001','200505',30);
insert into TabName VALUES('B001','200503',5 );
insert into TabName VALUES('B001','200504',33);
insert into TabName VALUES('B001','200505',15);
declare col1 TabName.年月%type; --定义类型
sqlstr varchar2(8000);
cursor c_try is select distinct 年月 from TabName;
begin
sqlstr:='';
open c_try;
loop
fetch c_try into col1;
exit when c_try%notfound;
sqlstr:=sqlstr||', sum(decode(年月,'''||col1||''',values,null)) as '||col1;
exit when c_try%notfound;
end loop;
close c_try;
sqlstr:='create table tt as select '||substr(sqlstr,2)||' from TabName';
--tt为刚创建的一个保存结果的表
execute immediate sqlstr;
end;select * from tt;
--------------------------------------------------------------------
create table TabName(ITEM VARCHAR2(10),年月 VARCHAR2(10),values INT)
insert into TabName VALUES('A001','200502',10);
insert into TabName VALUES('A001','200503',20);
insert into TabName VALUES('A001','200505',30);
insert into TabName VALUES('B001','200503',5 );
insert into TabName VALUES('B001','200504',33);
insert into TabName VALUES('B001','200505',15);
select
ITEM,
SUM(decode(年月,'200502',values,null)) as '200502',
SUM(decode(年月,'200503',values,null)) as '200503',
SUM(decode(年月,'200504',values,null)) as '200504',
SUM(decode(年月,'200505',values,null)) as '200505'
from
TabName
group by
ITEM
可不可以不用pl/sql吗?
就写句sql语句能实现吗
select to_char(max(date),'yyyymm') from tablename
select to_char(max(date)-interval '1' month,'yyyymm') from tablename
select to_char(max(date)-interval '2' month,'yyyymm') from tablename
select to_char(max(date)-interval '3' month,'yyyymm') from tablename
sum(y.年月1) as 200505,sum(y.年月2) as 200504,
sum(y.年月3) as 200503,sum(y.年月4) as 200502
from
(
select x.部门,
decode(x.年月,'200505',x.num ) as 年月1,decode(x.年月,'200504',x.num ) as 年月2,
decode(x.年月,'200503',x.num ) as 年月3,decode(x.年月,'200502',x.num ) as 年月4
from (selecta.部门,a.年月 ,count(values) num
from 表 a
group by a.部门,a.年月
) x
) ygroup by y.部门