php - 使用mysql选择十大最新事物

我希望收到最近提交的十个补丁（前十个patchID desc）
从下面的查询开始，我在10上遇到错误。错误是syntax error, unexpected NUM, expecting END_OF_INPUT, or ';'。在以下查询中会导致此错误吗？

SELECT TOP 10 synth.*
FROM (
    SELECT p.`id`,
        p.`patchName`,
        p.`description`,
        p.`tfsID`,
        p.`codeRelease`,
        s.`name`,
        d.`deployStatus`,
        d.`deployedDate`,
        t.`testedStatus`,
        t.`testedDate`
    FROM `patches` AS p
    JOIN `deployed` AS d ON p.`id` = d.`PatchID`
    JOIN `servers` AS s ON d.`serverID` = s.`id`
    JOIN `tested` AS t ON p.`id` = t.`patchID`
) synth
ORDER BY synth.id DESC

最佳答案

TOP 10是MS SQL特定的事物，在MySQL中，它们改用LIMIT。

SELECT p.`id`,
    p.`patchName`,
    p.`description`,
    p.`tfsID`,
    p.`codeRelease`,
    s.`name`,
    d.`deployStatus`,
    d.`deployedDate`,
    t.`testedStatus`,
    t.`testedDate`
FROM `patches` AS p
JOIN `deployed` AS d ON p.`id` = d.`PatchID`
JOIN `servers` AS s ON d.`serverID` = s.`id`
JOIN `tested` AS t ON p.`id` = t.`patchID`
ORDER BY p.`id` DESC
LIMIT 10

每个服务器的限制语法差异很大，这是一个概述：

从显示10个结果的第100行开始（即101、102、103 ...）

PostgreSQL, MySQL and DB2: SELECT * FROM table LIMIT 10 OFFSET 100
Informix: SELECT SKIP 100 FIRST 10 FROM table
Microsoft SQL Server and Access: SELECT TOP 10 * FROM table -- skipping the offset here as it's a pain, search for it if you need it :)
Oracle: SELECT * FROM (SELECT *, ROW_NUMBER() OVER ORDER BY id) rnk FROM table) WHERE rnk BETWEEN 100 AND 110

关于php - 使用mysql选择十大最新事物，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17328311/