我正在尝试创建一个mysql查询,根据currend ID选择前面的5行和5行
SELECT * FROM posts WHERE PID<='10' ORDER BY PID DESC LIMIT 7
UNION ALL
SELECT * FROM posts WHERE PID>'10' ORDER BY PID ASC LIMIT 6
不过,由于某些原因,查询不起作用。我是不是错过了什么?我在论坛上找到了这个解决方案。
试试这个:
(SELECT *
FROM posts
WHERE PID <= '10'
ORDER BY PID DESC LIMIT 7
)
UNION ALL
(SELECT *
FROM posts
WHERE PID > '10'
ORDER BY PID ASC LIMIT 6
)
ORDER BY PID ASC;
sqlfiddle演示
PID
可能是int类型。因此,您应该使用以下(SQL Fiddle):
(SELECT * FROM posts WHERE PID <= 10 ORDER BY PID DESC LIMIT 7)
UNION ALL
(SELECT * FROM posts WHERE PID > 10 ORDER BY PID ASC LIMIT 6)
正如您所看到的,这两个查询的顺序也是单独维护的。
DOCS
要将ORDERBY或LIMIT应用于单个SELECT,请将子句放在SELECT的括号内: