求一经典语句，判断是否单级！请指点
我这个写法有问题，请予以指正　：）

select     distinct   A.kmbh,A.kmmc,case   when   len(A.kmbh)=4   and  
charindex(A.KMBH,B.KMBH)> 0   and   len(B.KMBH)> 4   THEN   1   ELSE   0   END
from  
(SELECT   KMBH,KMMC   FROM   KMB_2005   WHERE   LEN(KMBH)=4)   A,  
(SELECT   KMBH,KMMC   FROM   KMB_2005   WHERE   LEN(KMBH)> 4)   B    
order   by   a.kmbh,kmmc

------解决方案--------------------
select distinct A.kmbh,A.kmmc,case when len(A.kmbh)=4 and
charindex(A.KMBH,B.KMBH)> 0 and len(B.KMBH)> 4 THEN 1 ELSE 0 END
from
(SELECT KMBH,KMMC FROM KMB_2005 WHERE LEN(KMBH)=4) A,
(SELECT KMBH,KMMC FROM KMB_2005 WHERE LEN(KMBH)> 4) B
order by a.kmbh,kmmc

------------
在SQL中是區分大小寫的，還有就是A,B表之間沒有關聯條件
------解决方案--------------------
从原语句改的话

select A.kmbh,A.kmmc,max(case when B.KMBH is not null THEN 1 ELSE 0 END)
from
(SELECT KMBH,KMMC FROM KMB_2005 WHERE LEN(KMBH)=4) A left join
(SELECT KMBH,KMMC FROM KMB_2005 WHERE LEN(KMBH)> 4) B
on charindex(A.KMBH,B.KMBH)> 0
group by A.kmbh,A.kmmc
order by a.kmbh,a.kmmc

------解决方案--------------------
use tempdb
create table KMB_2005
(
nId int identity(1,1) ,
kmbh nvarchar(100),
kmmc nvarchar(100),
primary key(nId)
)

go

insert KMB_2005(kmbh,kmmc) values( 'aa ', '1 ');
insert KMB_2005 values( 'aaa ', '2 ');
insert KMB_2005 values( 'aaaa ', '3 ');
insert KMB_2005 values( 'aaaaa ', '4 ');
insert KMB_2005 values( 'aaaaaa ', '5 ');
insert KMB_2005 values( 'bbbbbb ', '6 ');
insert KMB_2005 values( 'bbbbbb ', '7 ');

go

select distinct A.kmbh, A.kmmc
from KMB_2005 a
where len(A.kmbh)=4 and exists
(select 1 from KMB_2005 b where len(b.kmbh)> 4 and charIndex(A.KMBH,B.KMBH)> 0)
order by a.kmbh,a.kmmc

drop table KMB_2005

---------------------------------
kmbh kmmc
---------- ----------
aaaa 3

(1 row(s) affected)