mysql - MS Access等效查询

我正在尝试编写一个查询，我知道它与MySQL-to-MS-Access兼容。这是一个编程任务，所以我不希望得到直接的答案，但我对MS Access的SQL版本还不够了解。我自己编写了MySQL，并测试了它的工作性能。这时我意识到查询需要为MS-Access工作。下面是已知的代码：

SELECT `D`.`RENT_NUM`,
    `R`.`RENT_DATE`,
    `D`.`VID_NUM`,
    `M`.`MOVIE_TITLE`,
    `D`.`DETAIL_DUEDATE`,
    `D`.`DETAIL_RETURNDATE`,
    `D`.`DETAIL_FEE`,
    `D`.`DETAIL_RETURNDATE` - `D`.`DETAIL_DUEDATE` AS `DAYS_LATE`
FROM `detailrental` AS `D`
    JOIN `rental` AS `R` ON `D`.`RENT_NUM` = `R`.`RENT_NUM`
    JOIN `video` AS `V` ON `D`.`VID_NUM` = `V`.`VID_NUM`
    JOIN `movie` AS `M` ON `V`.`MOVIE_NUM` = `M`.`MOVIE_NUM`
WHERE `D`.`DETAIL_RETURNDATE` - `D`.`DETAIL_DUEDATE` > 0
    ORDER BY `R`.`RENT_NUM`, `M`.`MOVIE_TITLE`;

我一直在尝试转换到MS Access SQL，但仍然没有得到它。这是最近的一次尝试。

SELECT [D].[RENT_NUM],
    [R].[RENT_DATE],
    [D].[VID_NUM],
    [M].[MOVIE_TITLE],
    [D].[DETAIL_DUEDATE],
    [D].[DETAIL_RETURNDATE],
    [D].[DETAIL_FEE],
    [D].[DETAIL_RETURNDATE] - [D].[DETAIL_DUEDATE] AS [DAYS_LATE]
FROM [DETAILRENTAL] AS [D] INNER JOIN
(
  [RENTAL] AS [R] INNER JOIN
  (
    [VIDEO] AS [V] INNER JOIN [MOVIE] AS [M] ON [V].[MOVIE_NUM] = [M].[MOVIE_NUM]
  )  ON [D].[VID_NUM] = [V].[VID_NUM]
) ON [D].[RENT_NUM] = [R].[RENT_NUM]
WHERE [D].[DETAIL_RETURNDATE] - [D].[DETAIL_DUEDATE] > 0
    ORDER BY [R].[RENT_NUM], [M].[MOVIE_TITLE];

我收到的错误是Syntax error in JOIN operation.我知道这意味着什么，但我对MS-Access的SQL还不够了解，无法发现错误。

最佳答案

我已经有一段时间没有编写任何访问查询了，所以我只使用了官方文档http://msdn.microsoft.com/en-us/library/office/bb208854(v=office.12).aspx中描述的语法。发现里面有语法错误。因此，要使查询工作，必须用括号将JOIN括起来。但是每个ON语句仍然需要在每组括号内而不是在外部。这应该能起到作用。

SELECT [D].[RENT_NUM]
       ,[R].[RENT_DATE]
       ,[D].[VID_NUM]
       ,[M].[MOVIE_TITLE]
       ,[D].[DETAIL_DUEDATE]
       ,[D].[DETAIL_RETURNDATE]
       ,[D].[DETAIL_FEE]
       ,[D].[DETAIL_RETURNDATE] - [D].[DETAIL_DUEDATE] AS [DAYS_LATE]
    FROM (
           (
             (
               [detailrental] AS [D] )
             INNER JOIN [rental] AS [R]
                ON [D].[RENT_NUM] = [R].[RENT_NUM] )
           INNER JOIN [video] AS [V]
            ON D.VID_NUM = [V].[VID_NUM] )
    INNER JOIN [movie] AS [M]
        ON [V].[MOVIE_NUM] = [M].[MOVIE_NUM]
    WHERE [D].[DETAIL_RETURNDATE] - [D].[DETAIL_DUEDATE] > 0
    ORDER BY [R].[RENT_NUM]
       ,[M].[MOVIE_TITLE];

关于mysql - MS Access等效查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21839684/