UPDATE T1 SET PRE_PRICE=
(select price from (SELECT PRICE FROM T2 WHERE T1.PRODUCTCODE=T2.PRODUCTCODE AND
ORDER BY T2.PRICEDATE DESC) where rownum <2);
由于order by在 rownum之后执行,所以不能放在一起
(select price from (SELECT PRICE FROM T2 WHERE T1.PRODUCTCODE=T2.PRODUCTCODE AND
ORDER BY T2.PRICEDATE DESC) where rownum <2);
由于order by在 rownum之后执行,所以不能放在一起
(select price from (SELECT PRICE FROM T2 WHERE T1.PRODUCTCODE=T2.PRODUCTCODE ORDER BY T2.PRICEDATE DESC) where rownum <2);
update T1 set PRE_PRICE=(select tt.PRE_PRICE from(select PRE_PRICE,RODUCTCODE from T2 where rownum<2 order by T2.PRE_PRICE desc) tt where tt.PRE_PRICE=T2.PRE_PRICE)
CREATE TABLE T1 (
PRODUCTCODE VARCHAR2 (1),
PRICEDATE DATE,
PRICE NUMBER,
PRE_PRICE NUMBER ) ; CREATE TABLE T2 (
PRODUCTCODE VARCHAR2 (1),
PRICEDATE DATE,
PRICE NUMBER ) ;
按日期排序取得最近的一条?
如果是这样的话得到的结果是错误的.
(SELECT PRICE FROM T2 y WHERE y.PRODUCTCODE=x.PRODUCTCODE and y.PRICEDATE=(select max(PRICEDATE )from T2 z where z.PRODUCTCODE=y.PRODUCTCODE));
SQL> UPDATE T1 x SET PRE_PRICE=
2 (SELECT PRICE FROM T2 y WHERE y.PRODUCTCODE=x.PRODUCTCODE and y.PRICEDATE=(
select max(PRICEDATE )from T2 z where z.PRODUCTCODE=y.PRODUCTCODE));已更新 1 行。SQL> select * from t1;P PRICEDATE PRICE PRE_PRICE
- ---------- ---------- ----------
1 9SQL>
但这个速度比较慢,我要从200多万条记录中检索。
由于历史原因PRICEDATE没有精确到秒,导致数据中会存在同一天对同一个产品的多次报价。
(select price from (SELECT PRICE,PRODUCTCODE FROM T2 ORDER BY T2.PRICEDATE DESC) x where T1.PRODUCTCODE=x.PRODUCTCODE and rownum <2);
SQL> UPDATE T1 SET PRE_PRICE=
2 (select price from (SELECT PRICE,PRODUCTCODE FROM T2 ORDER BY T2.PRICEDAT
E DESC) x where T1.PRODUCTCODE=x.PRODUCTCODE and rownum <2);已更新 1 行。已用时间: 00: 00: 00.00
SQL> select * from t1;P PRICEDATE PRICE PRE_PRICE
- ---------- ---------- ----------
1 9已用时间: 00: 00: 00.00
SQL>
(SELECT PRICE FROM T2 y WHERE to_date(y.pricedate)=to_date(sysdate) and y.PRODUCTCODE=x.PRODUCTCODE and y.PRICEDATE=(select max(PRICEDATE )from T2 z where z.PRODUCTCODE=y.PRODUCTCODE) and to_date(z.pricedate)=to_date(sysdate));