出入库关联

create table 物资(id int, name varchar(10))
insert into 物资 values(1 , '物A')
create table 入库(id int , 物资id int, dt datetime)
insert into 入库 values(1 , 1 , getdate()-1)
insert into 入库 values(1 , 1 , getdate()-2)
create table 出库(id int , 物资id int, dt datetime)
insert into 出库 values(1 , 1 , getdate()-1)
insert into 出库 values(1 , 1 , getdate()-2)
insert into 出库 values(1 , 1 , getdate()-3)
insert into 出库 values(1 , 1 , getdate()-4)
go

select t1.name , t2.入库 , t2.出库 from 物资 t1,
(
select isnull(m.物资id , n.物资id) 物资id ,
       isnull('入'+cast(m.px as varchar), '') 入库,
       isnull('出'+cast(n.px as varchar), '') 出库
from
(select *, px = (select count(1) from 入库 where 物资id = t.物资id and dt < t.dt) + 1 from 入库 t) m
full join
(select *, px = (select count(1) from 出库 where 物资id = t.物资id and dt < t.dt) + 1 from 出库 t) n
on m.物资id = n.物资id and m.px = n.px
) t2
where t1.id = t2.物资id
order by case 入库 when '' then 2 else 1 end, 入库 , 
         case 出库 when '' then 2 else 1 end, 出库

drop table 物资,入库,出库
======================================================================================
/*
name       入库                               出库                              
---------- -------------------------------- --------------------------------
物A         入1                               出1
物A         入2                               出2
物A                                          出3
物A                                          出4

（所影响的行数为 4 行）
*/