mysql - usage - order by group by一起用




UNION和ORDER BY的使用不正确? (4)

我如何在mysql中使用unionorder

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

给我错误

#1221 - Incorrect usage of UNION and ORDER BY

任何人都可以帮忙吗?


说明:

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

子语句 union all 子语句 union all 子语句 [ order by -clause] [ limit -clause]

其中“ 子语句 ”可以选择性地被()包围。 一些工作实例:

  • 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) union all (select 3);

(请注意, 官方文档中未提及上述要点。)

不这样做是语法错误:

  • 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...

接下来,每个“ 子语句 ”可以包含wheregroup byhavingjoinlimit ,但不包括order by

如果您想使用order by ,则包含order by的“ substatement ”必须用大括号括起来。 (这意味着它们不再是可选的。)

现在,如果我们再次查看语法:

子语句 union all 子语句 union all 子语句 [ order by -clause] [ limit -clause]

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

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

我们之前已经提到过limit关键字也可以应用于单个“ 子语句 ”:

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

如果要对最后一个“ 语句”(而不是整个union语句)应用limit ,则必须用括号括起最后一个“ 语句”:

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

要将limit应用于最后一个“ 语句” 以及整个union语句,请使用:

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

它的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>

但请注意,将order by应用于“ substatement ”是没有意义的,因为docs明确声明 order by仅在保证( cf. )应用于整个union语句时才能工作:

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

在“ 子语句 ”中order by的唯一方法是将它与limit结合使用:

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

此外,如果你想将select intounion结合起来,那么还有更多的“陷阱”需要注意。 有关此问题 ,请参见问题32858


我认为如果您使用order by或limit或两者,则必须使用括号。 我尝试使用限制和顺序通过交替使用括号来处理查询,但是查询不起作用。 它只在添加括号后才起作用。


正确的是:

(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

试试()我想

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




union