表1 id itemname
1 i1
2 i2
3 i3 表2 id itemName iname
i1 a
i2 a
i2 b
得到:
i1 a
i2 a
i3 null
i1 null
i2 b
i3 null 不用存储过程用视图
1 i1
2 i2
3 i3 表2 id itemName iname
i1 a
i2 a
i2 b
得到:
i1 a
i2 a
i3 null
i1 null
i2 b
i3 null 不用存储过程用视图
create table 表1
(id int,itemname varchar(10))insert into 表1
select 1,'i1' union all
select 2,'i2' union all
select 3,'i3'create table 表2
(id int,itemName varchar(10),iname varchar(10))insert into 表2
select 1,'i1','a' union all
select 2,'i2','a' union all
select 3,'i2','b'
select x.itemname,y.iname
from
(select a.itemname,
row_number() over(partition by a.itemname order by getdate()) 'rn'
from
(select itemname from 表1 union all
select itemname from 表1) a) x
left join
(select itemName,iname,row_number() over(partition by itemName order by getdate()) 'rn'
from 表2) y on x.itemname=y.itemname and x.rn=y.rn
/*
itemname iname
---------- ----------
i1 a
i1 NULL
i2 a
i2 b
i3 NULL
i3 NULL(6 row(s) affected)
*/