阅读原文:http://c.raqsoft.com.cn/article/1535964014661?r=alice
Oracle 支持一些独特的语法和函数,在移植到 MySQL 上时或多或少给程序员造成了困扰,下面我们针对 Oracle 的一些特殊用法举例并讲解如何用集算器来完成同样功能。这些方法当然也不限于针对 MySQL,对于所有其它数据库也能支持。
1、 递归语句
a) select employee_id,first_name,last_name,manager_id
from hr.employees
start with employee_id=102
connect by prior employee_id = manager_id
A | |
1 | =connect("orcl") |
2 | =A1.query@x("select employee_id, first_name, last_name, manager_id from hr.employees") |
3 | =A2.keys(EMPLOYEE_ID) |
4 | =A2.select@1(EMPLOYEE_ID==102) |
5 | =A2.switch(MANAGER_ID, A2) |
6 | =A2.nodes(MANAGER_ID, A4) |
7 | =(A4|A6).new(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID.EMPLOYEE_ID:MANAGER_ID) |
(1) A3 设置序表 A2 的键
(2) A4 选取起始雇员
(3) A5 将 A2 中 MANAGER_ID 值转换成记录,以便递归
(4) A6 获取起始雇员的所有子节点
b) select employee_id, first_name,last_name,manager_id
from hr.employees
start with employee_id=104
connect by prior manager_id = employee_id
A | |
1 | =connect("orcl") |
2 | =A1.query@x("select employee_id, first_name, last_name, manager_id from hr.employees") |
3 | =A2.keys(EMPLOYEE_ID) |
4 | =A2.switch(MANAGER_ID, A2) |
5 | =A2.select@1(EMPLOYEE_ID==104) |
6 | =A5.prior(MANAGER_ID) |
7 | =A6.new(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID.EMPLOYEE_ID:MANAGER_ID) |
聚集分析函数和嵌套聚集函数,多重分组使用方式可阅读原文:http://c.raqsoft.com.cn/article/1535964014661?r=alice