select t2.* from t2 where item_subno not in (select tm from t1) select t2.* from t2 where not exists (select 1 from t1 where t1.tm = t2.item_subno)
--表2列出表1沒有數據 select item_subno from jsptab except select tm from jsptab
服务器: 消息 156,级别 15,状态 1,行 2 在关键字 'except' 附近有语法错误。
谢谢大家.可以了 select t2.* from t2 where item_subno not in (select tm from t1) select t2.* from t2 where not exists (select 1 from t1 where t1.tm = t2.item_subno)
select * from t2 where not exists (select 1 from t1 where tm = t2.item_subno)---简化一点点 不要那么多前缀
也可以: select * from t2 where checksum(*) not in(select checksum(*) from t1)
select t2.* from t2 where not exists (select 1 from t1 where t1.tm = t2.item_subno)
--表2列出表1沒有數據
select item_subno from jsptab
except
select tm from jsptab
服务器: 消息 156,级别 15,状态 1,行 2
在关键字 'except' 附近有语法错误。
select t2.* from t2 where item_subno not in (select tm from t1)
select t2.* from t2 where not exists (select 1 from t1 where t1.tm = t2.item_subno)
select * from t2 where checksum(*) not in(select checksum(*) from t1)
列1 列2
tm lsjitem表2 列1 item_subno 列2 sale_price
select * from 表2 a where not exists(select 1 from 表1 where id=a.id)
--此处用id来对比,也可以用其他列来对比.
这样比较
select
*
from jsptab as a
full join
item as b on a.tm=b.item_subno and a.sale_price=b.sale_price
where nullif( a.tm,b.item_subno) is not null
都可用 full join+nullif
建议使用except,not in 的效率不高。