UNION 및 ORDER BY의 잘못된 사용?

UNION 및 ORDER BY의 잘못된 사용?

---

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

나에게 오류를 줘

#1221 - Incorrect usage of UNION and 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
)

하지만 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
)

즉, MySQL이 외부 절에서 내부 정렬을 유지하는 것이 필수는 아니지만 해당 절 을 계산하기 위해 행을 정렬해야하므로 아마도 그렇게 할 것 입니다LIMIT .

---

설명:

유사한 사용 사례에서 "문제"를 방지하려면 이것이 어떻게 작동하는지 이해하는 것이 중요합니다. 주의 union의 구문은 다소 "특별한"입니다 :

_하위 union all _명령문 union all _하위order by 명령문 _{하위 명령문} [-clause] [limit-clause]

여기서 " 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);
  

그러나 첫 번째 "둘러싸는 경우, 하위 문 교정기를"당신은 해야 다른 "모든 서라운드 하위 문은 "중괄호 S :

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

다음, 각각의 " 하위 문 "포함 할 수 있습니다 where, group by, having, join, limit,하지만 order by.

를 사용하려면 을 포함 order by하는 " substatement "를 order by중괄호로 묶어야합니다. (이는 더 이상 선택 사항이 아님을 의미합니다.)

이제 구문을 다시 살펴보면 :

_하위 union all _명령문 union all _하위order by 명령문 _{하위 명령문} [-clause] [limit-clause]

전체 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>
  

이전에 limit키워드가 개별 " 하위 문 " 에도 적용될 수 있다고 언급했습니다 .

• 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 limit to the last "substatement" (as opposed to the entire union statement), you must surround the last "substatement" with braces:

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

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

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

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

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

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.

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

---

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

---

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

---

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.

ReferenceURL : https://stackoverflow.com/questions/6732661/incorrect-usage-of-union-and-order-by