Cno      Cpno
1    5
2    NULL
3    1
4    6
5    7
6    NULL
7    6
8    6

create table tb(cno int,    Cpno int)
insert into tb values(1  ,  5)
insert into tb values(2  ,  NULL)
insert into tb values(3  ,  1)
insert into tb values(4  ,  6)
insert into tb values(5  ,  7)
insert into tb values(6  ,  NULL)
insert into tb values(7  ,  6)
insert into tb values(8  ,  6)
go

select t1.cno , cpno = (select cpno from tb t2 where t2.cno = t1.cpno) from tb t1
/*
cno         cpno        
----------- ----------- 
1           7
2           NULL
3           5
4           NULL
5           6
6           NULL
7           NULL
8           NULL

（所影响的行数为 8 行）
*/

select * from 
(
  select t1.cno , cpno = (select cpno from tb t2 where t2.cno = t1.cpno) from tb t1
) t
where cpno is not null
/*
cno         cpno        
----------- ----------- 
1           7
3           5
5           6

（所影响的行数为 3 行）
*/


drop table tb