SQL - 关键字附近的语法不正确

Question

我的查询存在小问题：

CREATE VIEW rental_view
AS 
   SELECT 
       m.movie_id, movie_name, co.copy_id, 
       f.format_id, format_name, cu.customer_id, 
       (first_name + ' ' + surname) AS customer_name, 
       rental_id, rental_date, return_date, 
       ISNULL(return_date, CAST(DATEDIFF(dd, rental_date, GETDATE() ) AS rental_duration 
   FROM 
         movie AS m 
   INNER JOIN copy AS co ON m.movie_id = co.movie_id
   INNER JOIN format AS f ON co.format_id = f.format_id
   INNER JOIN rental ON co.copy_id = rental.copy_id
   INNER JOIN customer AS cu ON rental.customer_id = cu.customer_id

导致错误：

消息 156，级别 15，状态 1，
过程 Rental_view，第 3 行关键字“FROM”附近的语法不正确。

我对此进行了很长时间的尝试，但无法解决。

感谢您的帮助。

Answer 1

您在这一行中缺少两个括号：

AS SELECT 
    m.movie_id, movie_name, co.copy_id, f.format_id, format_name, 
    cu.customer_id, (first_name + ' ' + surname) AS customer_name, 
    rental_id, rental_date, return_date, 
    ISNULL(
        return_date, 
        CAST(DATEDIFF(dd, rental_date, GETDATE()))) AS rental_duration 

无论如何，您的语法在其他部分是错误的：

• CAST 希望转换字段、关键字 AS 和新类型（请参阅 docs)
• DATEDIFF 需要两个参数（参见 文档) <-- 这个如果您使用的是 MySql；使用 MS-SQL 是正确的

Answer 2

试试这个：

  CREATE VIEW rental_view
  AS SELECT m.movie_id, 
movie_name, 
co.copy_id, 
f.format_id, 
format_name, 
cu.customer_id, 
(first_name + ' ' + surname) AS customer_name, 
rental_id, 
rental_date, 
return_date,
ISNULL(return_date,
CAST(DATEDIFF(dd, rental_date, GETDATE()) AS *type desired here*)) AS name of column
  FROM movie as m 
  INNER JOIN copy AS co
  ON m.movie_id = co.movie_id
  INNER JOIN format AS f
  ON co.format_id = f.format_id
  INNER JOIN rental 
  ON co.copy_id = rental.copy_id
  INNER JOIN customer AS cu
  ON rental.customer_id = cu.customer_id