联盟和订单使用错误?

how can i use union and order by in mysql ?

在mysql中如何使用union和order ?

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 ?

谁能帮忙?

4 个解决方案

#1

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 BY clause at the end of the second query

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

#2

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 probably do so since it needs to sort rows anyway to calculate the corresponding LIMIT clauses.

也就是说，MySQL并不强制将内部排序保留在外部条款中——尽管它可能会这样做，因为它需要对行进行排序以计算相应的限制条款。

#3

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]

子语句联合所有子语句联合所有子语句[order by-clause] [limited -子句]

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

其中“子语句”可选地被(和)包围。一些工作的例子:

• 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);
  

• 选择1 union all(选择2);

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

但是，如果你用大括号括住第一个“子语句”，你必须用大括号括住所有其他的“子语句”:

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

• (选择1)联合所有(选择2)联合所有(选择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;——错误，因为并非所有的“子语句”都已准备好

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

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

If you'd like to use order by, the "substatement" that contains order by must 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]

子语句联合所有子语句联合所有子语句[order by-clause] [limited -子句]

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

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

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

• mysql >选择1

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

我们之前提到过limit关键字也可以应用于单个的"substatement"s:

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

• mysql>选择1限制1

If you want to apply limit to the last "substatement" (as opposed to the entire union statement), you must surround the last "substatement" with braces:

如果要对最后一个“子语句”(与整个union语句相反)应用limit，则必须在最后一个“子语句”周围加上大括号:

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

• mysql >选择1

To apply limit to the the last "substatement" and also to the entire union statement, use:

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

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

• mysql >选择1

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 >选择1

But note that applying order by to "substatement"s is meaningless because the docs have explicitly stated that order by is only guaranteed (cf.) to work when applied to the entire union statement:

但是请注意，将order by应用于“子语句”s是没有意义的，因为文档明确地声明，order by只保证(cf)在应用于整个union语句时有效:

 –§–  ..use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result..

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

The only way order by would make sense in a "substatement" is if you combine it with 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 not necessarily affect the order of those rows in the final UNION result.

-§. .在此上下文中，ORDER BY的使用通常与LIMIT结合在一起，因此它用于确定要为SELECT检索的所选行的子集，即使它不一定会影响最终UNION结果中这些行的顺序。

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

此外，如果您想要将select合并到union中，则需要注意更多的“陷阱”。见第32858期。

#4

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