34,576
社区成员
发帖
与我相关
我的任务
分享
select *
from Table_A a
inner join Table_B b on b.TID=a.TID
and b.ID=(select min(x.ID) from Table_B x where x.TID=b.TID and x.city1=b.city1)
where b.city1='天津'
---测试数据
;WITH A(TID,route)AS(
select 1,'天津线' union all
select 2,'北京线' union all
select 3,'唐山线'
),B(ID,TID, city1,city2)AS(
select 1,1,'天津','太原' union all
select 2,1,'太原','石家庄' union all
select 3,1,'石家庄','秦皇岛' union all
select 4,2,'北京','合肥' union all
select 5,2,'合肥','上海' union all
select 6,3,'唐山','天津' union all
select 7,3,'天津','石家庄' union all
select 8,1,'天津','乌鲁木齐'
)
---测试数据结束,读取语句如下:
SELECT a.* ,
b1.*
FROM A a
LEFT JOIN B b1 ON b1.TID = a.TID
INNER JOIN ( SELECT ( SELECT TOP 1
b2.ID
FROM B b2
WHERE b3.TID = b2.TID
AND b3.city1 = b2.city1
) AS ID
FROM B b3
WHERE b3.city1 = '天津'
GROUP BY b3.TID ,
b3.city1
) t ON t.ID = b1.ID;