将两个mysql查询合并为一个

一尘不染

将两个mysql查询合并为一个

sql

结合这两个查询的正确语法是什么？

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;

但我收到“ UNION和ORDER BY的用法不正确”。

编辑此外，我希望结果在一行中返回。这样我就可以访问php中的值，例如

$row['nextclick'] and $row['topclick']

根据西蒙的建议，我不应该使用UNION，因为我想返回一行数据

阅读 125

2021-05-23

共1个答案

一尘不染

您不能先ORDER BY进入SELECT，然后再进入UNION。

编辑
但是你可以

将ORDER BY或LIMIT应用于单个SELECT，将子句放在包围SELECT的括号内：

如MySQL UNION文档中

(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;

2021-05-23