INSERT INTO SELECT不能使用公用表表达式(CTE)定义的临时结果集

Viewed 51
  1. test_table表结构
    CREATE TABLE testdb.test_table(
    user_id BIGINT NOT NULL COMMENT "用户 ID",
    name VARCHAR(20) COMMENT "用户姓名",
    age INT COMMENT "用户年龄"
    )
    DUPLICATE KEY(user_id)
    DISTRIBUTED BY HASH(user_id) BUCKETS 10;

  2. test_table2表结构
    CREATE TABLE testdb.test_table2 LIKE testdb.test_table;

  3. INSERT INTO SELECT语句
    WITH temp as (
    SELECT user_id, name, age FROM test_table WHERE age < 30
    )
    INSERT INTO test_table2 (user_id, name, age)
    SELECT user_id, name, age FROM temp

  4. 错误
    image.png

2 Answers

image.png
这样也是可以的,一定要有(字段)