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)goselect 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) mfull join(select *, px = (select count(1) from 出库 where 物资id = t.物资id and dt < t.dt) + 1 from 出库 t) non m.物资id = n.物资id and m.px = n.px) t2where t1.id = t2.物资idorder 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 行） */
