22,207
社区成员
发帖
与我相关
我的任务
分享
create table [A]([id] varchar(2),[name] varchar(4),[typeID] varchar(1))
insert [A]
select 'A1','材料','a' union all
select 'A2','项目','b'
if object_id('[B]') is not null drop table [B]
go
create table [B]([key] varchar(2),[typeid] varchar(1),[typeName] varchar(2))
insert [B]
select 'T1','a','1a' union all
select 'T2','a','2a' union all
select 'T3','b','1b' union all
select 'T4','b','2b' union all
select 'T5','b','3b' union all
select 'T6','c','1c'
select * from b
select a.*,(select top 1(typename) from b where b.typeid = a.typeid order by typeid asc) from a
---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] varchar(2),[name] varchar(4),[typeID] varchar(1))
insert [A]
select 'A1','材料','a' union all
select 'A2','项目','b'
if object_id('[B]') is not null drop table [B]
go
create table [B]([key] varchar(2),[typeid] varchar(1),[typeName] varchar(2))
insert [B]
select 'T1','a','1a' union all
select 'T2','a','2a' union all
select 'T3','b','1b' union all
select 'T4','b','2b' union all
select 'T5','b','3b' union all
select 'T6','c','1c'
---查询---
select a.*,b.typeName
from a
left join b on a.typeid=b.typeid
and not exists(select 1 from b t where t.typeid=b.typeid and t.[key]<b.[key])
---结果---
id name typeID typeName
---- ---- ------ --------
A1 材料 a 1a
A2 项目 b 1b
(2 行受影响)
select a.*,b.typeName
from a
left join b on a.typeid=b.typeid
and not exists(select 1 from b t where t.typeid=b.typeid and t.[key]>b.[key])