我要把某物料的入库出库盘点记录都显示出来怎么做？

[linhaobin1982]

入库明细(id,物料编号，数量，时间）
出库明细（id，物料编号，数量，时间）
盘点（id，物料编号，数量，时间）
现在查询物料编号001－00001的所有出入库盘点单，要求得到这样的显示列表：
单号id（如果是入库单就在id前加'入库'两个字，出库就加出库。。。），数量（如果是出库就是正数，如果是出库就是负数，盘点就修正仓库中这种物料的数量），时间（结果按时间先后排序）
另外要统计这种物料当前
怎样得到这样的显示结果？

[whw502]

[code=SQL][
if object_id('Receipt')is not null drop table Receipt
if object_id('delivery')is not null drop table delivery
if object_id('stocktake')is not null drop table stocktake

create table Receipt
(
id int,
itemcode varchar(100),
qty int,
date varchar(8)

)
insert into Receipt
select 1,'001',100,'20080601'
union all
select 2,'002',100,'20080601'

create table delivery
(
id int,
itemcode varchar(100),
qty int,
date varchar(8)

)
insert into delivery
select 1,'001',100,'20080602'

create table stocktake
(
id int,
itemcode varchar(100),
qty int,
date varchar(8)

)insert into stocktake
select 2,'002',80,'20080602'

select * from
(
select '入库'+itemcode as itemdes,itemcode ,qty,date
from Receipt
union all
select '出库'+itemcode as itemdes,itemcode,-qty,date
from delivery
union all
(
select '盘点'+T1.itemcode as itemdes,T0.itemcode,sum(T1.qty)-sum(T0.qty),T1.date from
(
select itemcode,qty,date from Receipt
union all
select itemcode,-qty,date from delivery
) T0
right join stocktake T1 on T0.itemcode = T1.itemcode and T0.date<=T1.date
group by T0.itemcode,T1.itemcode,T1.date)
) T2




/code]