“table或view不存在”和“invalid identifier”错误与“with”子句

-- 示例：检查表是否存在
SELECT * FROM information_schema.tables WHERE table_name = 'your_table_name';

-- 示例：创建表
CREATE TABLE your_table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 示例：检查列是否存在
SELECT column_name FROM information_schema.columns WHERE table_name = 'your_table_name' AND column_name = 'your_column_name';

-- 示例：修正列名
ALTER TABLE your_table_name RENAME COLUMN old_column_name TO new_column_name;

-- 示例：普通CTE
WITH sales_data AS (
    SELECT product_id, SUM(sales) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT * FROM sales_data WHERE total_sales > 1000;

-- 示例：递归CTE
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;