哎脏活累活大伙都不愿意干. 说实话我也不愿意干.但看你那么 痛苦.帮助帮助你吧. 下面是创建是测试数据. create type a as object(name_inf varchar2(100),lang_cd varchar2(2),on_flag number); create type b as table of a; create table map_bs_poi_srch(poi_id varchar2(100),poi_name b) nested table poi_name store as ab; insert into map_bs_poi_srch values('0001',b(a('11','FF',0), a('11','FF',0), a('11','FF',0), a('11','FF',0), a('11','FF',0), a('11','FF',0))); insert into map_bs_poi_srch values('0002',b(a('11','FF',0), a('11','FF',1), a('11','FF',0), a('11','FF',1), a('11','FF',0), a('11','FF',0))); commit; 检索所有on_flag为0的poi_id,poi_name select poi_id, poi_name from (select p.poi_id, p.poi_name, (select sum(on_flag) from table (select t.poi_name from map_bs_poi_srch t where t.poi_id = p.poi_id)) sum from map_bs_poi_srch p) where sum = 0
说实话我也不愿意干.但看你那么
痛苦.帮助帮助你吧.
下面是创建是测试数据.
create type a as object(name_inf varchar2(100),lang_cd varchar2(2),on_flag number);
create type b as table of a;
create table map_bs_poi_srch(poi_id varchar2(100),poi_name b)
nested table poi_name store as ab;
insert into map_bs_poi_srch values('0001',b(a('11','FF',0),
a('11','FF',0),
a('11','FF',0),
a('11','FF',0),
a('11','FF',0),
a('11','FF',0)));
insert into map_bs_poi_srch values('0002',b(a('11','FF',0),
a('11','FF',1),
a('11','FF',0),
a('11','FF',1),
a('11','FF',0),
a('11','FF',0)));
commit;
检索所有on_flag为0的poi_id,poi_name
select poi_id, poi_name
from (select p.poi_id,
p.poi_name,
(select sum(on_flag)
from table (select t.poi_name
from map_bs_poi_srch t
where t.poi_id = p.poi_id)) sum
from map_bs_poi_srch p)
where sum = 0