Oracle中不允许表的列名称使用Oracle声明的关键字! (ORA-01747: user.table.column, table.column 或列说明无效)

1.问题

ORA-01747: user.table.column, table.column 或列说明无效

-- Oracle下,根据商品价格从高到低取4-6名商品
SELECT *
FROM (
		SELECT ROWNUM, t1.*
		FROM (
			SELECT prod_name, prod_price 
			FROM PRODUCTS
			ORDER BY PROD_PRICE DESC
		) t1
		WHERE ROWNUM <= 6
	) t2
WHERE t2.ROWNUM > 3;

2.解决

这里只要给ROWNUM起一个别名即可,如下面的代码所示:

-- Oracle下,根据商品价格从高到低取4-6名商品
SELECT *
FROM (
		SELECT ROWNUM AS rm, t1.*
		FROM (
			SELECT prod_name, prod_price 
			FROM PRODUCTS
			ORDER BY PROD_PRICE DESC
		) t1
		WHERE ROWNUM <= 6
	) t2
WHERE t2.rm > 3;