求一经典语句，判断是否单级！请指点
我这个写法有问题，请予以指正 ：）
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)
