mysql> DESC TB_Table;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| ymd | varchar(15) | | PRI | | |
| ip | varchar(255) | | PRI | | |
| octets | varchar(255) | | | 0 | |
| gbytes | varchar(255) | | | 0 | |
| mbitps | varchar(255) | | | 0 | |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 문자열 정렬때문에 자리수까지 반영안됨
mysql> select * from TB_TRAFFIC_BACKUP where ymd='20110923' order by gbytes DESC LIMIT 10;
+----------+-----------------+-------------+--------+--------+
| ymd | ip | octets | gbytes | mbitps |
+----------+-----------------+-------------+--------+--------+
| 20110923 | XXX.XXX.6.21 | 8993603700 | 9 | 17 |
| 20110923 | XXX.XXX.140.105 | 9433388400 | 9 | 82 |
| 20110923 | XXX.XXX.15.133 | 8854516800 | 9 | 109 |
| 20110923 | XXX.XXX.161.239 | 7927218300 | 8 | 91 |
| 20110923 | XXX.XXX.46.187 | 8293270200 | 8 | 65 |
| 20110923 | XXX.XXX.14.89 | 7767715800 | 8 | 65 |
| 20110923 | XXX.XXX.161.48 | 6462369600 | 7 | 31 |
| 20110923 | XXX.XXX.15.94 | 64924760100 | 61 | 104 |
| 20110923 | XXX.XXX.157.144 | 5386317900 | 6 | 66 |
| 20110923 | XXX.XXX.7.221 | 5514294300 | 6 | 60 |
+----------+-----------------+-------------+--------+--------+
10 rows in set (1.04 sec)
# +0 으로 숫자연산을 하게 되면 문자열을 강제로 숫자로 바꿔서 처리
mysql> select * from TB_TRAFFIC_BACKUP where ymd='20110923' order by gbytes+0 DESC LIMIT 10;
+----------+-----------------+-------------+--------+--------+
| ymd | ip | octets | gbytes | mbitps |
+----------+-----------------+-------------+--------+--------+
| 20110923 | XXX.XXX.15.94 | 64924760100 | 61 | 104 |
| 20110923 | XXX.XXX.6.55 | 32159743800 | 30 | 25 |
| 20110923 | XXX.XXX.17.16 | 29648243400 | 28 | 69 |
| 20110923 | XXX.XXX.163.121 | 25003029900 | 24 | 84 |
| 20110923 | XXX.XXX.213.94 | 17743800000 | 17 | 41 |
| 20110923 | XXX.XXX.46.154 | 17481720300 | 17 | 100 |
| 20110923 | XXX.XXX.46.152 | 15550536300 | 15 | 162 |
| 20110923 | XXX.XXX.6.21 | 8993603700 | 9 | 17 |
| 20110923 | XXX.XXX.140.105 | 9433388400 | 9 | 82 |
| 20110923 | XXX.XXX.15.133 | 8854516800 | 9 | 109 |
+----------+-----------------+-------------+--------+--------+
10 rows in set (0.96 sec)
