MySQL UNION 和 ORDER BY 的错误用法？

Question

提问by Yuda Prawira

how can i use unionand order byin mysql ?

我如何在 mysql 中使用union和order by？

select * from _member_facebook 
inner join _member_pts 
ON _member_facebook._fb_owner=_member_pts._username 
where _member_facebook._promote_point = 9 
ORDER BY RAND() limit 2 
UNION ALL
select * from _member_facebook 
inner join _member_pts 
ON _member_facebook._fb_owner=_member_pts._username 
where _member_facebook._promote_point = 8 limit 3

give me error

给我错误

#1221 - Incorrect usage of UNION and ORDER BY

any one can help ?

有人可以帮忙吗？

Answer 1

回答by Tudor Constantin

Try with:

尝试：

(
  select 
    * 
  from 
     _member_facebook 
   inner join 
     _member_pts 
   ON 
     _member_facebook._fb_owner=_member_pts._username 
  where 
    _member_facebook._promote_point = 9 
  ORDER BY RAND() 
  limit 2
) 
UNION ALL
(
  select 
    * 
  from 
    _member_facebook 
   inner join 
    _member_pts 
   ON 
     _member_facebook._fb_owner=_member_pts._username 
  where 
    _member_facebook._promote_point = 8 
  limit 3
)

Although, I think you should put the ORDER BYclause at the end of the second query

虽然，我认为您应该将该ORDER BY子句放在第二个查询的末尾

Answer 2

回答by álvaro González

With parenthesis:

带括号：

(
    SELECT *
    FROM _member_facebook
    INNER JOIN _member_pts
    ON _member_facebook._fb_owner         =_member_pts._username
    WHERE _MEMBER_FACEBOOK._PROMOTE_POINT = 9
    ORDER BY RAND()
    LIMIT 2
)
UNION ALL
(
    SELECT *
    FROM _MEMBER_FACEBOOK
    INNER JOIN _MEMBER_PTS
    ON _MEMBER_FACEBOOK._FB_OWNER         =_MEMBER_PTS._USERNAME
    WHERE _MEMBER_FACEBOOK._PROMOTE_POINT = 8
    LIMIT 3
)

Said that, it isn't mandatory for MySQL to keep the inner sorting in the outer clause—though it'll probablydo so since it needs to sort rows anyway to calculate the corresponding LIMITclauses.

也就是说，对于 MySQL 来说，在外部子句中保留内部排序并不是强制性的——尽管它可能会这样做，因为它无论如何都需要对行进行排序来计算相应的LIMIT子句。

Answer 3

回答by Pacerier

Explanation:

解释：

It's important to understand how this works to avoid "gotchas" in similar use cases. Note thatunion's syntax is somewhat "special":

重要的是要了解它是如何工作的，以避免类似用例中的“陷阱”。请注意，union的语法有些“特殊”：

_substatementunion all_substatementunion all_substatement[order by-clause] [limit-clause]

where "substatement" can optionally be surrounded by (and ). Some working examples:

其中“ substatement”可以选择被(和包围)。一些工作示例：

select 1 union all (select 2);
select 1 union all  select 2  union all (select 3);
select 1 union all (select 2) union all  select 3;
select 1 union all (select 2) union all (select 3);
select 1 union all (select 2) union all (select 3) union all  select 4;
select 1 union all (select 2) union all  select 3  union all (select 4);

select 1 union all (select 2);
select 1 union all  select 2  union all (select 3);
select 1 union all (select 2) union all  select 3;
select 1 union all (select 2) union all (select 3);
select 1 union all (select 2) union all (select 3) union all  select 4;
select 1 union all (select 2) union all  select 3  union all (select 4);

However, if you surround the first "substatement" with braces, you mustsurround all the other "substatement"s with braces:

但是，如果用大括号将第一个“子语句”括起来，则必须用大括号将所有其他“子语句”括起来：

(select 1) union all (select 2) union all (select 3);

(select 1) union all (select 2) union all (select 3);

(Note that the above point is not mentioned in the official docs.)

（请注意，官方文档中未提及上述要点。）

Failing to do that is a syntax error:

不这样做是语法错误：

mysql> (select 1) union all select 2; -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error in your SQL syntax; check the...
mysql> (select 1) union all (select 2) union all  select 3; -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error...
mysql> (select 1) union all  select 2  union all (select 3); -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error...

mysql> (select 1) union all select 2; -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error in your SQL syntax; check the...
mysql> (select 1) union all (select 2) union all  select 3; -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error...
mysql> (select 1) union all  select 2  union all (select 3); -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error...

Next, each "substatement" can contain where, group by, having, join, limit, but not order by.

接下来，每个“子语句”可以包含where, group by, having, join, limit，但不能包含order by。

If you'd like to use order by, the "substatement" that contains order bymust be surrounded by braces. (Which means they are no longer optional.)

如果您想使用order by，则包含的“子语句”order by必须用大括号括起来。（这意味着它们不再是可选的。）

Now, if we'd look at the syntax again:

现在，如果我们再看一下语法：

_substatementunion all_substatementunion all_substatement[order by-clause] [limit-clause]

