参考create table tab1 (槽号 char(3), 品味 char(10), 日期 char(10) ); insert into tab1 select '101','al99.90', '2005-1-30' from dual; insert into tab1 select '102','al99.90', '2005-1-30' from dual;commit;create table pingwei (品味 char(10));insert into pingwei values ('al99.90'); insert into pingwei values ('al99.85'); insert into pingwei values ('al99.70A'); insert into pingwei values ('AL99.70'); insert into pingwei values ('AL99.60'); insert into pingwei values ('AL99.50'); insert into pingwei values ('AL99.00'); commit; select b.品味,nvl(a.车间1,0) 车间1,nvl(车间2,0) 车间2,日期 from( select 品味,sum(车间1)车间1,sum(车间2)车间2,日期 from (select 品味,decode(floor(to_number(substr(槽号,1,1))/3),0,1,0) 车间1, decode(floor(to_number(substr(槽号,1,1))/3),1,1,0) 车间2, 日期 from tab1) a group by 品味,日期) a,pingwei b where a.品味(+)=b.品味; drop table tab1; drop table pingwei;
(槽号 char(3),
品味 char(10),
日期 char(10)
);
insert into tab1
select '101','al99.90', '2005-1-30' from dual;
insert into tab1
select '102','al99.90', '2005-1-30' from dual;commit;create table pingwei
(品味 char(10));insert into pingwei values ('al99.90');
insert into pingwei values ('al99.85');
insert into pingwei values ('al99.70A');
insert into pingwei values ('AL99.70');
insert into pingwei values ('AL99.60');
insert into pingwei values ('AL99.50');
insert into pingwei values ('AL99.00');
commit;
select b.品味,nvl(a.车间1,0) 车间1,nvl(车间2,0) 车间2,日期 from(
select 品味,sum(车间1)车间1,sum(车间2)车间2,日期 from
(select 品味,decode(floor(to_number(substr(槽号,1,1))/3),0,1,0) 车间1,
decode(floor(to_number(substr(槽号,1,1))/3),1,1,0) 车间2,
日期 from tab1) a
group by 品味,日期) a,pingwei b
where a.品味(+)=b.品味;
drop table tab1;
drop table pingwei;