有四张表:MaterialStore Material Unit Supplier
现在需要将MaterialStore里主要字段有:原料编号m_ID 新纪录发生日期ms_Date 好品数ms_GoodNum 次品数ms_BadNum
通过m_ID与Material关联 Material里再与Unit Supplier关联
现在我想查询得出MaterialStroe里各原材料最新的库存量,及相关信息
如
MaterialStore表里有数据:
m_ID ms_Date ms_GoodNum ms_BadNum
1 2009-8-2 20 1
1 2009-8-3 30 2
1 2009-8-7 40 0
2 2009-8-4 20 1
2 2009-8-9 30 1查询结果
m_ID ms_Date ms_GoodNum ms_BadNum m_Name un_Name su_Name
1 2009-8-7 40 0 铁 公斤 XX公司
2 2009-8-9 30 1 铝 斤 YY公司
现在需要将MaterialStore里主要字段有:原料编号m_ID 新纪录发生日期ms_Date 好品数ms_GoodNum 次品数ms_BadNum
通过m_ID与Material关联 Material里再与Unit Supplier关联
现在我想查询得出MaterialStroe里各原材料最新的库存量,及相关信息
如
MaterialStore表里有数据:
m_ID ms_Date ms_GoodNum ms_BadNum
1 2009-8-2 20 1
1 2009-8-3 30 2
1 2009-8-7 40 0
2 2009-8-4 20 1
2 2009-8-9 30 1查询结果
m_ID ms_Date ms_GoodNum ms_BadNum m_Name un_Name su_Name
1 2009-8-7 40 0 铁 公斤 XX公司
2 2009-8-9 30 1 铝 斤 YY公司
(
select * from MaterialStore M where not exists(select * from MaterialStore where M.m_ID=m_ID and M.ms_Date<ms_Date)
)
select h.* from hgo join Material M on h.m_ID=M.m_ID join Unit U on M.ID=U.ID join
Supplier S on U.m_ID=S.m_ID
不知道你的这个数据那里来的
(
select * from MaterialStore M where not exists(select * from MaterialStore where M.m_ID=m_ID and M.ms_Date<ms_Date)
)
select h.* from hgo h join Material M on h.m_ID=M.m_ID join Unit U on M.ID=U.ID join
Supplier S on U.m_ID=S.m_ID
insert into @Table values( 1,'2009-8-5',40 ,3)
insert into @Table values( 2,'2009-8-3',30 ,2)
insert into @Table values( 2,'2009-8-7',50 ,1)select * from @Table M where not exists(select * from @Table where M.m_ID=m_ID and M.ms_Date<ms_Date)
declare @T table (m_ID int,m_Name char(20),un_Name char(20),su_Name char(20))
insert into @Table values( 1,'2009-8-2',20 ,1)
insert into @Table values( 1,'2009-8-5',40 ,3)
insert into @Table values( 2,'2009-8-3',30 ,2)
insert into @Table values( 2,'2009-8-7',50 ,1)insert into @T values(1,'铁','公斤','XX公司')
insert into @T values(2,'铝','斤','YY公司')
;with dt as
(
select * from @Table M where not exists(select * from @Table where M.m_ID=m_ID and M.ms_Date<ms_Date)
)
select * from dt h join @T M on h.m_ID=M.m_ID
from MaterialStore p
inner join Material q on p.m_ID = q.m_ID
inner join Unit x on x.ma_id = q.ma_id
inner join Supplier y on y.ma_id = q.ma_id
where datediff(day,convert(datetime,p.ms_Date,120),convert(datetime,getDate(),120)) <= 3 (最近3天的记录,其中:ma_id为Material的主键)