we can see that the entire unionstatement ends with an optional order by/ limit. These two keywords apply to the entire unionstatement, not just the last "substatement":

我们可以看到整个union语句以可选的order by/结尾limit。这两个关键字适用于整个union语句，而不仅仅是最后一个“子语句”：

mysql> select 1
    -> union all
    -> select 2 limit 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

mysql> select 1
    -> union all
    -> select 2 limit 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

We've mentioned previously that the limitkeyword can also be applied to individual "substatement"s:

我们之前提到过，limit关键字也可以应用于单独的“ substatement”：

mysql> select 1 limit 1
    -> union all
    -> select 2;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

mysql> select 1 limit 1
    -> union all
    -> select 2;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

If you want to apply limitto the last "substatement" (as opposed to the entire unionstatement), you mustsurround the last "substatement" with braces:

如果要应用于limit最后一个“子union语句”（而不是整个语句），则必须用大括号将最后一个“子语句”括起来：

mysql> select 1
    -> union all
    -> (select 2 limit 1);
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

mysql> select 1
    -> union all
    -> (select 2 limit 1);
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

To apply limitto the the last "substatement" and alsoto the entire unionstatement, use:

要应用于limit最后一个“子语句”以及整个union语句，请使用：

mysql> select 1
    -> union all
    -> (select 2 limit 1)limit 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

mysql> select 1
    -> union all
    -> (select 2 limit 1)limit 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

It's the same with order by:

这与order by：

mysql> select 1
    -> union all
    -> (select 2 order by 1)order by 1;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

mysql> select 1
    -> union all
    -> (select 2 order by 1)order by 1;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

But note that applying order byto "substatement"s is meaninglessbecause the docs have explicitly statedthat order byis only guaranteed (cf.) to work when applied to the entire unionstatement:

但要注意，申请order by到“子语句” S是毫无意义的，因为该文档已明确表示这order by才能得到保证（参见当应用到整个）工作union语句：

–§– ..use of ORDER BYfor individual SELECTstatements implies nothing about the order in which the rows appear in the final result..

–§– ..ORDER BY用于单个SELECT语句的使用并不意味着行在最终结果中出现的顺序。

The only way order bywould make sense in a "substatement" is if you combine it with limit:

order by在“子语句”中唯一有意义的方法是将它与limit：

–§– ..the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does notnecessarily affect the order of those rows in the final UNIONresult.

-§- 在此上下文中使用..the ORDER BY的是典型地与结合LIMIT，使得它被用于确定所选择的行的子集来检索SELECT，即使它并不一定会影响那些行的顺序在最终UNION结果。

Also, if you want to combine select intowith union, there'll be more "gotchas" to watch out for. See issue 32858regarding this.

此外，如果您想与结合使用，则会有更多的“陷阱”需要注意。有关此问题，请参阅问题 32858。select intounion

Answer 4

回答by Diego Vintimilla Espinoza

The correct is:

正确的是：

(SELECT *
   FROM _member_facebook
   INNER JOIN _member_pts ON _member_facebook._fb_owner=_member_pts._username
   WHERE _member_facebook._promote_point = 9 LIMIT 2)
UNION ALL
  (SELECT *
   FROM _member_facebook
   INNER JOIN _member_pts ON _member_facebook._fb_owner=_member_pts._username
   WHERE _member_facebook._promote_point = 8 LIMIT 3)
ORDER BY 1

Answer 5

回答by anil kumar meena

try () i think like

试试 () 我觉得像

(SELECT  CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY)=(SELECT MIN(LENGTH(CITY)) FROM STATION) ORDER BY CITY LIMIT 1) 
UNION ALL
(SELECT  CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY)=(SELECT MAX(LENGTH(CITY)) FROM STATION) ORDER BY CITY LIMIT 1);

Answer 6

回答by Syed Quader

I think use of parenthesis is mandatory if you use order by or limit or both. I tried working on a query by using limit and order by interchangeably without parenthesis and the query did not work. It only worked after adding parenthesis.

我认为如果您使用 order by 或 limit 或两者都使用括号，则必须使用括号。我尝试通过使用 limit 和 order by 在没有括号的情况下互换来处理查询，但查询不起作用。它仅在添加括号后才起作用。

MySQL UNION 和 ORDER BY 的错误用法？

提问by Yuda Prawira

回答by Tudor Constantin

回答by álvaro González

回答by Pacerier

Explanation:

解释：

回答by Diego Vintimilla Espinoza

回答by anil kumar meena

回答by Syed Quader

相关推荐

最近更新

标签

MySQL UNION 和 ORDER BY 的错误用法？

提问by Yuda Prawira

回答by Tudor Constantin

回答by álvaro González

回答by Pacerier

Explanation:

解释：

回答by Diego Vintimilla Espinoza

回答by anil kumar meena

回答by Syed Quader

相关推荐

MySQL SELECT * FROM WHERE 没有找到所有记录

MySQL 两个查询的总和

如何从 MySQL 函数返回表

MySQL 在mysql中按范围分组

相关推荐

最近更新

标签