SnowConvert AI - Redshift - 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 ]

示例源模式¶

输入代码：¶

Redshift¶

 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;

Results¶


EMPLOYEE_NAME	DEPARTMENT_NAME
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Sofía	Engineering

输出代码：¶

Redshift¶

 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;

Results¶

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)）

Note

Snowflake 完全支持 GROUP BY 子句。

语法¶

 GROUP BY expression [, ...]

示例源模式¶

输入代码：¶

Redshift¶

 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;

Results¶


MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
	1

输出代码：¶

Snowflake¶

 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;

Results¶


MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
	1

已知问题¶

没有已知问题。

HAVING 子句¶

描述¶

HAVING 子句可将条件应用于查询返回的中间分组结果集。（Redshift SQL 语言参考“HAVING 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html)）

Note

Snowflake 完全支持 HAVING 子句。

语法¶

 [ HAVING condition ]

示例源模式¶

输入代码：¶

Redshift¶

 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;

Results¶


MANAGER_ID	TOTAL_EMPLOYEES
101	3
103	3
104	3

输出代码：¶

Snowflake¶

 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;

Results¶


MANAGER_ID	TOTAL_EMPLOYEES
101	3
103	3
104	3

已知问题¶

没有已知问题。

LIMIT 和 OFFSET 子句¶

描述¶

LIMIT 和 OFFSET 子句可检索并跳过数字中指定的行数。

Note

Snowflake 完全支持 LIMIT 和 OFFSET 子句。

语法¶

 [ LIMIT { number | ALL } ]
[ OFFSET start ]

示例源模式¶

LIMIT 数¶

输入代码：¶

Redshift¶

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT 5;

Results¶


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

输出代码：¶

Snowflake¶

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT 5;

Results¶


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¶

输入代码：¶

Redshift¶

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT ALL;

Results¶


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

输出代码：¶

Snowflake¶

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL;

Results¶


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）。

输入代码：¶

Redshift¶

 SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
OFFSET 5;

Results¶


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

输出代码：¶

Snowflake¶

 CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL
OFFSET 5;

Results¶


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

已知问题¶

没有已知问题。

局部变量和参数¶

描述¶

Redshift also allows SELECT INTO variables when the statement is executed inside stored procedures.

Note

Snowflake 完全支持此模式。

语法¶

 SELECT [ select_expressions ] INTO target [ select_expressions ] FROM ...;

示例源模式¶

SELECT INTO（表达式在左侧）¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
    var1 int;
BEGIN
     select 10, 100 into param1, var1;
END;
$$ LANGUAGE plpgsql;

Results¶


