查詢最近日期的Record(Sql語句怎樣寫)？？？

ufo_1945

Table1
Y_Indate  LotNo      weight   Price
08/04/2003        05123XH3        107.14         370000
16/05/2003        05123XH3        250            330000
18/07/2003        05123XH3        178.57         370000
03/11/2003        05123XH3        71.43          380000
10/12/2003        05123XH3        214.29         350000
31/12/2003        05123XH3        178.57         340000
01/04/2003        05174XH3        55.12          11.23
04/04/2003        05175XH3        2469.15        14.22
08/04/2003        05175XH3        176.37         14.22
04/04/2003        05176XH3        500            11.23
處理後結果：
Satrt_Indate LotNo  Weight_Total  Price--(LastDatePrice)
08/04/2003        05123XH3        1000                340000
01/04/2003        05174XH3        55.12               11.23
04/04/2003        05175XH3        2645.52            14.22
04/04/2003        05176XH3        500                11.23
　　　各位幫幫忙呀！！！[em06]

[此贴子已经被作者于2004-2-19 11:59:51编辑过]

zhengjialon

已解决，参见：http://www.office-cn.net/bbs/dispbbs.asp?BoardID=2&replyid=42367&id=8372&star=2&skin=0

HG

order by y_input

ufo_1945

但你們好象還不清楚我所要的結果是這樣呀：
Satrt_Indate   LotNo   Weight_Total    Price--(LastDatePrice)
(Satrt_Indate)日期是同一LotNo的開始第一天；而(Weight_Total)重量之和是同一LotNo的重量之和l；Price--(LastDatePrice)－－指同一LotNo的最後一天的單價呢？麻煩你們再幫我想想？
　　　　　　　　　　謝謝！

HG

select (select top 1 Y_Indate from tbl_name where LotNo=@lotNo order by Y_indate) as Satrt_indate,
@LotNo as LotNo,
(select sum(weight) from tbl_name where LotNo=@LotNo) as Weight_Total,
(select top 1 Price from tbl_name where LotNo=@LotNo order by Y_indate desc) as last_Price
from tbl_name
where lotNo=@lotNO
---------------------------------------------------------------------------
如你想带到一批处理过的数据，VBA/的代码举列如下：
1,先生成一个放处理完后数据的表

create table ##tmp_table(y_indate as smalldatetime,Weight_Total as real,last_price as real)

2，向表中加载数据
dim rs as new adodb.recordset
dim strSql as string

strsql="select distinct LotNo from tbl_name"
rs.open strsql,currentproject.connection,0,1,adcmdtext
if not (rs.eof and rs.bof) then
   do until rs.eof
   rs.movenext
   strsql="insert into ##tmp_table select (select top 1 Y_Indate from tbl_name where LotNo=" & rs("LotNo") & " order by Y_indate) as Satrt_indate,'" & rs("LotNo") &"' as LotNo,(select sum(weight) from tbl_name where LotNo='" & rs("LotNo") &"') as Weight_Total,(select top 1 Price from tbl_name where LotNo=@LotNo order by Y_indate desc) as last_Price
from tbl_name where lotNo='"  & rs(lotno) &"'"
   loop
end if
rs.close
set rs=nothing
-----------------------
只是举例，并无测试，请自行测试修正[em08]

[此贴子已经被作者于2004-2-23 13:55:50编辑过]

ufo_1945

勁呀！

yodong

真是历害