将两个mysql查询合并为一个
将两个mysql查询合并为一个
提问于 2013-01-11 15:48:02
将这两个查询组合在一起的正确语法是什么？
SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1和
SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1我试过：
SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1
UNION
SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1;但我得到了“不正确的使用联合和订购的”。
另外，编辑，我希望结果在一行中返回。这样我就可以访问php中的值了。
$row['nextclick'] and $row['topclick'] 根据Simon的建议，我不应该使用UNION，因为我想返回一行数据
回答 3
Stack Overflow用户
回答已采纳
发布于 2013-01-11 15:49:50
您不能在您的第一个ORDER BY，然后UNION它。
编辑
不过，你可以
对单个SELECT应用ORDER或place，将子句放在括住SELECT的括号内：
就像在MySQL UNION documentation一样
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);，这将使您的SQL
(SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1)
UNION
(SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1);编辑2
返回array
SELECT (SELECT clicks
FROM clicksTable
WHERE clicks > 199
ORDER BY clicks ASC
LIMIT 1) AS NextClick,
(SELECT clicks
FROM clicksTable
ORDER BY clicks DESC
LIMIT 1) AS TopClick;Stack Overflow用户
发布于 2013-01-11 15:49:54
SELECT clicks FROM
(SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1) A
UNION
SELECT clicks FROM
(SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1) B;Stack Overflow用户
发布于 2013-01-11 15:51:09
首先，您想要union还是union all？
问题在于第一部分的order by。您可以使用子查询修复此问题：
(select * from (SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1))
UNION ALL
(select * from (SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1))在union表达式中，只允许在结尾处使用order by，并且它适用于整个表达式。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14281663
复制相关文章
相似问题
腾讯云开发者
