问题:对buymoney进行按区间统计,不同的人数和落在该区间的金额
CTEATE TABLE order_information
(Number SMALLINT UNSIGNED,
Buydate DATE,
buymoney MEDIUMINT UNSIGNED, /*范围2e24-1 16777215 */
qixian SMALLINT UNSIGNED);
INSERT INTO TABLE order_information
(Number,Buydate,buymoney,qixian)
VALUES(,,,);
满足初级筛选条件的数据表:
1.使用ELT()和INTERVAL()作为区间分组,区间按照(0,10000]划分,目前只能找到限定固定区间的方法(或者使用 CASE WHEN THEN)
mysql>SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej' mysql>SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
mysql>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10); +---------------------------------------------------------+ | INTERVAL(6,1,2,3,4,5,6,7,8,9,10) | +---------------------------------------------------------+ | 6 | +---------------------------------------------------------+ 1 row in set (0.00 sec)INTERVAL(N,N1,N2,N3,N4...)
N<N1->0
2.如果按照[0,10000)的区间划分,可以利用 FLOOR(buymoney/10000) /*buymoney 为 int类型 FLOOR()取整数部分 */
显示 0~1 SELECT CONVERT(varchar,B.buymoney)+'~'+CONVERT(varchar,B.buymoney+1)