加上其它可以聚合的字段,然后group by 如果type不定,可以使用存储过程来动态拼接类似的SQL
如果id 和 type重复情况下,你想输出什么结果呢?
--create table create table test4 ( id varchar2(4), type number, num number, other1 varchar2(20), other2 varchar2(20) )--insert data declare begin for i in 1 .. 100 loop insert into test4 select '000'||trunc(dbms_random.value(1,9)),trunc(dbms_random.value(1,9)),trunc(dbms_random.value(1,100)),dbms_random.string('m',5),dbms_random.string('x',5) from dual; end loop; commit; end;--查询是否有重复数据 select * from test4 a where rowid > (select min(rowid) from test4 b where a.id = b.id and a.type=b.type) --selectselect id, max(decode(type,1,num,0)) as 类别1, max(decode(type,2,num,0)) as 类别2, max(decode(type,3,num,0)) as 类别3, max(decode(type,4,num,0)) as 类别4, max(decode(type,5,num,0)) as 类别5, max(decode(type,6,num,0)) as 类别6, max(decode(type,7,num,0)) as 类别7, max(decode(type,7,num,0)) as 类别8, max(decode(type,9,num,0)) as 类别9, other1, other2 from test4 group by id,other1,other2 drop table test4
decode(type,'1',num),
decode(type,'2',num)
from t
这样可以么
如果该表还有其他属性
id+type不能确定唯一值的呢?
有限制例如总共5个
有限制可以使用
select id,
max(decode(type,'1',num,0)) as 类别1,
max(decode(type,'2',num,0)) as 类别2
......
from t
group by id
类别是有限制的,但是这个不能分组
这个表id+type不能确定唯一值还有加上其他属性才能确定唯一值的
分组了,那数据不就少了
如果type不定,可以使用存储过程来动态拼接类似的SQL
create table test4
(
id varchar2(4),
type number,
num number,
other1 varchar2(20),
other2 varchar2(20)
)--insert data
declare
begin
for i in 1 .. 100 loop
insert into test4
select '000'||trunc(dbms_random.value(1,9)),trunc(dbms_random.value(1,9)),trunc(dbms_random.value(1,100)),dbms_random.string('m',5),dbms_random.string('x',5) from dual;
end loop;
commit;
end;--查询是否有重复数据
select * from test4 a
where rowid > (select min(rowid) from test4 b where a.id = b.id and a.type=b.type)
--selectselect id,
max(decode(type,1,num,0)) as 类别1,
max(decode(type,2,num,0)) as 类别2,
max(decode(type,3,num,0)) as 类别3,
max(decode(type,4,num,0)) as 类别4,
max(decode(type,5,num,0)) as 类别5,
max(decode(type,6,num,0)) as 类别6,
max(decode(type,7,num,0)) as 类别7,
max(decode(type,7,num,0)) as 类别8,
max(decode(type,9,num,0)) as 类别9,
other1,
other2
from test4
group by id,other1,other2
drop table test4