关键字“SELECT”附近的语法不正确。创建视图错误

Question

我正在为作业创建数据库视图，

但我在代码中不断收到此错误。

CREATE VIEW Rental_View

SELECT Mo.Movie_ID, copy_id, mo.Movie_Name, format_name
FROM Movies AS mo JOIN copies AS co
ON mo.Movie_ID = co.movie_id  
JOIN format AS fo
ON co.Format_id = fo.format_id

SELECT c.customer_id, rental_ID, DATEDIFF (day, rental_date, return_date) AS  rental_duration' , c.first_name + ' ' + c.last_name AS customer_name
FROM Customers AS c INNER JOIN rentals AS r
ON c.customer_ID = r.customer_ID

在第一个 SELECT 语句中，它带有红色下划线，并且出现错误

“关键字“SELECT”附近的语法不正确。”

我真的不知道如何纠正这个问题，因为我对 SQL 还很陌生，任何帮助将不胜感激。

提前致谢。

Answer 1

您需要 AS 关键字：

CREATE VIEW Rental_View   AS            --- <------ AS needed here

SELECT mo.Movie_ID                     --- mo, not Mo
     , co.copy_id                      --- play safe and declare which table 
     , mo.Movie_Name
     , fo.format_name                  --- are these two columns from
FROM Movies AS mo JOIN copies AS co
ON mo.Movie_ID = co.movie_id  
JOIN format AS fo
ON co.Format_id = fo.format_id  
;                                     --- add a ; if you want to
                                      --- run multiple statements

---

如果您想将两个 SELECT 合并为一个，则需要 JOIN两者的表，如下所示：

CREATE VIEW Rental_View   AS           

SELECT mo.Movie_ID                     
     , co.copy_id                      
     , mo.Movie_Name
     , fo.format_name                  
     , c.customer_id
     , rental_ID
     , DATEDIFF (day, rental_date, return_date) AS rental_duration 
     , c.first_name + ' ' + c.last_name AS customer_name
FROM Movies AS mo 
  JOIN copies AS co
    ON mo.Movie_ID = co.movie_id  
  JOIN format AS fo
    ON co.Format_id = fo.format_id  

  JOIN rentals AS r
    ON r.movie_id = mo.Movie_id        --- just a guess, you have to write this
  JOIN Customers AS c 
    ON c.customer_ID = r.customer_ID

Answer 2

CREATE VIEW Rental_View
AS

您缺少“AS”关键字

Answer 3

您看起来在这段代码中的 rental_duration 之后有一个错误的单引号 -

SELECT c.customer_id, rental_ID, DATEDIFF (day, rental_date, return_date) AS  rental_duration' , c.first_name + ' ' + c.last_name AS customer_name
FROM Customers AS c INNER JOIN rentals AS r
ON c.customer_ID = r.customer_ID

尝试

SELECT c.customer_id, rental_ID, DATEDIFF (day, rental_date, return_date) AS  rental_duration , c.first_name + ' ' + c.last_name AS customer_name
FROM Customers AS c INNER JOIN rentals AS r
ON c.customer_ID = r.customer_ID

Answer 4

您可以尝试将第一个选择从

SELECT Mo.Movie_ID, copy_id, mo.Movie_Name, format_name

更改为

SELECT mo.Movie_ID, copy_id, mo.Movie_Name, format_name

Answer 5

尝试：

CREATE VIEW Rental_View
AS
  SELECT Mo.Movie_ID, copy_id, mo.Movie_Name, format_name
  FROM Movies AS mo JOIN copies AS co
  ON mo.Movie_ID = co.movie_id  
  JOIN format AS fo
  ON co.Format_id = fo.format_id

CREATE VIEW Rental_Duration
AS
      SELECT c.customer_id, rental_ID, DATEDIFF (day, rental_date, return_date) AS  rental_duration , c.first_name +   ' ' + c.last_name AS customer_name
      FROM Customers AS c INNER JOIN rentals AS r
     ON c.customer_ID = r.customer_ID

通常情况下，您不会创建一个返回多个结果集的视图。如果这确实是您想要的，那么 BEGIN/END 块应该可以解决问题（未经测试 - 现在不在任何 RDMS 前面）：

CREATE VIEW Rental_View
AS
BEGIN
  SELECT Mo.Movie_ID, copy_id, mo.Movie_Name, format_name
  FROM Movies AS mo JOIN copies AS co
  ON mo.Movie_ID = co.movie_id  
  JOIN format AS fo
  ON co.Format_id = fo.format_id

  SELECT c.customer_id, rental_ID, DATEDIFF (day, rental_date, return_date) AS  rental_duration , c.first_name +   ' ' + c.last_name AS customer_name
      FROM Customers AS c INNER JOIN rentals AS r
     ON c.customer_ID = r.customer_ID
END

Answer 6

您发布的代码中有多个错误。

1. CREATE VIEW 语句在 SELECE 语句之前需要 AS 关键字。你可以这样做：

   创建视图 Rental_View
   作为
   SELECT Mo.Movie_ID、copy_id、mo.Movie_Name、format_name
   FROM 电影 AS mo JOIN 副本 AS co
   ON mo.Movie_ID = co.movi​​e_id  
   JOIN 格式 AS fo
   ON co.Format_id = fo.format_id
   

2. 我可以看到另一个 SELECT 语句，这在视图中没有任何意义。添加第二个 SELECT 语句将导致错误“关键字‘SELECT’附近的语法不正确。”对于第二个 SELECT 语句。