create table tb (a int,b int) insert into tb select 1,2 insert into tb select 2,2 insert into tb select 3,3 insert into tb select 4,2 insert into tb select 5,1 select distinct a from tb where a not in (select b from tb) /* 4 5 */
select count(pn1) from pninfo select count(pn2) from pninfo查询结果是: 38398 19366但是select distinct pn1 from pninfo where pn1 not in (select distinct pn2 from pninfo)一条也差不出来,这是为什么啊???
你意思是pn2的记录都包含在pn1吧?
select replace(pn1,pn2,'') as from t2
select * from pninfo where pn2is null or pn2=''
select * from (select a,count(b) con from t)a full join (select b,count(a) con from t)b on a.a=b.b and a.con=b.con where nullif(a.a,b.b) is not null
select distinct pn1 from pninfo where pn1 not in (select pn2 from pninfo where pn2 is not null)
当然为空 你两个都用同一个tb 表select distinct a from 数据多的tb where a not in (select b from 数据少的表)
create table tb (a int,b int)
insert into tb select 1,2
insert into tb select 2,2
insert into tb select 3,3
insert into tb select 4,2
insert into tb select 5,1
select distinct a from tb where a not in (select b from tb)
/*
4
5
*/
select count(pn2) from pninfo查询结果是:
38398
19366但是select distinct pn1 from pninfo where pn1 not in (select distinct pn2 from pninfo)一条也差不出来,这是为什么啊???
你意思是pn2的记录都包含在pn1吧?
select replace(pn1,pn2,'') as from t2
select * from pninfo where pn2is null or pn2=''
*
from
(select a,count(b) con from t)a
full join
(select b,count(a) con from t)b on a.a=b.b and a.con=b.con
where
nullif(a.a,b.b) is not null
当然为空 你两个都用同一个tb 表select distinct a from 数据多的tb where a not in (select b from 数据少的表)