求一经典查询，请赐教！

Question

--楼主没说结果怎么来的。只能猜测了。取后边3位中不是数值的。
select bh,mc
from tablename
where isnumeric(right(bh,3))=0

Answer 1

select * from 表 t
where not exists(select 1 
                   from 表 
                     where left(MC,2)=left(t.MC,2)
                           and len(MC)<len(t.MC))

Answer 2

declare @tb table
(
  BH varchar(20),
  MC varchar(10)
)
insert @tb
select '1001AA00000000000002','00101-AAA' union
select '1001AA00000000000001','001-AA' union
select '1012AA000000000008M9','00-A' union
select '1001AA00000000003003','01000-BBB' union
select '1001AA00000000000003','010-BB' union
select '1031AA000000000007JG','01-B' --查询
select * from @tb t
where not exists(select 1 
                   from @tb 
                     where left(MC,2)=left(t.MC,2)
                           and len(MC)<len(t.MC))
--结果
/*BH                   MC         
-------------------- ---------- 
1012AA000000000008M9 00-A
1031AA000000000007JG 01-B（所影响的行数为 2 行）
*/

Answer 3

--生成测试数据
create table #T(BH varchar(20),MC varchar(20)) 
insert into #T select '1001AA00000000000002','00101-AAA'
insert into #T select '1001AA00000000000001','001-AA'
insert into #T select '1012AA000000000008M9','00-A'
insert into #T select '1001AA00000000003003','01000-BBB'
insert into #T select '1001AA00000000000003','010-BB'
insert into #T select '1031AA000000000007JG','01-B'
--执行查询
select
    a.*
from 
    #T a
where
    not exists(select 
                   1 
               from
                   #T 
               where 
                   BH != a.BH  
                   and 
                   left(a.MC,charindex('-',a.MC)-1) like left(MC,charindex('-',MC)-1)+'%')
--输出结果
/*
BH                      MC
--------------------    -----
1012AA000000000008M9    00-A
1031AA000000000007JG    01-B
*/

Answer 4

--没有规则,俺投机取巧下:
--借 vivianfdlpw() 数据
declare @tb table
(
  BH varchar(20),
  MC varchar(10)
)
insert @tb
select '1001AA00000000000002','00101-AAA' union
select '1001AA00000000000001','001-AA' union
select '1012AA000000000008M9','00-A' union
select '1001AA00000000003003','01000-BBB' union
select '1001AA00000000000003','010-BB' union
select '1031AA000000000007JG','01-B' 
--查询  
select * from @tb
where len(MC)=4
--结果
BH                   MC         
-------------------- ---------- 
1012AA000000000008M9 00-A
1031AA000000000007JG 01-B（所影响的行数为 2 行）