Oracle Expression Lists

最新推荐文章于 2023-01-31 17:46:53 发布

原创最新推荐文章于 2023-01-31 17:46:53 发布 · 627 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #each #list #email #sql

数据库专栏收录该内容

23 篇文章

订阅专栏

表达式列表是其他表达式的组合，常用于SQL查询和子查询的条件和成员资格判断。每个表达式集由括号包围，且每个集的表达式数量必须相同，匹配条件中的表达式数。列表最多包含1000个表达式，而表达式集的数量不受限制，但每个集最多1000个。有效的表达式列表示例包括在不同SQL子句中的应用，如GROUP BY。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

An expression list is a combination of other expressions.

Expression lists can appear in comparison and membership conditions and in GROUP BY clauses of queries and subqueries.

Comparison and membership conditions appear in the conditions of WHERE clauses. They can contain either one or more comma-delimited expressions, or one or more sets of expressions where each set contains one or more comma-delimited expressions. In the latter case (multiple sets of expressions):

Each set is bounded by parentheses
Each set must contain the same number of expressions
The number of expressions in each set must match the number of expressions before the operator in the comparison condition or before the IN keyword in the membership condition.

A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions.

The following are some valid expression lists in conditions:

(10, 20, 40) 
('SCOTT', 'BLAKE', 'TAYLOR')
( ('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA') )

In the third example, the number of expressions in each set must equal the number of expressions in the first part of the condition. For example:

SELECT * FROM employees 
  WHERE (first_name, last_name, email) IN 
  (('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA'))

In a simple GROUP BY clause, you can use either the upper or lower form of expression list:

SELECT department_id, MIN(salary), MAX(salary)
   FROM employees
   GROUP BY department_id, salary;

SELECT department_id, MIN(salary), MAX(salary)
   FROM employees
   GROUP BY (department_id, salary);

In ROLLUP, CUBE, and GROUPING SETS clauses of GROUP BY clauses, you can combine individual expressions with sets of expressions in the same expression list. The following example shows several valid grouping sets expression lists in one SQL statement:

SELECT 
prod_category, prod_subcategory, country_id, cust_city, count(*)
   FROM  products, sales, customers
   WHERE sales.prod_id = products.prod_id 
   AND sales.cust_id=customers.cust_id 
   AND sales.time_id = '01-oct-00'
   AND customers.cust_year_of_birth BETWEEN 1960 and 1970
GROUP BY GROUPING SETS 
  (
   (prod_category, prod_subcategory, country_id, cust_city),
   (prod_category, prod_subcategory, country_id),
   (prod_category, prod_subcategory),
    country_id
  );