代码之家 › 专栏 › 技术社区 › mon

Oracle-有序列ID(如ORDER BY 1)在哪里被允许?

group-by oracle sql

mon · 技术社区 · 3 年前

问题

有序列ID 1,2。。。允许吗?我能从文件的哪一部分看出来?

SELECT *
FROM (
    SELECT Months * Salary, COUNT(*)
    FROM Employee
    GROUP BY (Months * Salary) 
    ORDER BY 1 DESC     <---- This is OK
    )
WHERE ROWNUM = 1;

----------
108064 7

SELECT *
FROM (
    SELECT Months * Salary, COUNT(*)
    FROM Employee
    GROUP BY 1        <--- ORA-00979: not a GROUP BY expression
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

----------
SELECT Months * Salary, COUNT(*)
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression

group_by_clause::=

order_by_clause::=

3 回复 | 直到 3 年前

the_slk 3 年前

在图像中只能按顺序排列,才能找到“位置”选项。

GROUP BY 
{
    column-Name [ , column-Name ]*  
|
    ROLLUP ( column-Name [ , column-Name ]* )
}
column-Name must be a column from the current scope of the query;
there can be no columns from a query block outside the current scope.
For example, if a GROUP BY clause is in a subquery,
it cannot refer to columns in the outer query.

ORDER BY { column-Name | ColumnPosition | Expression }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
    [ , column-Name | ColumnPosition | Expression 
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
    ] * 

ColumnPosition
An integer that identifies the number of the column in the SelectItems
in the underlying query of the SELECT statement.
ColumnPosition must be greater than 0 and not greater
than the number of columns in the result table. In other words,
if you want to order by a column, that column must be specified
in the SELECT list.

MT0 3 年前

在 GROUP BY 条款 1
在 ORDER BY 条款 1 指的是 SELECT 条款。

SELECT *
FROM (
    SELECT COUNT(*)
    FROM Employee
    GROUP BY 1              -- A number literal
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

同:

SELECT *
FROM (
    SELECT COUNT(*)
    FROM Employee
    GROUP BY NULL           -- A NULL literal
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

或

SELECT *
FROM (
    SELECT COUNT(*)
    FROM Employee
    GROUP BY 'ABC'          -- A string literal
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

然而,

SELECT *
FROM (
    SELECT Months * Salary, COUNT(*)
    FROM Employee
    GROUP BY 1
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

1 是要按其分组的文本数字值 Months 和 Salary 是列中的列名 分组依据 但不聚合。

mon 3 年前

位置整数列ID在ORDER BY中可用,但在GROUP BY中不可用。

按符号的位置顺序

根据表达式的值指定行的排序位置 位置值必须是整数 .

数据库首先根据第一行的值对行进行排序表情。对于第一个表达式具有相同值的行,则根据第二个表达式的值进行排序,依此类推。这个以降序排列在所有其他的前面。参见“排序查询” Results”,用于讨论查询结果的排序。

按符号的位置顺序,发出以下语句按部门id升序,薪资降序,最后

SELECT last_name, department_id, salary 
   FROM employees 
   ORDER BY 2 ASC, 3 DESC, 1;