求一条SQL查询语句

[Dream_Hunter_]

有四张表：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公司

[ws_hgo]

;with hgo as
(
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

[llsen]

楼主 怎么了

[ws_hgo]

应该是慢
多点击了几下
就这样子啦

[十八道胡同]

学习ws_hgoSQL

[十八道胡同]

declare @Table table( m_ID int , ms_Date datetime , ms_GoodNum int , ms_BadNum int)

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)

select  * from @Table M where not exists(select * from @Table where M.m_ID=m_ID and M.ms_Date<ms_Date)

[十八道胡同]

try...

declare @Table table( m_ID int , ms_Date datetime , ms_GoodNum int , ms_BadNum int)
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

[jianchencool]

select p.m_ID, p.ms_Date, p.ms_GoodNum, p.ms_BadNum, q.m_Name, x.un_Name ,y.su_Name
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的主键)

[zq32206124]

来接分

[屌丝女士111]

需要提这么多麽？

[昕颖]

[Quote=引用 6 楼 lcl_data 的回复:]
try...
SQL codedeclare@Tabletable( m_IDint , ms_Datedatetime , ms_GoodNumint , ms_BadNumint)declare@Ttable (m_IDint,m_Namechar(20),un_Namechar(20),su_Namechar(20))insertinto@Tablevalues(1,'2009-8-2',20 ,1)insertinto@Tablevalues(1,'2009-8-5',40 ,3)insertinto@Tablevalues(2,'2009-8-3',30 ,2)insertinto@Tablevalues(2,'2009-8-7',50 ,1)insertinto@Tvalues(1,'铁','公斤','XX公司')insertinto@Tvalues(2,'铝','斤','YY公司')
;with dtas
(select*from@Table Mwherenotexists(select*from@Tablewhere M.m_ID=m_IDand M.ms_Date<ms_Date)
)select*from dt hjoin@T Mon h.m_ID=M.m_ID
[/Quote]


UP，应当可以实现。/

[Dream_Hunter_]

昨天每次发完贴，点提交回复都提示错误。网站打开也超慢。
以为没发上，今天才发现发了这么多条。
算散分了。