求个sql

[lijian260]

表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 不用存储过程用视图

[唐诗三百首]

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)
*/