将两个mysql查询合并为一个 [英] Combine two mysql query into one

问题描述

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

What is the proper syntax to combine these two queries?

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的用法不正确".

but I get "Incorrect usage of UNION and ORDER BY".

编辑 另外，我希望将结果单行返回.这样我就可以访问php中的值，例如

EDIT Additionally, I want the result to be returned in a single row. So that I can access the value in php eg

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

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

From Simon's suggestion, I should not use UNION because I want to return a single row of data

推荐答案

在第一个SELECT和第二个UNION中不能ORDER BY.

You can't ORDER BY in your first SELECT and then UNION it.

修改
但是，您可以

Edit
You can however

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

apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the 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;

这篇关于将两个mysql查询合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！