1.LISTAGG
SELECT deptno,listagg(ename, ', ')WITHIN GROUP( ORDER by ename) as employees
From Scott.emp GROUPby Deptno;
Summary of Deptno fields, after which multiple ename are separated by ', '
2.NVL/NVL2
--selectemployee_id,last_name,salary*12* (1+NVL (commission_pct,0)) from Employees
If the commission_pct exists, it is itself, otherwise the assignment is 0;
--select employee_id,last_name,commission_pct,
NVL2 (commission_pct,commission_pct+0.15,0.01) from Employees
If commission_pct exists, the return value is commission_pct+0.15, otherwise 0.01
parameter value2 value3 can be any data type other than a long type
3. Case...when...then...when...then...else...end
Select department_id,
Case department_id
When Ten then salary*1.1
When then salary*1.2
else salary*1.3 end as "new_sal"//Assignment Alias
From employees where department_id in (10,20,30)
4.decode
Selectemployee_id,decode (department_id,10,salary*1.1,20,salary*1.2,salary) new_sal
From employees where Department_idin (10,20,30)
According to DEPARTMENT_ID, if 10, then its salary*1.1, if 20, then its salary*1.2, other salary unchanged, and assign aliases--new_sal
5.to_char
Select To_char (sysdate, ' yyyy ' year "MM" month "DD" Day "HH:MI:SS") from dual
Select To_char (1234567.89, ' 999,999,999.99 ') from dual
6.to_number
Select To_number (' 1,234,567.89 ', ' 999,999,999.99 ') +100from dual
Notice the range of two digits before and after, the latter must be larger than the former
7.| | concatenation character
Select Last_Name | | ' Earns ' | | To_char (Salary, ' $99999 ') | | ' Monthlym,but wants to earn ' | |
To_char (3*salary, ' $99999 ') "Dream salary" from employees
The console will output a word
8.group by...having
Select Department_id,min (Salary) from employees
GROUP BY department_id
Having min (salary) >
(select min (Salary) from employees where department_id = ' 50 ')
9. Tree structure start with connect by prior
Reference: https://www.cnblogs.com/sunfie/p/5129716.html
Select ... from tablename
Start with condition 1
Connect by Prior Condition 2
Where Condition 3;
Example: SELECT * from EMP
Startwith empno=7369
CONNECT by PRIOR Empno=mgr--pid after the front ID (query up)
To put it simply, a tree structure is stored in a table, such as the presence of two fields in a list: org_id,parent_id, then by representing the parent of each record, a tree structure can be formed, and a query with the syntax above can be used to obtain all the records of this tree:
Condition 1 is the limit statement of the root node, of course, we can relax the qualification to obtain multiple root nodes, in fact, many trees.
Condition 2 is the join condition, in which the prior represents the previous record , such as connect by priororg_id = parent_id; the org_id of the last record is this note The parent_id, the father of this record, is the previous record.
Condition 3 is a filter condition that filters all returned records.
10. Combination function Row_number over (Partition by)
The same values are grouped together into a block, that is, grouping, and then sorting numbers within the group
11. Summary function Rollup function
Select decode (job) + grouping (DEPTNO), 1, ' Subtotal ', 2, ' total ', job) job,
Decode (Grouping (DEPTNO), 1,count (*) | | Bar ', Deptno) deptno
From EMP
GROUP BY Rollup (Job, Deptno);
12. Sort after grouping (similar to partition by)
Rank/dense_rank over
Rank () over (Partition by Item_idorder by fee Desc)