param1
10

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE test_sp1 (param1 OUT int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            var1 int;
BEGIN
     select 10, 100 into
                : param1,
                : var1;
END;
$$;

Results¶


TEST_SP1
{ "param1":10 }

SELECT INTO（表达式在右侧）¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
    var1 int;
BEGIN
     select into param1, var1 10, 100;
END;
$$ LANGUAGE plpgsql;

Results¶


param1
10

输出代码：¶

由于 Snowflake 不支持 SELECT INTO 的此语法，因此将表达式移至 INTO 的左侧。

Snowflake¶

 CREATE OR REPLACE PROCEDURE test_sp1 (param1 OUT int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            var1 int;
BEGIN
     select
                10, 100
            into
                : param1,
                : var1;
END;
$$;

Results¶


TEST_SP1
{ "param1":10 }

已知问题¶

没有已知问题。

ORDER BY 子句¶

描述¶

ORDER BY 子句对查询的结果集进行排序。（Redshift SQL 语言参考“Order By 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_ORDER_BY_clause.html)）

Note

Snowflake 完全支持 ORDER BY 子句。

语法¶

 [ ORDER BY expression [ ASC | DESC ] ]
[ NULLS FIRST | NULLS LAST ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]

示例源模式¶

输入代码：¶

Redshift¶

 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;

Results¶


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

输出代码：¶

Snowflake¶

 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;

Results¶


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

已知问题¶

没有已知问题。

SELECT 列表¶

描述¶

SELECT 列表会命名您希望查询返回的列、函数和表达式。该列表表示查询的输出。（Redshift SQL 语言参考“SELECT 列表” (https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html)）

Note

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 子句¶

输入代码：¶

Redshift¶

 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;

Results¶


ID	NAME	MANAGER_ID
100	Carlos	null
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

输出代码：¶

Snowflake¶

 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;

Results¶


ID	NAME	MANAGER_ID
100	Carlos	null
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

ALL¶

输入代码：¶

Redshift¶

SELECT ALL manager_id
INTO manager
FROM employee;

Results¶


MANAGER_ID
null
100
101
101
101
102
103
103
103
104
104
102
104

输出代码：¶

Snowflake¶

 CREATE TABLE IF NOT EXISTS manager AS
SELECT ALL manager_id
FROM
employee;

Results¶


MANAGER_ID
null
100
101
101
101
102
103
103
103
104
104
102
104

DISTINCT¶

输入代码：¶

Redshift¶

SELECT DISTINCT manager_id
INTO manager
FROM employee;

Results¶


MANAGER_ID
null
100
101
102
103
104

输出代码：¶

Snowflake¶

 CREATE TABLE IF NOT EXISTS manager AS
SELECT DISTINCT manager_id
FROM
employee;

Results¶


MANAGER_ID
null
100
101
102
103
104

已知问题¶

没有已知问题。

UNION、INTERSECT 和 EXCEPT¶

描述¶

UNION、INTERSECT 和 EXCEPT 集合运算符 用于比较和合并两个单独的查询表达式的结果。（Redshift SQL 语言参考“集合运算符” (https://docs.aws.amazon.com/redshift/latest/dg/r_UNION.html)）

Note

Snowflake 完全支持集合运算符。

语法¶

 query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query

示例源模式¶

输入代码：¶

Redshift¶

 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;

Results¶


ID	NAME	MANAGER_ID
103	Kwaku	101
110	Liu	101
102	Jorge	101
106	Mateo	102
201	Sofía	102

输出代码：¶

Snowflake¶

 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;

Results¶


ID	NAME	MANAGER_ID
102	Jorge	101
103	Kwaku	101
110	Liu	101
106	Mateo	102
201	Sofía	102

已知问题¶

没有已知问题。

WHERE 子句¶

描述¶

WHERE 子句中包含联接表或将谓词应用于表列的条件。（Redshift SQL 语言参考“WHERE 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_WHERE_clause.html)）

Note

Snowflake 完全支持 WHERE 子句。

语法¶

 [ WHERE condition ]

示例源模式¶

输入代码：¶

Redshift¶

 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%';

Results¶


ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101

输出代码：¶

Snowflake¶

 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 '\\';

Results¶


ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101

已知问题¶

没有已知问题。

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)）

Note

Snowflake 完全支持 WITH 子句。

语法¶

 [ 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 )

示例源模式¶

非递归形式¶

输入代码：¶

Redshift¶

 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;

Results¶


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

输出代码：¶

Snowflake¶

 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;

Results¶


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

递归形式¶

输入代码：¶

Redshift¶

 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;

Results¶


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

输出代码：¶

Snowflake¶

 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": "07/11/2025",  "domain": "no-domain-provided" }}';

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 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
   FROM
   john_org
   ORDER BY manager_id;
SELECT * FROM
   new_org;

Results¶


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

已知问题¶

没有已知问题。

SnowConvert AI - Redshift - SELECT INTO¶

描述¶

语法¶

FROM 子句¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

Results¶

输出代码：¶

Redshift¶

Results¶

已知问题¶

相关的 EWIs。¶

GROUP BY 子句¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

Results¶

输出代码：¶

Snowflake¶

Results¶

已知问题¶

相关的 EWIs。¶

HAVING 子句¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

Results¶

输出代码：¶

Snowflake¶

Results¶

已知问题¶

相关的 EWIs。¶

LIMIT 和 OFFSET 子句¶

描述¶

语法¶

示例源模式¶

LIMIT 数¶

输入代码：¶

Redshift¶

Results¶

输出代码：¶

Snowflake¶

Results¶

LIMIT ALL¶

输入代码：¶

Redshift¶

Results¶

输出代码：¶

Snowflake¶

Results¶

OFFSET（无 LIMIT）¶

输入代码：¶

Redshift¶

Results¶

输出代码：¶

Snowflake¶

Results¶

已知问题¶

相关的 EWIs。¶

局部变量和参数¶

描述¶

语法¶

示例源模式¶

SELECT INTO（表达式在左侧）¶

输入代码：¶

Redshift¶

Results¶

输出代码：¶

Snowflake¶

Results¶

SELECT INTO（表达式在右侧）¶

输入代码：¶

Redshift¶