select distinct city from A 都只有济南,青岛,威海 这种只有缔造个临时表来做
with tab as ( select '济南' cityNm from dual union all select '青岛' cityNm from dual union all select '淄博' cityNm from dual union all select '枣庄' cityNm from dual union all select '烟台' cityNm from dual union all select '潍坊' cityNm from dual union all select '东营' cityNm from dual union all select '济宁' cityNm from dual union all select '泰安' cityNm from dual union all select '威海' cityNm from dual union all select '日照' cityNm from dual union all select '莱芜' cityNm from dual union all select '临沂' cityNm from dual union all select '德州' cityNm from dual union all select '聊城' cityNm from dual union all select '滨州' cityNm from dual union all select '菏泽' cityNm from dual ), tab2 as ( select '济南' cityNm from dual union all select '青岛' cityNm from dual union all select '威海' cityNm from dual ) select * from tab minus select * from tab2 ------------------------------------------- cityNm ------ 淄博 枣庄 东营 烟台 潍坊 济宁 泰安 日照 莱芜 临沂 德州 聊城 滨州 菏泽
所以我觉得,oracle可以增加这么一个函数,例如“XXX”,这样的话,上面的SQL就可以写成: select ctiy from A where city XXX('济南','青岛','烟台','威海','淄博','潍坊','日照','泰安','德州','济宁','聊城','菏泽','滨州','临沂','东营','莱芜','枣庄');查询结果是“济南,青岛,威海”在17地市的反集,这个函数看起来很容易实现,大家觉得呢?
排除列表 表Aselect distinct city from B where B.city not in (select distinct city from A);
比如说B表
select * from b where not exists(select null from a where a.city=b.city)
难道oracle自己会知道 山东省一共有17个地市?
这种只有缔造个临时表来做
(
select '济南' cityNm from dual union all
select '青岛' cityNm from dual union all
select '淄博' cityNm from dual union all
select '枣庄' cityNm from dual union all
select '烟台' cityNm from dual union all
select '潍坊' cityNm from dual union all
select '东营' cityNm from dual union all
select '济宁' cityNm from dual union all
select '泰安' cityNm from dual union all
select '威海' cityNm from dual union all
select '日照' cityNm from dual union all
select '莱芜' cityNm from dual union all
select '临沂' cityNm from dual union all
select '德州' cityNm from dual union all
select '聊城' cityNm from dual union all
select '滨州' cityNm from dual union all
select '菏泽' cityNm from dual
),
tab2 as
(
select '济南' cityNm from dual union all
select '青岛' cityNm from dual union all
select '威海' cityNm from dual
)
select * from tab minus select * from tab2
-------------------------------------------
cityNm
------
淄博
枣庄
东营
烟台
潍坊
济宁
泰安
日照
莱芜
临沂
德州
聊城
滨州
菏泽
select ctiy from A where city XXX('济南','青岛','烟台','威海','淄博','潍坊','日照','泰安','德州','济宁','聊城','菏泽','滨州','临沂','东营','莱芜','枣庄');查询结果是“济南,青岛,威海”在17地市的反集,这个函数看起来很容易实现,大家觉得呢?