只用 WHERE USER_YXRGL.TBL_DLYDDJQYS.cbzlrq<=USER_YXRGL.TBL_DLHSJSP.fxrq 或 只用 WHERE USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh
USER_YXRGL.TBL_DLYDDJQYS.cbzlrq<=USER_YXRGL.TBL_DLHSJSP.fxrq and USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh 字段有没有建立索引,如果没有先建立,然后看下执行情况。
USER_YXRGL.TBL_DLYDDJQYS WHERE USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh order by USER_YXRGL.TBL_DLYDDJQYS.cbzlrq DESC) where rownum=1) as hyfl * ERROR 位于第 10 行: ORA-00904: invalid column name 出现这个错误,可我这两个表里都有yhh字段啊,什么原因
少了表名:USER_YXRGL.TBL_DLHSJSP改为: USER_YXRGL.TBL_DLYDDJQYS,USER_YXRGL.TBL_DLHSJSP WHERE USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh order by USER_YXRGL.TBL_DLYDDJQYS.cbzlrq DESC) where rownum=1) as hyfl
这么长的用户.表.字段,不是个好习惯,建议使用别名select a.colname from tablename a;
还不行,我没有权限建索引,不是少表名的问题,我在sqlserver也没建索引,好使
SELECT (SELECT * FROM ( SELECT USER_YXRGL.TBL_DLYDDJQYS.HYFL AS hyfl FROM USER_YXRGL.TBL_DLYDDJQYS WHERE USER_YXRGL.TBL_DLYDDJQYS.cbzlrq<=USER_YXRGL.TBL_DLHSJSP.fxrq and USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh order by USER_YXRGL.TBL_DLYDDJQYS.cbzlrq DESC ) where rownum=1 ) as hyfl FROM USER_YXRGL.TBL_DLHSJSP
用下面的试一试: SELECT (SELECT * FROM ( SELECT USER_YXRGL.TBL_DLYDDJQYS.HYFL AS hyfl FROM USER_YXRGL.TBL_DLYDDJQYS,USER_YXRGL.TBL_DLHSJSP WHERE USER_YXRGL.TBL_DLYDDJQYS.cbzlrq<=USER_YXRGL.TBL_DLHSJSP.fxrq and USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh order by USER_YXRGL.TBL_DLYDDJQYS.cbzlrq DESC ) where rownum=1 ) as hyfl FROM USER_YXRGL.TBL_DLHSJSP
WHERE USER_YXRGL.TBL_DLYDDJQYS.cbzlrq<=USER_YXRGL.TBL_DLHSJSP.fxrq
或
只用
WHERE USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh
字段有没有建立索引,如果没有先建立,然后看下执行情况。
*
ERROR 位于第 10 行:
ORA-00904: invalid column name
出现这个错误,可我这两个表里都有yhh字段啊,什么原因
USER_YXRGL.TBL_DLYDDJQYS,USER_YXRGL.TBL_DLHSJSP WHERE USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh order by USER_YXRGL.TBL_DLYDDJQYS.cbzlrq DESC) where rownum=1) as hyfl
(SELECT * FROM
(
SELECT
USER_YXRGL.TBL_DLYDDJQYS.HYFL AS hyfl
FROM
USER_YXRGL.TBL_DLYDDJQYS WHERE USER_YXRGL.TBL_DLYDDJQYS.cbzlrq<=USER_YXRGL.TBL_DLHSJSP.fxrq and USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh
order by USER_YXRGL.TBL_DLYDDJQYS.cbzlrq DESC
) where rownum=1
) as hyfl
FROM
USER_YXRGL.TBL_DLHSJSP
SELECT
(SELECT * FROM
(
SELECT
USER_YXRGL.TBL_DLYDDJQYS.HYFL AS hyfl
FROM
USER_YXRGL.TBL_DLYDDJQYS,USER_YXRGL.TBL_DLHSJSP WHERE USER_YXRGL.TBL_DLYDDJQYS.cbzlrq<=USER_YXRGL.TBL_DLHSJSP.fxrq and USER_YXRGL.TBL_DLYDDJQYS.yhh=USER_YXRGL.TBL_DLHSJSP.yhh
order by USER_YXRGL.TBL_DLYDDJQYS.cbzlrq DESC
) where rownum=1
) as hyfl
FROM
USER_YXRGL.TBL_DLHSJSP
分开写一下试试。
SQLServer和Oracle怎么能一样呢。