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

Question

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;

我想将此语句的输出转换为 View 。我以为我可以这样做:

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

但是它出错了:

Incorrect syntax near 'WITH'

有人可以帮忙吗？

TIA,约翰

Answer 1

去掉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