dense_rank()解析函数

SELECT empno, deptno
dense_rank() OVER (PARTITION BY deptno
     ORDER BY sal NULLS LAST) SRLNO
FROM emp
WHERE deptno IN (10, 20)
group by empno, deptno  --,sal
ORDER BY deptno, SRLNO;

This Query didn't work because Sal should be in group by clause. Can anyone explain why it is so, and is there any alternative to get the rank in the same select without changing the group by clause? U want to order rank only based on salary.

这个查询不起作用，因为Sal应该在group by子句中。有人能解释一下为什么会这样吗?有没有其他的方法可以在不改变group by子句的情况下得到相同选择中的排名?1 .你要的是根据薪水高低来决定级别。

EDIT
Suppose there is another column name commision in emp table and i have to group by deptno and commision and partition by deptno only ,So what will be the suggested answer :

假设emp表中还有另一个列名commision，我需要根据deptno和commision进行分组，然后根据deptno进行划分，建议的答案是:

    SELECT empno, deptno,sum(sal) 
    dense_rank() OVER (PARTITION BY deptno
     ORDER BY sal NULLS LAST) SRLNO
    FROM emp
    WHERE deptno IN (10, 20)
    group by  deptno,commision  --,sal
    ORDER BY deptno, SRLNO;

now how can i group by depno and commision and only partition by deptno.

现在我怎么能用depno和commision来分组，只用deptno来划分。

HOW CAN I GROUP BY ON DIFFERENT COLUMN AND FIND RANK BASED ON PARTITION BY ON DIFFERNT COLUMN

如何通过不同的列来分组，并根据不同的列在分区上找到秩?

1 个解决方案

#1

Don't group by anything--your partition by does it for you!

不要随便分组——你的分区帮你做!

More specifically, since you're referencing sal in your partition by and order by, group by needs to be aware of it to group the results bu that. However, in this case, you don't need the group by because your dense_rank is using its own partitioning from the partition by clause, and will not follow whatever is in group by.

更具体地说，由于您在分区by和order by中引用了sal，所以group by需要知道它，以便对结果进行分组。但是，在这种情况下，您不需要group by，因为您的dense_rank正在使用它自己的分区，从partition by子句开始，并且不会跟随group by中的任何内容。

Regarding your edit, use your over clause there:

关于你的编辑，请使用上面的条款:

sum(sal) over (partition by deptno, commission) as salsum

超过(以部门划分的，佣金)的金额

#1