How to re-query oracle

Oracle provides multiple deduplication query methods: The DISTINCT keyword returns a unique value for each column. The GROUP BY clause groups the results and returns a non-repetitive value for each group. The UNIQUE keyword is used to create an index containing only unique rows, and querying the index will automatically deduplicate. The ROW_NUMBER() function assigns unique numbers and filters out results that contain only line 1. The MIN() or MAX() function returns non-repetitive values ​​of a numeric column. The INTERSECT operator returns the common values ​​of the two result sets (no duplicates).

Oracle Deduplication Query

Oracle provides several ways to perform deduplication queries, i.e., remove duplicate values ​​from the result set.

1. DISTINCT keywords

The DISTINCT keyword is the easiest way to deduplicate. It is used to return unique values ​​for each column in the result set.

 <code class="sql">SELECT DISTINCT column_name FROM table_name;</code>

2. GROUP BY clause

The GROUP BY clause groups the result set and returns only non-repetitive values ​​for each group.

 <code class="sql">SELECT column_name FROM table_name GROUP BY column_name;</code>

3. UNIQUE keywords

The UNIQUE keyword can be used to create an index that contains only unique rows of the table. Querying such indexes will automatically deduplicate.

 <code class="sql">CREATE UNIQUE INDEX index_name ON table_name (column_name);</code>

4. ROW_NUMBER() function

The ROW_NUMBER() function assigns a unique number to each row in the result set. It can then be used to filter out deduplicate results that contain only line 1.

 <code class="sql">SELECT column_name FROM ( SELECT column_name, ROW_NUMBER() OVER (ORDER BY column_name) AS row_num FROM table_name ) AS subquery WHERE row_num = 1;</code>

5. MIN() or MAX() function

For numeric columns, you can use the MIN() or MAX() functions to return non-repetitive values.

 <code class="sql">SELECT MIN(column_name) FROM table_name;</code>

 <code class="sql">SELECT MAX(column_name) FROM table_name;</code>

6. INTERSECT operator

The INTERSECT operator intersects two result sets and returns only their common values ​​(i.e. no duplicate values).

 <code class="sql">SELECT column_name FROM table_name INTERSECT SELECT column_name FROM table_name2;</code>

The above is the detailed content of How to re-query oracle. For more information, please follow other related articles on the PHP Chinese website!