求条语句

[kaikai_kk]

T1(sno int,a varchar(20))
-------------------------
1 A-100
2 A-2-4
3 B
4 C
5 D
6 A

T2(ino int,b varchar(20))
-------------------------
1 A-04
2 A-02
3 A-03-3
4 A-05
5 B-1
6 B-f
7 B-B-3
8 B-0
9 C-6
10 C
11 C-3

说明:
T1的sno唯一,T2 ino,b唯一
以T1的a左边第一个减号(-)部分与T2按b升序排列后，取第一笔,再取b左边第一个减号(-)部分相连接
截断a,按sno升序,返回结果如下

sno a ino b
-----------
1 A 2 A-02
2 A 2 A-02
3 B 8 B-0
4 C 10 C
6 A 2 A-02

[-晴天]

create table T1(sno int,a varchar(20))
insert into t1 select 1,'A-100'
insert into t1 select 2,'A-2-4'
insert into t1 select 3,'B'
insert into t1 select 4,'C'
insert into t1 select 5,'D'
insert into t1 select 6,'A'
create table T2(ino int,b varchar(20))
insert into t2 select 1,'A-04'
insert into t2 select 2,'A-02'
insert into t2 select 3,'A-03-3'
insert into t2 select 4,'A-05'
insert into t2 select 5,'B-1'
insert into t2 select 6,'B-f'
insert into t2 select 7,'B-B-3'
insert into t2 select 8,'B-0'
insert into t2 select 9,'C-6'
insert into t2 select 10,'C'
insert into t2 select 11,'C-3'
go
;with cte as(
select a.sno,a.a,b.ino,b.b from(
select sno,case when charindex('-',a)>0 then left(a,charindex('-',a)-1) else a end a from t1
)a inner join t2 b on charindex(a.a,b.b)=1
)select * from cte a where not exists(select 1 from cte where sno=a.sno and b<a.b)
go
drop table t1,t2
/*
sno         a                    ino         b
----------- -------------------- ----------- --------------------
1           A                    2           A-02
2           A                    2           A-02
3           B                    8           B-0
4           C                    10          C
6           A                    2           A-02

(5 行受影响)

*/

[cd731107]

select sno,left(t1.a,1),ino,b 
from t1,
(
select *
from T2 
where not exists
(select 1 from T2 as tb where left(T2.b,1)=left(tb.b,1) and tb.b>T2.b)
) t3
where left(t1.a,1)=left(t3.b,1)

[kaikai_kk]

select X.*,t2.* From
(select sno,(Case When charindex('-',a)>0 Then Left(a,charindex('-',a)-1) Else a End) a from t1) X
left join t2 
on charindex(X.a,t2.b)=1
where t2.b=(Select top 1 b From t2 where charindex(x.a,b)=1 order by b)