各位,我有一条SQL语句看不明白,请帮我详细解释一下,最好说明一下其中几个函数的用法:
一张表:EMPLO SALARY
----- ----------
1 2000
1 3000
1 4000
1 4000
2 2000
2 3000
2 4000
2 5000输入以下SQL语句:select employerid,
max(decode(top3,1,salary,null)) salary1,
max(decode(top3,2,salary,null)) salary2,
max(decode(top3,3,salary,null)) salary3
from (select employerid,salary,
row_number()
over (partition by employerid
order by salary desc nulls last) top3
from aa)
where top3<=3
group by employerid
/得到结果:EMPLO SALARY1 SALARY2 SALARY3
----- ---------- ---------- ----------
1 4000 4000 3000
2 5000 4000 3000
一张表:EMPLO SALARY
----- ----------
1 2000
1 3000
1 4000
1 4000
2 2000
2 3000
2 4000
2 5000输入以下SQL语句:select employerid,
max(decode(top3,1,salary,null)) salary1,
max(decode(top3,2,salary,null)) salary2,
max(decode(top3,3,salary,null)) salary3
from (select employerid,salary,
row_number()
over (partition by employerid
order by salary desc nulls last) top3
from aa)
where top3<=3
group by employerid
/得到结果:EMPLO SALARY1 SALARY2 SALARY3
----- ---------- ---------- ----------
1 4000 4000 3000
2 5000 4000 3000
from (select employerid,salary,
row_number()
over (partition by employerid
order by salary desc nulls last) top3
from aa)
where top3<=3
相当于SQLServer的top-n语句(ORACLE没有Top)加入group就是分组取前三条,再加入decode就相当于SQLServer的case when效果,三行转为三列
order by salary desc nulls last) top3是什么意思?
[email protected]
row_number() over (partition by employerid
order by salary desc nulls last) top3效果应该是
Employid salary top3
1 4000 1
1 4000 2
1 3000 3
1 2000 4
2 5000 1
2 4000 2
2 3000 3
2 2000 4where top3<3语句 就把
1 2000 4
2 2000 4
两条记录过滤掉了剩下的用decode语句翻转
1 4000 0
1 4000 1
1 3000 2
1 2000 3
2 5000 0
2 4000 1
2 3000 2
2 2000 3我这里没环境不能测试
我给你解释
decode(字段A,字段B,不符合反回值)符合字段A返回字段B:这是ORCAL的聚集函数。
MAX()是取所有返回的最大值这是应该是这样,上面的没有错。。
GROUP BY 和 WHERE 子句
可以在包含 GROUP BY 子句的查询中使用 WHERE 子句。在进行任何分组之前,将消除不符合 WHERE 子句条件的行。例如:USE pubs
SELECT type, AVG(price)
FROM titles
WHERE advance > $5000
GROUP BY type下面是结果集:type
------------ --------------------------
business 2.99
mod_cook 2.99
popular_comp 21.48
psychology 14.30
trad_cook 17.97 (5 row(s) affected)只有预付款超过 $5,000 的行才包含在查询结果所显示的组中。
学习
====================================================
MAX
返回表达式的最大值。语法
MAX ( [ ALL | DISTINCT ] expression )
====================================================
row_number的具体作用,可能要看他的具体函数实现才知道
我给你解释
decode(字段A,字段B,不符合反回值)符合字段A返回字段B:这是ORCAL的聚集函数。
------------------------------------------希望能结合这个实例,分析一下,各位帮帮忙!
partition by 是分类分组,你可以这样理解磁盘上有100个文件,
你按照employerid建立了25个文件夹,并把对应的文件放进去(这个过程就是partition by)
然后再在文件夹内order by,并用row_number()标记
这样row_number()可以重复了,你的例子中就是1、2、3、4、1、2、3、4...这样循环如果不加partition by 你得到的row_number()将是1、2、3、4、5、6、7、8... 这样的序列
max(decode(top3,1,salary,null)) salary1,decode(top3,top3的值,salaryr的值,不符合反回值)
就是说,当表中top3的值=1时返回salary,不等返回null)从表中遍历
max(decode(top3,1,salary,null)) salary1,
max(decode(top3,2,salary,null)) salary2,
max(decode(top3,3,salary,null)) salary3翻译到SQLServer就是
select
max(Case When Top3=1 then Salary else 0) [salary1],
max(Case When Top3=2 then Salary else 0) [salary2],
max(Case When Top3=3 then Salary else 0) [salary3]
from...
group by xxxx加入max是为了构造一个聚合形式,否则group by 语句无法通过,把你的max换成min也是一样的
当然是row_number的别名了
where rank3<=3
)的写法:其中的 rank()over(partition by employerid order by salary desc) as rank3 from xxx是什么意思啊?
By Boris Milrud, Oracle Pro
The task of retrieving the top or bottom N rows from a database table (by salary, sales amount, credit, etc.) is often referred to as a "top-N query." This task is fairly common in application development.
The most straightforward, but inefficient, way of accomplishing such a query is by retrieving all rows from the database table(s), sorting them by specified criteria, scanning from the top, and then selecting the top N rows. This is definitely not an elegant solution. Writing a procedure that retrieves and sorts all rows from a database table requires lots of code, and it probably won't execute as quickly as you'd expect. Use a single SQL statement to perform a top-N query. You can do so either by using the ROWNUM pseudocolumn available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK() and DENSE_RANK().
RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.
DENSE_RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. 我认为效果是一样的