select flow_id,rw from (select t.flow_id ,rownum as rw from apex_030200.wwv_flow_list_templates t) where rw >= 5
Only use alias
2. Subquery reference can only be referenced in the query results. For example, the subquery does not find flow_id, the outer layer cannot be used, and the outer layer cannot refer to the inner layer t
3. The top three salary, the inner layer finds the salary. The order desc's virtual surface is used with rownum<3
It can be implemented that if the data exists, update does not exist, then insert
merge into product a
using (select 1717 product_id, '002' req_no from dual b)
on (a.product_id = and a.req_no = )
when matched then
update set product name = ''.....................
when not matched then
insert () values ()
with connect by You can query the data of a group of trees, pay attention to the condition of the last connect by (parent node = child node query upwards and vice queried downwards)
You can order, add two trees (or), or add where conditions
select * from emp
where......
start with empnc = 7369 or empnc = 7204 (note that it cannot be used and )
connect by prior mgr = empno
order by ...
6 Shares (query the percentage of total data that a certain data holds)
select ,,,
100*round(sal/sum(sal) over(),5)
from emp t
7 Continuous sum (the same name is divided into the same group and accumulated)
select ,,,
sum(sal) over(order by sal)
from emp t
8. Continuous summation with conditions (continuous summation by department)
select ,,,,
sum(sal) over(partition by order by sal)
from emp t
9. Sub-department sum (take out orderby)
select ,,,,
sum(sal) over(partition by )
from emp t
10 group query shares of salary (% of total number) bring to department group
select ,,,,
100*round(sal/sum(sal) over (partition by ),4)
from emp t
Note that the query here is ""grouping", so what is query here is to become a group into a 100%, and the proportion of salary occupied by employees in this part of a department
11 group query a single condition and classify it (query the salary level of a certain department). Note that the difference between rank() and row_number() is the difference between rank() and row_number(). rank is a jumping juxtaposition (1.1.3.3.5) row_number(1.2.3.4.5)
select t.*,ROW_NUMBER() over(partition by order by desc) rank from emp t
12 "Total". . . . . . This word is generally used by group by (different from over(partition by order by))
Query the total salary of the department by group
select sum(), from emp t group by
13 Group by + rollup
select sum(), from emp t group by rollup() and sum the sum
select sum(), from emp t group by rollup (,) Note that only the first parameter of multiple rollups is valid
14cube connects behind order by (replace ROLLUP) rollup upgrade version All groups
15Grouping implementation can be done without Java codeoracleThe null field found is assigned to the value 0 itself result 1 total result
select sum(),,
(case
when((grouping()=1 and grouping()=0)) then 'Department subtotal'
when((grouping()=1 and grouping()=1)) then 'Department Total'
else end) as
job from emp t group by rollup (,)
The fields accumulated after 16 groups (for example, according to employee names, grouped according to month, and the salary accumulation from January to December is achieved, that is, February is January + February.)
select , , , sum(sal) over (partition by order by desc) from emp t
17 Grouping Maximum Value Minimum Value Average Use max() over(partition by order by) instead of sum() You can also use min() avg()
18select * from v$transaction View transaction
19 Multi-layer GroupingfunctionConflict issues with subqueries
select , a.count_Sum, b.full_name, b.dept_code, b.area_code
from (select CREATOR, count() as count_Sum
from (select ,
,
instr(, ',', 1, ) + 1,
substr(,
instr(, ',', 1, ) + 1,
instr(, ',', 1, + 1) -
(instr(, ',', 1, ) + 1)) AS c
from (select ',' || checker || ',' AS ca,
checker,
LENGTH(checker),
length(checker || ','),
REPLACE(checker, ','),
length(REPLACE(checker, ',')),
nvl(length(REPLACE(checker, ',')), 0),
length(checker || ',') -
nvl(length(REPLACE(checker, ',')), 0) AS cnt
FROM wm_time_info a
where a.check_result != 1) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c
where <= ) temp
group by ) a,
base_user b
where = b.user_code
Outer query and inner grouping conflict
--select , a.count_Sum ,b.full_name,b.dept_code,b.area_code from (
select ,
count() as count_Sum,
FULLNAME,
DEPTCODE,
AREACODE
from (select CREATOR,
b.full_name FULLNAME,
b.dept_code DEPTCODE,
b.area_code AREACODE,
work_date
from (select work_date,
,
,
instr(, ',', 1, ) + 1,
substr(,
instr(, ',', 1, ) + 1,
instr(, ',', 1, + 1) -
(instr(, ',', 1, ) + 1)) AS c
from (
---
select work_date,
',' || checker || ',' AS ca,
checker,
LENGTH(checker),
length(checker || ','),
REPLACE(checker, ','),
length(REPLACE(checker, ',')),
nvl(length(REPLACE(checker, ',')), 0),
length(checker || ',') -
nvl(length(REPLACE(checker, ',')), 0) AS cnt
FROM wm_time_info a
where a.check_result != 1
---
) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c
where <= ) temp,
base_user b
where = b.user_code) o
--where work_date >='2016-01-10'
group by , FULLNAME, DEPTCODE, AREACODE
--) a ,base_user b where = b.user_code
20 Note: Neither the grouping nor the subquery in this select can be passed in as a function parameter.
5. Oracle group query and view
1. Grouping function: Average score: Sum: Maximum value: Minimum value Note: The first four must be targeted at numeric fields, and the parameters can only be one: Find a number 2. Group query 1. The syntax is group by group...
Introduction to solr4.5 group query and statistical functions
When it comes to group statistics, it is probably familiar to everyone, it is the group by statement of the database. However, when we use solr4.5 to search the full text, no matter how good the database provides SQL statements, it has no meaning. So how do we achieve group statistics in solr4.5? It...
oracle group query
Grouping function In the grouping function, if there is a lookup item grouped, the other items must also be grouped. For example, the following statement will report an error because the sal is grouped, but the name is not grouped: 1. Show the employees with the highest salary: 2. Show the average salary of all employees: 2.1 Use the system letter...
oracle group query
Commonly used functions: ·: Statistics: COUNT(), return the result based on the actual data volume in the table: ·:Sum:Sum(), is a statistics for numbers, summing. ·:Average value...
Error analysis of oracle group query instances ORA-00979 and ORA-00937
select ,,,, sum() from JZPZXX J &l ...
MysqlGroup query by time period to count the number of members
1. Use the case when method (not recommended) - The code is as follows Copy the code SELECT COUNT(DISTINCT user_id) user_count, CASE WHEN cre ...
[Django] How does Django support group query and statistics?
Code: from import Sum alarm_sum_group_items = models.FILE_PROTECT_ALARM. ...
Group query of oracle database
What this chapter is about group query in data. What is more complicated about group query is based on multiple table queries (we have shared with you the skills of using multi-table query in the previous lesson. You can access it yourself: Multi-table query 1 Multi-table query 2) and...
Rails tips: group query statistics and deduplication
Group query and count (:special_type).count select special_type,count(*) from special_groups gro...
Random recommendations
CodeForces Round #179 (295A) - Greg and ArrayUse one line tree twice
Line segment treeThe interval update and interval summation... Use such a line segment tree twice... Scan 1~k first... Use the line segment tree to count the number of times each operation is executed... Then each operation becomes op. l , , = times*...
Objc writes data to the plist file of iOS real machine
SQL SERVER2008 Methods for migrating remote databases to local
/wuzhanwen/article/details/77449229 SQL SERVER 2008 for Winform program or website background is placed on a remote server, using Mi...
Getting started with sqlmap injection
Getting started with sqlmap injection usage of sqlmap:linuxCell: sqlmap [Options] Windows: python sqlmap [Options] Common parameters and meanings: Target -d DIRECT ...
【Reprint】C# recursively delete folder directories and files
During the C# file operation, sometimes it is necessary to delete the corresponding directory. If the folder contains other folders or files, it also needs to be deleted together. At this time, you may need to use recursion to delete the folder directory and files. During the recursion process, if the object traversed is a folder, delete the folder...
CentOS7 yum installation PHP 5.6.24
Configure yum source and add epel and remi sources of CentOS 6.5. # rpm -Uvh /pub/linux/fedora/epel/6/x86_64/epel- ...
Java Basics_0302: Classes and Objects
Define class class Book { // Define a new classStringtitle; // The name of the book double price; // The price of the book /** * The complete information of the output object */ public voi ...
mybatis mappers configuration description load map file
Mappers Since the behavior of MyBatis has been configured by the above elements, we now need to define SQL mapping statements. But first we need to tell MyBatis where to find these statements. Java is automatically looking for this...
Detailed explanation of MySQL and Mariadb binary log binlog
Mariadb/mysql provides 4 different logs, namely error log(), general log, slow log and binary log. Error log records the system startup...
Add user controls in C# WinForm
Turn:/haelang/article/details/40681003 Sometimes we need to frequently use some combination of system default tools, so we can use custom user controls. Create a ...