联合所有和限制在MySQL

Let's say I want to perform this query:

假设我想执行这个查询:

(SELECT a FROM t1 WHERE a=10 AND B=1) 
UNION ALL 
(SELECT a FROM t2 WHERE a=11 AND B=2) 
UNION ALL 
(SELECT a FROM t3 WHERE a=12 AND B=3) 
ORDER BY a LIMIT 1000;

Is MySQL smart enough to skip "t3" if 550 results are available in "t1" and 450 in "t2"?

如果“t1”有550个结果，“t2”有450个结果，MySQL是否足够智能跳过“t3”?

I'm looking at MySQL docs (http://dev.mysql.com/doc/refman/5.1/en/union.html) but can't seem to find the answer.

我正在查看MySQL文档(http://dev.sqmyl.com/doc/refman/5.1 /en/union.html)，但似乎找不到答案。

2 个解决方案

#1

As specified in UNION Syntax description (http://dev.mysql.com/doc/refman/5.1/en/union.html):

如UNION语法描述中所述(http://dev.sqmyl.com/doc/refman/5.1/en/union.html):

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

UNION的默认行为是从结果中删除重复的行。可选的DISTINCT关键字除了默认值外没有其他作用，因为它还指定了重复行删除。使用可选的ALL关键字，不会发生重复行删除，结果包括所有SELECT语句中的所有匹配行。

I suppose, that's the answer to your question.

我想这就是你问题的答案。

#2

It works for me I'm using MySQL.

我用的是MySQL。

but make sure the limit number is always the same for all

但是要确保所有的极限数都是一样的

in that example it gets you 3 results from each table

在这个例子中，它从每个表中得到3个结果

 (SELECT a FROM t1 WHERE a=10 AND B=1 LIMIT 9) 
 UNION ALL 
 (SELECT a FROM t2 WHERE a=11 AND B=2 LIMIT 9) 
 UNION ALL 
 (SELECT a FROM t3 WHERE a=12 AND B=3 LIMIT 9)

#1