The application of MySQL interval grouping interval,elt "Memo"

Thank you for sharing:http://blog.itpub.net/13379967/viewspace-715701/  sometimes it is often necessary to group intervals in statistical requirements  mysql can use ELT functions to achieve such requirements  mysql> SELECT * FROM k1;+------+------+| ID   | YB   |+------+------+|    1 |  100 | |   &NBSP;2 |   11 | |    3 |    5 | |    4 |  501 | |    5 | 1501 | |    6 |   &NBSP;1 |+------+------+  now to be counted, less than 100, 100~500, 500~1000, more than 1000, the ID number of each interval   use interval to underline 4 intervals   Re-use the ELT function to return 4 intervals to a column name  mysql> select ELT (interval (d.yb,0, +, +), ' less100 ', ' 100to500 ', ' 500to1000 ', ' more1000 ') as Yb_level, Count (d.id) as cnt    -> from K1 d       - > GROUP BY ELT (interval (d.yb, 0, +, +), ' less100 ', ' 100to500 ', ' 500to1000 ', ' more1000k '), +-----------+-----+ | Yb_level  | CNT |+-----------+-----+| 100to500  |   1 | | 500to1000 |   1 | | less100   |   3 | | more1000  |   1 |+-----------+-----+4 rows in Set (0.00 sec)    If you need to sort by from small to large, you can sort each document interval by a few first characters when the column name is defined  mysql> Select ELT (Interval (d.yb,0 , +, +, +), ' 1/less100 ', ' 2/100to500 ', ' 3/500to1000 ', ' 4/more1000 ') as Yb_level, Count (d.id) as cnt   &NB Sp;-> from K1 D       -> Group by ELT (interval (d.yb, 0, 2/10, +), ' 1/less100 ', ' 0to500 ', ' 3/500to1000 ', ' 4/more1000k '), +-------------+-----+| Yb_level    | CNT |+-------------+-----+| 1/less100   |   3 | | 2/100to500  |   1 | | 3/500to1000 |   1 | | 4/more1000  |   1 |+-------------+-----+4 rows in Set (0.00 sec)    with ELT function format:  elt (N,STR1,STR2,STR3,...) If n= 1, return str1, if n= 2, return str2, and so on. If n is less than 1 or greater than the number of arguments, NULL is returned. ELT () is a field () inverse operation. Mysql> Select ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo ');        -> ' ej ' mysql> select ELT (4, ' EJ ', ' Heja ', ' Hej ', ' foo ');        -> ' foo '          interval functionsFormat:          interval () return the index of the argument that was less than the first argument (smaller than one of the following parameters, returning the previous position number of this parameter)INTERVAL (N,n1,n2,n3,...) Returns 0 if n < N1, 1 if n < N2 and so on Or-1 if n is NULL. All arguments is treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This was because a binary search is used (very fast). Mysql> SELECT INTERVAL (23, 1, 15, 17, 30, 44, 200);        (23 is less than 30, the position of 30 is 4, so return 3), 3mysql> SELECT INTERVAL (10, 1, 10, 100, 1000);        -2mysql> SELECT INTERVAL (22, 23, 30, 44, 200); 0

The application of MySQL interval grouping interval,elt "Memo"