SnowConvert：Redshift SELECT INTO 语句¶
SELECT INTO¶
描述¶
从表、视图和用户定义的函数中返回行，并将它们插入到新表中。（Redshift SQL 语言参考“SELECT 语句” (https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_synopsis.html)）
语法¶
[ WITH with_subquery [, ...] ]
SELECT
[ TOP number ] [ ALL | DISTINCT ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM table_reference [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | { EXCEPT | MINUS } } [ ALL ] query ]
[ ORDER BY expression
[ ASC | DESC ]
[ LIMIT { number | ALL } ]
[ OFFSET start ]
有关更多信息，请参阅以下每个链接：
FROM 子句¶
描述¶
查询中的 FROM 子句列出了从中选择数据的表引用（表、视图和子查询）。如果列出了多个表引用，则必须在 FROM 子句或 WHERE 子句中使用相应语法联接这些表。如果未指定联接条件，则系统会将查询作为交叉联接进行处理。（Redshift SQL 语言参考“FROM 子句”） (https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html)）
警告
Snowflake 部分支持 FROM 子句。目前不支持 Object 取消透视 (https://docs.aws.amazon.com/redshift/latest/dg/query-super.html#unpivoting)。
语法¶
FROM table_reference [, ...]
<table_reference> ::=
with_subquery_table_name [ table_alias ]
table_name [ * ] [ table_alias ]
( subquery ) [ table_alias ]
table_reference [ NATURAL ] join_type table_reference
[ ON join_condition | USING ( join_column [, ...] ) ]
table_reference PIVOT (
aggregate(expr) [ [ AS ] aggregate_alias ]
FOR column_name IN ( expression [ AS ] in_alias [, ...] )
) [ table_alias ]
table_reference UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ] (
value_column_name
FOR name_column_name IN ( column_reference [ [ AS ]
in_alias ] [, ...] )
) [ table_alias ]
UNPIVOT expression AS value_alias [ AT attribute_alias ]
示例源模式¶
输入代码：¶
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
CREATE TABLE department (
id INT,
name VARCHAR(50),
manager_id INT
);
INSERT INTO department(id, name, manager_id) VALUES
(1, 'HR', 100),
(2, 'Sales', 101),
(3, 'Engineering', 102),
(4, 'Marketing', 103);
SELECT e.name AS employee_name, d.name AS department_name
INTO employees_in_department
FROM employee e
INNER JOIN department d ON e.manager_id = d.manager_id;
EMPLOYEE_NAME |
DEPARTMENT_NAME |
|---|---|
John |
HR |
Jorge |
Sales |
Kwaku |
Sales |
Liu |
Sales |
Mateo |
Engineering |
Nikki |
Marketing |
Paulo |
Marketing |
Richard |
Marketing |
Sofía |
Engineering |
输出代码：¶
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
CREATE TABLE department (
id INT,
name VARCHAR(50),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO department (id, name, manager_id) VALUES
(1, 'HR', 100),
(2, 'Sales', 101),
(3, 'Engineering', 102),
(4, 'Marketing', 103);
CREATE TABLE IF NOT EXISTS employees_in_department AS
SELECT e.name AS employee_name, d.name AS department_name
FROM
employee e
INNER JOIN
department d ON e.manager_id = d.manager_id;
| EMPLOYEE_NAME | DEPARTMENT_NAME |
|---|---|
| John | HR |
| Jorge | Sales |
| Kwaku | Sales |
| Liu | Sales |
| Mateo | Engineering |
| Nikki | Marketing |
| Paulo | Marketing |
| Richard | Marketing |
| Sofía | Engineering |
已知问题¶
没有已知问题。
GROUP BY 子句¶
描述¶
GROUP BY 子句标识了查询的分组列。当查询使用标准函数（如 SUM、AVG 和 COUNT）计算聚合时，必须声明分组列。（Redshift SQL 语言参考“GROUP BY 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_GROUP_BY_clause.html)）
Snowflake 完全支持 GROUP BY 子句。
Grammar Syntax
GROUP BY expression [, ...]
Sample Source Patterns
Input Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
SELECT
manager_id,
COUNT(id) AS total_employees
INTO manager_employees
FROM employee
GROUP BY manager_id
ORDER BY manager_id;
MANAGER_ID |
TOTAL_EMPLOYEES |
|---|---|
100 |
1 |
101 |
3 |
102 |
2 |
103 |
3 |
104 |
3 |
1 |
Output Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
CREATE TABLE IF NOT EXISTS manager_employees AS
SELECT
manager_id,
COUNT(id) AS total_employees
FROM
employee
GROUP BY manager_id
ORDER BY manager_id;
MANAGER_ID |
TOTAL_EMPLOYEES |
|---|---|
100 |
1 |
101 |
3 |
102 |
2 |
103 |
3 |
104 |
3 |
1 |
Known Issues
没有已知问题。
Related EWIs.
无相关的 EWIs。
HAVING 子句
Description
HAVING 子句可将条件应用于查询返回的中间分组结果集。（Redshift SQL 语言参考“HAVING 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html)）
Snowflake 完全支持 HAVING 子句。
语法¶
[ HAVING condition ]
示例源模式¶
输入代码：¶
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
SELECT manager_id, COUNT(id) AS total_employees
INTO manager_employees
FROM
employee
GROUP BY manager_id
HAVING COUNT(id) > 2
ORDER BY manager_id;
MANAGER_ID |
TOTAL_EMPLOYEES |
|---|---|
101 |
3 |
103 |
3 |
104 |
3 |
输出代码：¶
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
CREATE TABLE IF NOT EXISTS manager_employees AS
SELECT manager_id, COUNT(id) AS total_employees
FROM
employee
GROUP BY manager_id
HAVING COUNT(id) > 2
ORDER BY manager_id;
MANAGER_ID |
TOTAL_EMPLOYEES |
|---|---|
101 |
3 |
103 |
3 |
104 |
3 |
已知问题¶
没有已知问题。
相关的 EWIs。¶
无相关的 EWIs。
LIMIT 和 OFFSET 子句¶
描述¶
LIMIT 和 OFFSET 子句可检索并跳过数字中指定的行数。
Snowflake 完全支持 LIMIT 和 OFFSET 子句。
Grammar Syntax
[ LIMIT { number | ALL } ]
[ OFFSET start ]
Sample Source Patterns
LIMIT 数
Input Code:
SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT 5;
ID |
NAME |
MANAGER_ID |
SALARY |
|---|---|---|---|
100 |
Carlos |
120000.00 |
|
101 |
John |
100 |
90000.00 |
102 |
Jorge |
101 |
95000.00 |
103 |
Kwaku |
101 |
105000.00 |
104 |
Paulo |
102 |
110000.00 |
Output Code:
CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT 5;
ID |
NAME |
MANAGER_ID |
SALARY |
|---|---|---|---|
100 |
Carlos |
120000.00 |
|
101 |
John |
100 |
90000.00 |
102 |
Jorge |
101 |
95000.00 |
103 |
Kwaku |
101 |
105000.00 |
104 |
Paulo |
102 |
110000.00 |
LIMIT ALL
Input Code:
SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT ALL;
ID |
NAME |
MANAGER_ID |
SALARY |
|---|---|---|---|
100 |
Carlos |
120000.00 |
|
101 |
John |
100 |
90000.00 |
102 |
Jorge |
101 |
95000.00 |
103 |
Kwaku |
101 |
105000.00 |
104 |
Paulo |
102 |
110000.00 |
105 |
Richard |
102 |
85000.00 |
106 |
Mateo |
103 |
95000.00 |
107 |
Liu |
103 |
108000.00 |
108 |
Zhang |
104 |
95000.00 |
Output Code:
CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL;
ID |
NAME |
MANAGER_ID |
SALARY |
|---|---|---|---|
100 |
Carlos |
120000.00 |
|
101 |
John |
100 |
90000.00 |
102 |
Jorge |
101 |
95000.00 |
103 |
Kwaku |
101 |
105000.00 |
104 |
Paulo |
102 |
110000.00 |
105 |
Richard |
102 |
85000.00 |
106 |
Mateo |
103 |
95000.00 |
107 |
Liu |
103 |
108000.00 |
108 |
Zhang |
104 |
95000.00 |
OFFSET（无 LIMIT）
Snowflake 不支持 OFFSET（无 LIMIT）。LIMIT 在转换后会添加 NULL（这是默认 LIMIT）。
Input Code:
SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
OFFSET 5;
ID |
NAME |
MANAGER_ID |
SALARY |
|---|---|---|---|
105 |
Richard |
102 |
85000.00 |
106 |
Mateo |
103 |
95000.00 |
107 |
Liu |
103 |
108000.00 |
108 |
Zhang |
104 |
95000.00 |
Output Code:
CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL
OFFSET 5;
ID |
NAME |
MANAGER_ID |
SALARY |
|---|---|---|---|
105 |
Richard |
102 |
85000.00 |
106 |
Mateo |
103 |
95000.00 |
107 |
Liu |
103 |
108000.00 |
108 |
Zhang |
104 |
95000.00 |
Known Issues
没有已知问题。
Related EWIs.
无相关的 EWIs。
局部变量和参数
Description
在存储过程中执行语句时，Redshift 还允许 SELECT INTO 变量。
Snowflake 完全支持此模式。
语法¶
SELECT [ select_expressions ] INTO target [ select_expressions ] FROM ...;
示例源模式¶
SELECT INTO（表达式在左侧）¶
输入代码：¶
CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
var1 int;
BEGIN
select 10, 100 into param1, var1;
END;
$$ LANGUAGE plpgsql;
param1 |
|---|
10 |
输出代码：¶
CREATE OR REPLACE PROCEDURE test_sp1 (param1 int)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "03/03/2025", "domain": "test" }}'
AS $$
DECLARE
var1 int;
BEGIN
select 10, 100 into
: param1,
: var1;
RETURN OBJECT_CONSTRUCT('param1', :param1);
END;
$$;
TEST_SP1 |
|---|
{ "param1":10 } |
SELECT INTO（表达式在右侧）¶
输入代码：¶
CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
var1 int;
BEGIN
select into param1, var1 10, 100;
END;
$$ LANGUAGE plpgsql;
param1 |
|---|
10 |
输出代码：¶
由于 Snowflake 不支持 SELECT INTO 的此语法，因此将表达式移至 INTO 的左侧。
CREATE OR REPLACE PROCEDURE test_sp1 (param1 int)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "03/03/2025", "domain": "test" }}'
AS $$
DECLARE
var1 int;
BEGIN
select
10, 100
into
: param1,
: var1;
RETURN OBJECT_CONSTRUCT('param1', :param1);
END;
$$;
TEST_SP1 |
|---|
{ "param1":10 } |
已知问题¶
没有已知问题。
相关的 EWIs。¶
无相关的 EWIs。
ORDER BY 子句¶
描述¶
ORDER BY 子句对查询的结果集进行排序。（Redshift SQL 语言参考“Order By 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_ORDER_BY_clause.html)）
Snowflake 完全支持 ORDER BY 子句。
Grammar Syntax
[ ORDER BY expression [ ASC | DESC ] ]
[ NULLS FIRST | NULLS LAST ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
Sample Source Patterns
Input Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT,
salary DECIMAL(10, 2)
);
INSERT INTO employee (id, name, manager_id, salary) VALUES
(100, 'Carlos', NULL, 120000.00),
(101, 'John', 100, 90000.00),
(102, 'Jorge', 101, 95000.00),
(103, 'Kwaku', 101, 105000.00),
(104, 'Paulo', 102, 110000.00),
(105, 'Richard', 102, 85000.00),
(106, 'Mateo', 103, 95000.00),
(107, 'Liu', 103, 108000.00),
(108, 'Zhang', 104, 95000.00);
SELECT id, name, manager_id, salary
INTO salaries
FROM employee
ORDER BY salary DESC NULLS LAST, name ASC NULLS FIRST
LIMIT 5
OFFSET 2;
ID |
NAME |
MANAGER_ID |
SALARY |
|---|---|---|---|
107 |
Liu |
103 |
108000.00 |
103 |
Kwaku |
101 |
105000.00 |
102 |
Jorge |
101 |
95000.00 |
106 |
Mateo |
103 |
95000.00 |
108 |
Zhang |
104 |
95000.00 |
Output Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT,
salary DECIMAL(10, 2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id, salary) VALUES
(100, 'Carlos', NULL, 120000.00),
(101, 'John', 100, 90000.00),
(102, 'Jorge', 101, 95000.00),
(103, 'Kwaku', 101, 105000.00),
(104, 'Paulo', 102, 110000.00),
(105, 'Richard', 102, 85000.00),
(106, 'Mateo', 103, 95000.00),
(107, 'Liu', 103, 108000.00),
(108, 'Zhang', 104, 95000.00);
CREATE TABLE IF NOT EXISTS salaries AS
SELECT id, name, manager_id, salary
FROM
employee
ORDER BY salary DESC NULLS LAST, name ASC NULLS FIRST
LIMIT 5
OFFSET 2;
ID |
NAME |
MANAGER_ID |
SALARY |
|---|---|---|---|
107 |
Liu |
103 |
108000.00 |
103 |
Kwaku |
101 |
105000.00 |
102 |
Jorge |
101 |
95000.00 |
106 |
Mateo |
103 |
95000.00 |
108 |
Zhang |
104 |
95000.00 |
Known Issues
没有已知问题。
Related EWIs.
无相关的 EWIs。
SELECT 列表
Description
SELECT 列表会命名您希望查询返回的列、函数和表达式。该列表表示查询的输出。（Redshift SQL 语言参考“SELECT 列表” (https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html)）
Snowflake 完全支持 查询起始选项。请记住，在 Snowflake 中，DISTINCT 和 ALL 选项必须放在查询的开头。
备注
在 Redshift 中，如果应用程序允许使用外键或无效的主键，则可能导致查询返回不正确的结果。例如，如果“主键”列中不包含所有唯一值，则 SELECT DISTINCT 查询可能会返回重复行。（Redshift SQL 语言参考“SELECT 列表” (https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html)）
语法¶
SELECT
[ TOP number ]
[ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]
示例源模式¶
Top 子句¶
输入代码：¶
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
SELECT TOP 5 id, name, manager_id
INTO top_employees
FROM employee;
SELECT * FROM top_employees;
ID |
NAME |
MANAGER_ID |
|---|---|---|
100 |
Carlos |
null |
101 |
John |
100 |
102 |
Jorge |
101 |
103 |
Kwaku |
101 |
110 |
Liu |
101 |
输出代码：¶
CREATE TABLE employee
(
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
CREATE TABLE IF NOT EXISTS top_employees AS
SELECT TOP 5 id, name, manager_id
FROM
employee;
SELECT * FROM
top_employees;
ID |
NAME |
MANAGER_ID |
|---|---|---|
100 |
Carlos |
null |
101 |
John |
100 |
102 |
Jorge |
101 |
103 |
Kwaku |
101 |
110 |
Liu |
101 |
ALL¶
输入代码：¶
SELECT ALL manager_id
INTO manager
FROM employee;
MANAGER_ID |
|---|
null |
100 |
101 |
101 |
101 |
102 |
103 |
103 |
103 |
104 |
104 |
102 |
104 |
输出代码：¶
CREATE TABLE IF NOT EXISTS manager AS
SELECT ALL manager_id
FROM
employee;
MANAGER_ID |
|---|
null |
100 |
101 |
101 |
101 |
102 |
103 |
103 |
103 |
104 |
104 |
102 |
104 |
DISTINCT¶
输入代码：¶
SELECT DISTINCT manager_id
INTO manager
FROM employee;
MANAGER_ID |
|---|
null |
100 |
101 |
102 |
103 |
104 |
输出代码：¶
CREATE TABLE IF NOT EXISTS manager AS
SELECT DISTINCT manager_id
FROM
employee;
MANAGER_ID |
|---|
null |
100 |
101 |
102 |
103 |
104 |
已知问题¶
没有已知问题。
相关的 EWIs。¶
无相关的 EWIs。
UNION、INTERSECT 和 EXCEPT¶
描述¶
UNION、INTERSECT 和 EXCEPT 集合运算符 用于比较和合并两个单独的查询表达式的结果。（Redshift SQL 语言参考“集合运算符” (https://docs.aws.amazon.com/redshift/latest/dg/r_UNION.html)）
Snowflake 完全支持 集合运算符。
Grammar Syntax
query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query
Sample Source Patterns
Input Code:
SELECT id, name, manager_id
INTO some_employees
FROM
employee
WHERE manager_id = 101
UNION
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 102
UNION ALL
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101
INTERSECT
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 103
EXCEPT
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 104;
ID |
NAME |
MANAGER_ID |
|---|---|---|
103 |
Kwaku |
101 |
110 |
Liu |
101 |
102 |
Jorge |
101 |
106 |
Mateo |
102 |
201 |
Sofía |
102 |
Output Code:
CREATE TABLE IF NOT EXISTS some_employees AS
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101
UNION
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 102
UNION ALL
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101
INTERSECT
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 103
EXCEPT
SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 104;
ID |
NAME |
MANAGER_ID |
|---|---|---|
102 |
Jorge |
101 |
103 |
Kwaku |
101 |
110 |
Liu |
101 |
106 |
Mateo |
102 |
201 |
Sofía |
102 |
Known Issues
没有已知问题。
Related EWIs.
无相关的 EWIs。
WHERE 子句
Description
WHERE 子句中包含联接表或将谓词应用于表列的条件。（Redshift SQL 语言参考“WHERE 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_WHERE_clause.html)）
Snowflake 完全支持 WHERE 子句。
语法¶
[ WHERE condition ]
示例源模式¶
输入代码：¶
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
SELECT id, name, manager_id
INTO employee_names
FROM employee
WHERE name LIKE 'J%';
ID |
NAME |
MANAGER_ID |
|---|---|---|
101 |
John |
100 |
102 |
Jorge |
101 |
输出代码：¶
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
CREATE TABLE IF NOT EXISTS employee_names AS
SELECT id, name, manager_id
FROM
employee
WHERE name LIKE 'J%' ESCAPE '\\';
ID |
NAME |
MANAGER_ID |
|---|---|---|
101 |
John |
100 |
102 |
Jorge |
101 |
已知问题¶
没有已知问题。
相关的 EWIs。¶
无相关的 EWIs。
WITH 子句¶
描述¶
WITH 子句是查询中的可选子句，需置于 SELECT INTO 语句之前。WITH 子句定义了一个或多个 common_table_expressions。每个公用表表达式 (CTE) 都定义了一个临时表，这类似于视图定义。您可以在 FROM 子句中引用这些临时表。（Redshift SQL 语言参考“WITH 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html)）
Snowflake 完全支持 WITH 子句。
Grammar Syntax
[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
--Where common_table_expression can be either non-recursive or recursive.
--Following is the non-recursive form:
CTE_table_name [ ( column_name [, ...] ) ] AS ( query )
--Following is the recursive form of common_table_expression:
CTE_table_name (column_name [, ...] ) AS ( recursive_query )
Sample Source Patterns
非递归形式
Input Code:
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2)
);
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES
(1, 101, '2024-02-01', 250.00),
(2, 102, '2024-02-02', 600.00),
(3, 103, '2024-02-03', 150.00),
(4, 104, '2024-02-04', 750.00),
(5, 105, '2024-02-05', 900.00);
WITH HighValueOrders AS (
SELECT
order_id,
customer_id,
order_date,
total_amount
FROM orders
WHERE total_amount > 500
)
SELECT * INTO high_value_orders FROM HighValueOrders;
SELECT * FROM high_value_orders;
ORDER_ID |
CUSTOMER_ID |
ORDER_DATE |
TOTAL_AMOUNT |
|---|---|---|---|
2 |
102 |
2024-02-02 |
600.00 |
4 |
104 |
2024-02-04 |
750.00 |
5 |
105 |
2024-02-05 |
900.00 |
Output Code:
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "03/03/2025", "domain": "test" }}';
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES
(1, 101, '2024-02-01', 250.00),
(2, 102, '2024-02-02', 600.00),
(3, 103, '2024-02-03', 150.00),
(4, 104, '2024-02-04', 750.00),
(5, 105, '2024-02-05', 900.00);
CREATE TABLE IF NOT EXISTS high_value_orders AS
WITH HighValueOrders AS (
SELECT
order_id,
customer_id,
order_date,
total_amount
FROM
orders
WHERE total_amount > 500
)
SELECT *
FROM
HighValueOrders;
SELECT * FROM
high_value_orders;
ORDER_ID |
CUSTOMER_ID |
ORDER_DATE |
TOTAL_AMOUNT |
|---|---|---|---|
2 |
102 |
2024-02-02 |
600.00 |
4 |
104 |
2024-02-04 |
750.00 |
5 |
105 |
2024-02-05 |
900.00 |
递归形式
Input Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
WITH RECURSIVE john_org(id, name, manager_id, level)
AS
(
SELECT id, name, manager_id, 1 AS level
FROM employee
WHERE name = 'John'
UNION ALL
SELECT e.id, e.name, e.manager_id, level + 1 AS next_level
FROM employee e, john_org j
WHERE e.manager_id = j.id and level < 4
)
SELECT DISTINCT id, name, manager_id into new_org FROM john_org ORDER BY manager_id;
SELECT * FROM new_org;
ID |
NAME |
MANAGER_ID |
|---|---|---|
101 |
John |
100 |
103 |
Kwaku |
101 |
102 |
Jorge |
101 |
110 |
Liu |
101 |
106 |
Mateo |
102 |
201 |
Sofía |
102 |
105 |
Richard |
103 |
110 |
Nikki |
103 |
104 |
Paulo |
103 |
120 |
Saanvi |
104 |
200 |
Shirley |
104 |
205 |
Zhang |
104 |
Output Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
CREATE TABLE IF NOT EXISTS new_org AS
WITH RECURSIVE john_org(id, name, manager_id, level)
AS
(
SELECT id, name, manager_id, 1 AS level
FROM
employee
WHERE
RTRIM( name) = RTRIM( 'John')
UNION ALL
SELECT e.id, e.name, e.manager_id, level + 1 AS next_level
FROM
employee e,
john_org j
WHERE e.manager_id = j.id and level < 4
)
SELECT DISTINCT id, name, manager_id
FROM
john_org
ORDER BY manager_id;
SELECT * FROM
new_org;
ID |
NAME |
MANAGER_ID |
|---|---|---|
101 |
John |
100 |
103 |
Kwaku |
101 |
102 |
Jorge |
101 |
110 |
Liu |
101 |
106 |
Mateo |
102 |
201 |
Sofía |
102 |
105 |
Richard |
103 |
110 |
Nikki |
103 |
104 |
Paulo |
103 |
120 |
Saanvi |
104 |
200 |
Shirley |
104 |
205 |
Zhang |
104 |
Known Issues
没有已知问题。
Related EWIs.
无相关的 EWIs。