a:表字段 id name
1 李
2 王
3 张b:表字段 p_id file id
a1 aaaa 1
b1 bbbb 1
c1 cccc 2
d1 dddd 2
e1 eeee 3希望输出的结果是:
id name file p_id
1 李 aaaa a1
2 王 cccc c1
3 张 dddd e1对b表中重复的内容,随机选择。希望能用嵌套inner join
1 李
2 王
3 张b:表字段 p_id file id
a1 aaaa 1
b1 bbbb 1
c1 cccc 2
d1 dddd 2
e1 eeee 3希望输出的结果是:
id name file p_id
1 李 aaaa a1
2 王 cccc c1
3 张 dddd e1对b表中重复的内容,随机选择。希望能用嵌套inner join
from a innert join b as bb on a.id = bb.id
where not exists(select 1 from b where id = bb.id and P_id > bb.Pid)
WHERE A.ID=B.ID AND
T.[file]=(SELECT MIN([file]) FROM B WHERE ID=T.ID )
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1]([id] int,[name] varchar(2))
insert [tb1]
select 1,'李' union all
select 2,'王' union all
select 3,'张'select * from [tb1]--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
create table [tb2]([p_id] varchar(2),[file] varchar(4),[id] int)
insert [tb2]
select 'a1','aaaa',1 union all
select 'b1','bbbb',1 union all
select 'c1','cccc',2 union all
select 'd1','dddd',2 union all
select 'e1','eeee',3select * from [tb2]
select A.id,A.name,B.[file],B.p_id
from tb1 A
inner join tb2 B on A.id = B.id and B.[file] = (select min(tb2.[file]) as N from Tb2 where id = B.id)--结果
/*id name file p_id
1 李 aaaa a1
2 王 cccc c1
3 张 eeee e1*/
消息 8117,级别 16,状态 1,第 2 行
操作数数据类型 uniqueidentifier 对于 min 运算符无效