Mysql 选择最后 2 个元素升序，然后是第一个元素

Question

我想按升序选择最后两个元素，然后选择第一个元素。这是我的代码

   SELECT products.*, locations.logo FROM 
    (SELECT products.* FROM 
    (SELECT products.* FROM products AS products  ORDER BY products.id DESC )
    AS products LEFT JOIN users ON users.id=products.userid WHERE users.hide=0)
    AS products LEFT JOIN locations ON products.location=locations.id LIMIT 2
    UNION SELECT products.*, locations.logo FROM 
    (SELECT products.* FROM 
    (SELECT products.* FROM products AS products  ORDER BY products.id ASC ) 
    AS products LEFT JOIN users ON users.id=products.userid WHERE users.hide=0)
    AS products LEFT JOIN locations ON products.location=locations.id LIMIT 3

例如现在我得到了 20 种产品

20, 19, 1(按 ID 排序)。

我正在尝试获得 19、20、1。

此时，上述语句根据 E.g. 起作用。我知道我必须添加一个 ORDER BY 子句，但我不知道在我的试验中出现错误的原因

"Incorrect usage of UNION and ORDER BY"

有人可以帮我吗？

Answer 1

你可以做这样的事情

SELECT id
  FROM
(
  (
    SELECT id, 0 sort_order
      FROM Table1
     ORDER BY id DESC
     LIMIT 2
  )
  UNION ALL
  (
    SELECT id, 1 sort_order
      FROM Table1
     ORDER BY id 
     LIMIT 1
  )
) q
 ORDER BY sort_order, id

输出:

| ID ||----|| 19 || 20 ||  1 |

这里是SQLFiddle 演示