--测试环境
declare @ta table(id1 int)
insert into @ta select 1
union all select 2
union all select 3declare @tb table(id2 int,name varchar(10))
insert into @tb select 1,'aa'
union all select 2,'bb'
union all select 2,'bb2'
union all select 3,'cc'
union all select 4,'dd'--查询
select
A.id1,B.name
from @ta A left join @tb B
on A.id1=B.id2 and name not like '%[0-9]%'
--结果
id1 name
----------- ----------
1 aa
2 bb
3 cc(所影响的行数为 3 行)
declare @ta table(id1 int)
insert into @ta select 1
union all select 2
union all select 3declare @tb table(id2 int,name varchar(10))
insert into @tb select 1,'aa'
union all select 2,'bb'
union all select 2,'bb2'
union all select 3,'cc'
union all select 4,'dd'--查询
select
A.id1,B.name
from @ta A left join @tb B
on A.id1=B.id2 and name not like '%[0-9]%'
--结果
id1 name
----------- ----------
1 aa
2 bb
3 cc(所影响的行数为 3 行)
上面的两表我是举个例子的.
实际上不能从NAME字段来判断的.有其它办法吗?
select a.id,min(a.name) from tb a
inner join ta a on a.id=b.id--相同ID取最大的Name
select a.id,max(a.name) from tb a
inner join ta a on a.id=b.id--如相同ID取特别的值,如楼上所有,用自定义函数
select a.id,min(a.name) from tb a
inner join ta a on a.id=b.id
group by a.id--相同ID取最大的Name
select a.id,max(a.name) from tb a
inner join ta a on a.id=b.id
group by a.id--如相同ID取特别的值,如楼上所有,用自定义函数
1 aa
2 bb
2 bb2
3 cc