--比如A、B表各3个字段col,col2,col3 select * from A a where not exists (select 1 from B b where b.col1=a.col1 and b.col2=a.col2 and b.col3=a.col3) --真实表有几个字段就写几个
with b as ( select ... 你要得到 的结果集的SQL语句 ) select * from a minus select * from b;
SELECT * FROM (SELECT * FROM TEMP_SHORTNUM_CDR_D A MINUS SELECT T1.* FROM DM.TEMP_SHORTNUM_CDR_D T1, DMCODE.DIM_PLATFORM_TYPE T2 WHERE T1.OPPO_NUMBER_N LIKE T2.PLATFORM_ID || '%') 有36530行 SELECT * FROM TEMP_SHORTNUM_CDR_D A 有44085行, SELECT T1.* FROM DM.TEMP_SHORTNUM_CDR_D T1, DMCODE.DIM_PLATFORM_TYPE T2 WHERE T1.OPPO_NUMBER_N LIKE T2.PLATFORM_ID || '%' 有8019行。数据的数量对不上,估计是like引起的,比如 opp_number:123456 ,platfrom_id :12,123 个数 就会出现两行,怎么解决
select * from tb1 t1 where ... minus select * from tb1 t2 where ...-- 表示:在查询一中存在,而在查询二中不存在的数据集合! -- 别想那么多:巧用 minus 关键字!
--比如A、B表各3个字段col,col2,col3
select *
from A a
where not exists
(select 1 from B b where b.col1=a.col1 and b.col2=a.col2 and b.col3=a.col3) --真实表有几个字段就写几个
select * from a
minus
select * from b;
SELECT *
FROM (SELECT *
FROM TEMP_SHORTNUM_CDR_D A
MINUS
SELECT T1.*
FROM DM.TEMP_SHORTNUM_CDR_D T1, DMCODE.DIM_PLATFORM_TYPE T2
WHERE T1.OPPO_NUMBER_N LIKE T2.PLATFORM_ID || '%')
有36530行
SELECT *
FROM TEMP_SHORTNUM_CDR_D A
有44085行,
SELECT T1.*
FROM DM.TEMP_SHORTNUM_CDR_D T1, DMCODE.DIM_PLATFORM_TYPE T2
WHERE T1.OPPO_NUMBER_N LIKE T2.PLATFORM_ID || '%'
有8019行。数据的数量对不上,估计是like引起的,比如 opp_number:123456 ,platfrom_id :12,123 个数 就会出现两行,怎么解决
minus
select * from tb1 t2 where ...-- 表示:在查询一中存在,而在查询二中不存在的数据集合!
-- 别想那么多:巧用 minus 关键字!
T1.OPPO_NUMBER_N LIKE T2.PLATFORM_ID || '%'引起的查询出来的结果集有重复记录,这个该怎么去重?