公司不让使用not exists,因为效率低,请问是否有其它办法能高效的替代以下这条语句?select * from (select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45 ) as A
where not exists ( select 1 from SQT_Department_Commerce as D where A.ID = D.CMMD_ID )
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45 ) as A
where not exists ( select 1 from SQT_Department_Commerce as D where A.ID = D.CMMD_ID )
select *
from (
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION S
join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45
) A
join SQT_Department_Commerce D on A.ID <> D.CMMD_ID
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45 ) as A
where int ( select 1 from SQT_Department_Commerce as D where A.ID <> D.CMMD_ID )
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45 ) as A
where in ( select 1 from SQT_Department_Commerce as D where A.ID <> D.CMMD_ID )
不等于 和 not in 都是不可以的
据说是临时表
可我又想一条语句搞定
晕
大家想想办法帮忙
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID
left join (select distinct CMMD_ID SQT_Department_Commerce )as D on T.ID = D.CMMD_ID
where S.BUYER_ID = 45 and D.CMMD_ID is null
不要说用not in.那更慢
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID
Left join SQT_Department_Commerce as D ON A.ID = D.CMMD_ID
Where S.BUYER_ID = 45 AND D.CMMD_ID is NUll
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION as S inner join SQT_TradeCommodity as T
on S.CMMD_ID = T.ID where S.BUYER_ID = 45
) as A
left join SQT_Department_Commerce as D
on A.id = D.CMMD_ID
where D.CMMD_ID is null
但not exists是不能用join代替的,无论是怎么join。
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45
left join SQT_Department_Commerce as D on S.CMMD_ID=D.CMMD_ID
where D.CMMD_ID is null
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,
T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID
LEFT JOIN SQT_Department_Commerce as D ON A.ID = D.CMMD_ID
WHERE S.BUYER_ID = 45 AND D.CMMD_ID IS NULL----否则加DISTINCT
select DISTINCT T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,
T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID
LEFT JOIN SQT_Department_Commerce as D ON A.ID = D.CMMD_ID
WHERE S.BUYER_ID = 45 AND D.CMMD_ID IS NULL
from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID
left join (select distinct CMMD_ID SQT_Department_Commerce )as D on T.ID = D.CMMD_ID
where S.BUYER_ID = 45 and D.CMMD_ID is null
-----------------------------------------
exists在一对一的情况下可以用join代替,
但not exists是不能用join代替的,无论是怎么join。
------------------------------------------------------
这里以distinct 了
同志们,我都说用join是不对的。大家想想一对多的情况,用not exists,如果子查询中有一条匹配,一条不匹配,那么外层这条记录就不会出现在结果集中,如果用left join,右边一条匹配,一条不匹配(NULL),那么左边的这条记录就会出现在结果集中,这根not exists的结果不一致啊。
-----------------------------------------------------------------------------------
请注意:匹配的条件只是CMMD_ID.
select A.*, d.cmm_id from
(select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45 )
as A left join SQT_Department_Commerce d on A.ID = D.CMMD_ID) ) t where cmmm_id is null
FROM (SELECT A.*, d .cmm_id
FROM (SELECT T .ID, T .COMMERCE_NAME, T .BRAND, T .MANU_NAME, T .SPEC,
T .MODEL, T .DEFAULT_MEASURE, T .PRICE, T .SALER_NAME,
T .STATE, T .HASFITTINGS
FROM SQT_CMMD_SEND_RELATION AS S INNER JOIN
SQT_TradeCommodity AS T ON S.CMMD_ID = T .ID
WHERE S.BUYER_ID = 45) AS A LEFT JOIN
SQT_Department_Commerce d ON A.ID = D .CMMD_ID)) t
WHERE cmmm_id IS NULL
三层嵌套不知道和not exists比会怎么样。。