select product_id,max(stock_refer) as stock_refer, max(storck_refer1) as storck_refer1 from product group by product_id
group by 适合数据量比较小的情况,上百万的数据跑起来就吃力了,也希望有好的方法
select nvl(a.product_id,b.product_id), stock_refer, stock_refer1 from (select product_id, stock_refer from product where stock_refer is not null) a full outer join (select product_id, stock_refer1 from product where stock_refer1 is not null) b on a.product_id = b.product_id; 测试如下 create table product (product_id number,stock_refer varchar2(10),stock_refer1 varchar2(10));insert into product select 1,'少量',null from dual; insert into product select 1,null,'少量' from dual; insert into product select 2,'少量',null from dual; insert into product select 2,null,'少量' from dual; SQL> select nvl(a.product_id,b.product_id), stock_refer, stock_refer1 2 from (select product_id, stock_refer 3 from product 4 where stock_refer is not null) a 5 full outer join 6 (select product_id, stock_refer1 7 from product 8 where stock_refer1 is not null) b 9 on a.product_id = b.product_id;NVL(A.PRODUCT_ID,B.PRODUCT_ID) STOCK_REFER STOCK_REFER1 ------------------------------ ----------- ------------ 1 少量 少量 2 少量 3 少量
支持3楼,再select语句前加上一句create table pp as(假设新表名为PP),将所得结果导入到新表: create table pp as select nvl(a.product_id,b.product_id), stock_refer, stock_refer1 from (select product_id, stock_refer from product where stock_refer is not null) a full outer join (select product_id, stock_refer1 from product where stock_refer1 is not null) b on a.product_id = b.product_id;
max(storck_refer1) as storck_refer1 from product
group by product_id
from (select product_id, stock_refer
from product
where stock_refer is not null) a
full outer join
(select product_id, stock_refer1
from product
where stock_refer1 is not null) b
on a.product_id = b.product_id;
测试如下
create table product (product_id number,stock_refer varchar2(10),stock_refer1 varchar2(10));insert into product select 1,'少量',null from dual;
insert into product select 1,null,'少量' from dual;
insert into product select 2,'少量',null from dual;
insert into product select 2,null,'少量' from dual; SQL> select nvl(a.product_id,b.product_id), stock_refer, stock_refer1
2 from (select product_id, stock_refer
3 from product
4 where stock_refer is not null) a
5 full outer join
6 (select product_id, stock_refer1
7 from product
8 where stock_refer1 is not null) b
9 on a.product_id = b.product_id;NVL(A.PRODUCT_ID,B.PRODUCT_ID) STOCK_REFER STOCK_REFER1
------------------------------ ----------- ------------
1 少量 少量
2 少量
3 少量
create table pp as
select nvl(a.product_id,b.product_id), stock_refer, stock_refer1
from (select product_id, stock_refer
from product
where stock_refer is not null) a
full outer join
(select product_id, stock_refer1
from product
where stock_refer1 is not null) b
on a.product_id = b.product_id;