时间戳

首页 > 解决方案 > ORDER BY with DISTINCT 给出 ORA-01791: not a SELECTed expression

sql - ORDER BY with DISTINCT 给出 ORA-01791: not a SELECTed expression

问题描述

我试图通过 select 语句使用 oracle order，但它会导致异常：

ORA-01791: 不是 SELECTed 表达式。

 select distinct usermenu.menuname
 from   usermenu, userpermission
 where  userpermission.menuno = usermenu.menuno 
 and    userpermission.userno = 1
 order by userpermission.menuno;

标签： sqloracle

解决方案

当语句的列列表DISTINCT中有聚合函数时，不属于列列表的列将不起作用。SELECTORDER BYSELECT

这是一个基于 Scott 模式的示例。

这工作正常，虽然D.LOC没有被选中：

SQL> select d.dname, e.ename
  2  from dept d join emp e on e.deptno = d.deptno
  3  order by d.loc;

DNAME          ENAME
-------------- ----------
SALES          BLAKE
SALES          TURNER
SALES          ALLEN
SALES          MARTIN
SALES          WARD
SALES          JAMES
RESEARCH       SCOTT
RESEARCH       JONES
RESEARCH       SMITH
RESEARCH       ADAMS
RESEARCH       FORD
ACCOUNTING     KING
ACCOUNTING     MILLER
ACCOUNTING     CLARK

14 rows selected.

现在，添加DISTINCT- 基本上，这就是你所拥有的：

SQL> select distinct d.dname, e.ename
  2  from dept d join emp e on e.deptno = d.deptno
  3  order by d.loc;
order by d.loc
         *
ERROR at line 3:
ORA-01791: not a SELECTed expression

聚合函数也是如此，例如COUNT：

SQL> select d.dname, e.ename, count(*)
  2  from dept d join emp e on e.deptno = d.deptno
  3  group by d.dname, e.ename
  4  order by d.loc;
order by d.loc
         *
ERROR at line 4:
ORA-00979: not a GROUP BY expression


SQL>

那么该怎么办？用别的东西订购。或者，将当前查询用作内联视图，将其与包含您要对结果排序的列的表连接起来，它将起作用：

SQL> select x.dname, x.ename
  2  from (select distinct d.dname, e.ename
  3        from dept d join emp e on e.deptno = d.deptno
  4       ) x
  5       join dept d1 on d1.dname = x.dname
  6  order by d1.loc;

DNAME          ENAME
-------------- ----------
SALES          TURNER
SALES          JAMES
SALES          BLAKE
SALES          WARD
SALES          MARTIN
SALES          ALLEN
RESEARCH       SMITH
RESEARCH       FORD
RESEARCH       ADAMS
RESEARCH       SCOTT
RESEARCH       JONES
ACCOUNTING     MILLER
ACCOUNTING     KING
ACCOUNTING     CLARK

14 rows selected.

SQL>

推荐阅读