首页 文章

在查询中使用内部联接时出现SQL语法错误

提问于
浏览 次
0

工作查询

(   SELECT R.ref as ref, 
           R.field8, 
           R.file_extension, 
           DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, 
           Ra.rank as rank 
    FROM resource R 
    INNER  JOIN resource_ranking Ra ON R.ref = Ra.ref_id 
    ORDER BY Ra.rank limit 2
) 
UNION ALL
(   SELECT R.ref as ref, 
           R.field8, 
           R.file_extension, 
           DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, 
           '99' as rank  
    FROM resource R
    LEFT JOIN (
           SELECT ref_id 
           FROM resource_ranking 
           ORDER BY rank LIMIT 2
    ) AS B ON R.ref = B.ref_id
    INNER JOIN collection_resource C ON R.ref = C.resource
    WHERE B.ref_id IS NULL 
      AND R.ref !=  '-1' AND C.collection = 1 
)
ORDER BY rank ASC, ref DESC LIMIT 0,  8

上面的查询工作正常我想使用内部联接向现有查询添加另一个表

下面是我使用内部联接的书面查询

(   SELECT R.ref as ref, 
           R.field8, 
           R.file_extension, 
           DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, 
           Ra.rank as rank 
    FROM resource R 
    INNER  JOIN resource_ranking Ra ON R.ref = Ra.ref_id 
    ORDER BY Ra.rank limit 2
    INNER  JOIN resource_rating Rr ON R.ref = Rr.resource_id 
    ORDER BY Rr.rating DESC
) 
UNION ALL
(   SELECT R.ref as ref, 
           R.field8, 
           R.file_extension, 
           DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, 
           '99' as rank  
    FROM resource R
    LEFT JOIN (
         SELECT ref_id 
         FROM resource_ranking 
         ORDER BY rank LIMIT 2
    ) AS B ON R.ref = B.ref_id
    INNER JOIN collection_resource C ON R.ref = C.resource
    WHERE B.ref_id IS NULL AND Rr.userid=1 
      AND R.ref !=  '-1' AND C.collection = 1
)
ORDER BY rank ASC, ref DESC LIMIT 0, 8

但是，这会产生以下错误：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN resource_rating Rr ON R.ref = Rr.resource_id ORDER BY Rr.rating DESC' at line 3
mysql

4 回答

  • 1

    摆脱这个后尝试：

    ORDER BY Ra.rank limit 2

    如果您只需要连接中的两行，则在第二个之后放置LIMIT 2 .

    回复于
  • 0

    这是你的第一个子查询：

    SELECT R.ref as ref, R.field8, R.file_extension,
       DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, Ra.rank as rank
FROM resource R  INNER JOIN
     resource_ranking Ra
     ON R.ref = Ra.ref_id
ORDER BY Ra.rank
limit 2
INNER  JOIN resource_rating Rr ON R.ref = Rr.resource_id ORDER BY Rr.rating DESC)

    inner join 在错误的地方 . 试试这个：

    SELECT R.ref as ref, R.field8, R.file_extension,
       DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, Ra.rank as rank
FROM resource R  INNER JOIN
     resource_ranking Ra
     ON R.ref = Ra.ref_id INNER JOIN
     resource_rating Rr
     ON R.ref = Rr.resource_id
ORDER BY Ra.rank
limit 2

    我不确定附加的 order by 条款是什么 .

    如果您学习格式化查询以使其可读，则更容易找到语法错误 .

    回复于
  • 0

    错误在这里：

    INNER  JOIN resource_ranking Ra ON R.ref = Ra.ref_id 
ORDER BY Ra.rank limit 2
INNER  JOIN resource_rating Rr ON R.ref = Rr.resource_id 
ORDER BY Rr.rating DESC

    SELECT语句只能有一个ORDER BY子句，它必须是语句中的最后一个子句（ORDER BY必须在末尾）：

    SELECT ....
FROM table1
JOIN table2 ON ....
JOIN ......
WHERE ...
ORDER BY ...
    回复于
  • 0
    (SELECT R.ref as ref, R.field8, R.file_extension, Rr.rating, DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, Ra.rank as rank FROM resource R 
    INNER  JOIN resource_ranking Ra ON R.ref = Ra.ref_id 
    INNER JOIN resource_rating Rr ON R.ref = Rr.resource_id
ORDER BY Ra.rank limit 2) 
UNION ALL
    (SELECT R.ref as ref, R.field8, R.file_extension, Rr.rating, DATE_FORMAT(R.creation_date,'%e/%c/%Y') as date, '99' as rank  FROM resource R
    LEFT JOIN (SELECT ref_id FROM resource_ranking ORDER BY rank LIMIT 2) AS B ON R.ref = B.ref_id
    INNER JOIN collection_resource C ON R.ref = C.resource
    INNER JOIN resource_rating Rr ON R.ref = Rr.resource_id
    WHERE B.ref_id IS NULL AND Rr.user_id = 1 
    AND R.ref !=  '-1' AND C.collection = 1)
    ORDER BY rank ASC, ref DESC LIMIT 0, 8
    回复于

相关问题