时间戳

首页 > 解决方案 > WITH AS (SELECT) 语句的 CREATE VIEW 语法

sql - WITH AS (SELECT) 语句的 CREATE VIEW 语法

问题描述

SQL Server：我有以下工作 SQL 语句：

WITH required_skills AS
(
    SELECT DISTINCT 
        e.empl_ID, e.job_code, j.skill_ID, j.skill_desc
    FROM 
        emplskills e
    JOIN 
        jobskills j ON e.job_code = j.job_code
)
SELECT 
    r.empl_ID, e.emplName, r.job_code, r.skill_ID, r.skill_desc
FROM
    required_skills r
FULL OUTER JOIN 
    emplskills e ON r.empl_ID = e.empl_ID
                 AND r.skill_ID = e.skill_ID;

我想将此语句的输出转换为视图。我以为我可以这样做：

CREATE VIEW MY_VIEW 
AS
    (WITH required_skills AS
     (
         SELECT DISTINCT 
             e.empl_ID, e.job_code, j.skill_ID, j.skill_desc
         FROM 
             emplskills e
         JOIN 
             jobskills j ON e.job_code = j.job_code
     )
     SELECT 
         r.empl_ID, e.emplName, r.job_code, r.skill_ID, r.skill_desc
     FROM 
         required_skills r
     FULL OUTER JOIN 
         emplskills e ON r.empl_ID = e.empl_ID AND r.skill_ID = e.skill_ID
);

但它出错了：

'WITH' 附近的语法不正确

有人可以帮忙吗？

TIA，约翰

标签： sqlsql-serverviewsyntax

解决方案

去掉 AS 后面的括号。

CREATE VIEW MyViewName
AS
WITH required_skills as (
      SELECT DISTINCT e.empl_ID, e.job_code, j.skill_ID, j.skill_desc
     FROM emplskills e
     JOIN jobskills j
       ON e.job_code = j.job_code
)
SELECT r.empl_ID, e.emplName, r.job_code, r.skill_ID, r.skill_desc
  FROM required_skills r
       FULL OUTER JOIN emplskills e
       ON r.empl_ID = e.empl_ID
      AND r.skill_ID = e.skill_ID

推荐阅读