Office中国论坛/Access中国论坛

标题: 查詢最近日期的Record(Sql語句怎樣寫)？？？ [打印本页]

作者: ufo_1945 时间: 2004-2-19 19:51
标题: 查詢最近日期的Record(Sql語句怎樣寫)？？？
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 时间: 2004-2-19 20:53
已解决，参见：http://www.office-cn.net/bbs/dispbbs.asp?BoardID=2&replyid=42367&id=8372&star=2&skin=0

作者: HG 时间: 2004-2-20 21:30
order by y_input

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

作者: HG 时间: 2004-2-23 21:27
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 时间: 2004-2-24 21:21
勁呀！

作者: yodong 时间: 2004-7-29 19:50
真是历害

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)