order by field 와 union, 원하는 대로 정렬 하기

우선 내가 주로 애용하는 testbl 이라는 테이블이 있다.

mysql> select * from testbl;
+-----+------+------+--------+
| sid | ssid | sort | status |
+-----+------+------+--------+
|   1 | aa   |   23 | e      |
|   2 | aa   |   11 | e      |
|   3 | aa   |   33 | e      |
|   4 | aa   |   32 | e      |
|   5 | bb   |   23 | a      |
|   6 | bb   |   67 | c      |
|   7 | bb   |   34 | a      |
|   8 | bb   |   77 | d      |
|   9 | cc   |   11 | a      |
|  10 | cc   |   22 | a      |
|  11 | cc   |   32 | d      |
|  12 | cc   |   23 | c      |
+-----+------+------+--------+
12 rows in set (0.00 sec)

이 테이블로 몇가지를 해볼 참이다.
일단, 우리가 흔히 쓰는 order by 로 나온 결과를 정렬 할때, 일정한 순서가 아닌 임의로 정한 순서대로 보여주려면 아래와 같이 하면 된다.

mysql> select * from testbl where sid in (3,4,1)  order by field (sid, 3, 4, 1);
+-----+------+------+--------+
| sid | ssid | sort | status |
+-----+------+------+--------+
|   3 | aa   |   33 | e      |
|   4 | aa   |   32 | e      |
|   1 | aa   |   23 | e      |
+-----+------+------+--------+
3 rows in set (0.00 sec)

order by field ( 컬럼명, "순서1", "순서2", .... ) 와 같은 방법으로 원하는 순서를 직접 지정 할 수 있다.

하지만, 이렇게 원하는 순서대로 뽑아내려는 때는 보통... 기존에 있던건 그대로 보여주고 원하는 결과만 위로 보여줘야 할때가 대부분일 것이다.(필자는 그랬다.)
그럴땐 union을 쓰면 된다. 

mysql> (select * from testbl where sid in (3,4,1)  order by field (sid, 3, 4, 1))
union 
(select * from testbl where sid < 9 );
+-----+------+------+--------+
| sid | ssid | sort | status |
+-----+------+------+--------+
|   1 | aa   |   23 | e      |
|   3 | aa   |   33 | e      |
|   4 | aa   |   32 | e      |
|   2 | aa   |   11 | e      |
|   5 | bb   |   23 | a      |
|   6 | bb   |   67 | c      |
|   7 | bb   |   34 | a      |
|   8 | bb   |   77 | d      |
+-----+------+------+--------+
8 rows in set (0.00 sec)

위의 쿼리문에서 union으로 조금 더 뽑아 와봤다. 그런데... 정렬이 엉망이다. 위에서 order by를 썼는데도 먹혀 있지도 않다. 그 이유는 "UNION은 레코드 집합을 순서 없이 만들게 되기 때문에 최종 결과에서 레코드들이 어떻게 나타나야 하는지 순서에 관해 아무것도 암시 하지 않는다." 라는.. 한마디로 union으로 합치면 순서는 멋대로 정해진다 라는 거다.
그래서 이럴때는 뽑아온 대상에 대해서 정렬을 해줘야 한다.

mysql> (select * from testbl where sid in (3,4,1)) 
union 
(select * from testbl where sid < 9 ) 
order by field (sid, 3, 4, 1);
+-----+------+------+--------+
| sid | ssid | sort | status |
+-----+------+------+--------+
|   5 | bb   |   23 | a      |
|   2 | aa   |   11 | e      |
|   8 | bb   |   77 | d      |
|   7 | bb   |   34 | a      |
|   6 | bb   |   67 | c      |
|   3 | aa   |   33 | e      |
|   4 | aa   |   32 | e      |
|   1 | aa   |   23 | e      |
+-----+------+------+--------+
8 rows in set (0.00 sec)

위처럼 해놓으면 3, 4, 1로 정렬 된 것이 보일 것이다. 하지만, 여전히 부족하다. 나머지 순서들이 엉망인데다가, 필자는 3, 4, 1이 맨 위에서 뽑혀지길 원했기 때문이다.
이럴때는 아래와 같이 하면 된다.

mysql> (select * from testbl where sid in (3,4,1)) 
union 
(select * from testbl where sid < 9 ) 
order by field (sid, 1, 4, 3) desc, sid;
+-----+------+------+--------+
| sid | ssid | sort | status |
+-----+------+------+--------+
|   3 | aa   |   33 | e      |
|   4 | aa   |   32 | e      |
|   1 | aa   |   23 | e      |
|   2 | aa   |   11 | e      |
|   5 | bb   |   23 | a      |
|   6 | bb   |   67 | c      |
|   7 | bb   |   34 | a      |
|   8 | bb   |   77 | d      |
+-----+------+------+--------+
8 rows in set (0.00 sec)

유의할점은 desc로 뽑아주기 때문에 기존에 3,4,1로 해놓았던 순서를 1,4,3으로 해놓아야 원하는 결과물을 얻을 수 있다는 거다.

뭐.. 공지사항 같은거 처리할때 꾀 쓸만할꺼 같다..

//-----------------------------추가 사항 2012.10.24
필자가 실험한 mysql 5.1.41 에서는 위처럼 쿼리를 실행 해도 아무 상관 없었지만, 환경에 따라 field 뒤에 괄호와 빈칸을 하나 띄우냐 마냐에 따라서 오류가 발생 할 수 있다고 한다.
혹시 오류가 뜬다면, field와 괄호 사이를 띄우지 않은채 해보기 바란다.