笑话/MySql group by 根据区间分组统计
mysql中可以利用elt函数来实现此类需求 mysql> select * from k1; +------+------+ | id | yb | +------+------+ | 1 | 100 | | 2 | 11 | | 3 | 5 | | 4 | 501 | | 5 | 1501 | | 6 | 1 | +------+------+ 现在要进行统计S3SXv4350h1x,rtnXB29g0BA5小于100的K224H7jI,ZPU3Mpl6100~500的ybPYFfd0k2X,85Ek5ceB44Y9500~1000的38CY9K,wtJ1691000以上的26xkl,MB5Fn4这各个区间的id数 利用interval划出4个区间9ILODs,EeZ6PXAQPPtwU再利用elt函数将4个区间分别返回一个列名2M2VrjGbosC:tdH87kD0G84CQG mysql> select elt(interval(d.ybJkiF15zZV0wf,BO37E6L7t01N86qs,Jb597vOeQ64cp9 10047O7MzP43s0y5,BuF5060bs 5003k92oy,jQcq9P7 1000)MkiDU645,J9zSUVC5ObB 'less100'cts6u81FMYF8,D16kv5x9qQQ116 '100to500'iTEMMOrF,Bs917BoLe '500to1000'm5rW6N,DH9CH9Kt9 'more1000') as yb_levelhg6Y97,q6A3BMJD count(d.id) as cnt from k1 d group by elt(interval(d.ybSH8D3xJctJX,2eBOLxL795o46 0tsVPJomid52Z4,pW3z6q0 1002yk0lm3m,4L4Tiv90aG7 500lFg8u8,r98QO 1000)Ro95sU4SV,azu348z 'less100'4IsNX436,WCS9WXA1ba4 '100to500'Dk5bN3,82r23zxU39223 '500to1000'VCG8L3eVQ0,6Pb59hltsCc 'more1000K'); +-----------+-----+ | yb_level | cnt | +-----------+-----+ | 100to500 | 1 | | 500to1000 | 1 | | less100 | 3 | | more1000 | 1 | +-----------+-----+ 4 rows in set (0.00 sec) 如果需要按从小到大排序的话 可以在列名定义时稍加一个首字符 对各档区间进行排序 mysql>select elt(interval(d.ybcaekV7TK80bCC,TNReZWbtQx0z0LqpVJmjZ,VOzqePH7RC3et3 100Yc134FFuVn,ofzfUDS58sxZv9 5001xi7EZ5JNGCrgj,83JFLu91KO 1000)83aMBJj3swh,AboTj8SJfuKL7j '1/less100'qGePB1083tRe8,1Ro5z '2/100to500'gZn9y,216j91leVq1l '3/500to1000'R4tzS9B,1KAMB8Kq7iD '4/more1000') as yb_level0xKbI41tv8,ft5mOl684 count(d.id) as cnt from k1 d group by elt(interval(d.yb4t2os63tW,eC2NReQ 0F53zt,706Lf 1006U4P0c15,AyVLNDZstZaFQD 500eA6FsPzeKoA0P,IF8U0CZU 1000)JW106J,K1Dx8xlU25k '1/less100'A3pbLq1C45Dab8,7eqkts5 '2/100to500'7tEXL,l3DXWQT19xU '3/500to1000'S0RT22TFB,InK1YD '4/more1000K'); +-------------+-----+ | yb_level | cnt | +-------------+-----+ | 1/less100 | 3 | | 2/100to500 | 1 | | 3/500to1000 | 1 | | 4/more1000 | 1 | +-------------+-----+ 4 rows in set (0.00 sec) 附elt函数格式yj16dLbE2:9QJOBE5xbx6u5u ELT(N3I7wJL9iJfEQo8,2295ZStm5JCstr1sw6wu3m87Q,BcP0r4str2b3Vu8Q37F,75GG1ctPWN1str302D9b,EaD3612PAg...) 如果N= 1u1Ddkj,8kgmUX返回str18zxZOrXF0L,D31d11Hy如果N= 27r9bG2tNwTF5,G2XS9n3A41返回str200z14dZq5G7k,o0P00S等等8W318c1Rf。8jQil0N如果N小于1或大于参数个数8DHD3uMnY,28ZHRP5返回NULLVosUSGOf4。584DxIl69WELT()是FIELD()反运算IC7p5u1sN12。Fjg9h mysql> select ELT(1rcIRy4cVfD00tO,mRLW73sz51 'ej'lu6qq4kj5g7,634JP 'Heja'3iA3T,Qd6o8Jz66fz85 'hej'PMj5640ewEdf,4w98Bt9hLylydK 'foo'); -> 'ej' mysql> select ELT(4xxqi4lxoPYb,hWxr6oU8v4pz 'ej'jQKg3w,48Rfk9 'Heja'BLgF5Slh5,blmD45nFXg8t5e 'hej'44uE5,1WaXExg 'foo'); -> 'foo' interval函数格式RW86rChv2SC:Oa1dOqrNl INTERVAL() Return the index of the argument that is less than the first argument(小于后面的某个参数UN59c886qJ8Qn,5W8moGw就返回这个参数的前一个位置数字) INTERVAL(NjY3ZY2,DW1N99J2h3wN1j8b403Jv1,2X0V8KyIdoli1UN2b9s5OR1JR5,L0b2u488XN3LpFS2taB,R3i02W3T...) Returns 0 if N < N1oZ2j3k77U,1M0ZKnxh2M 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast). mysql> SELECT INTERVAL(237H4i0,FoHPbjLs1tLnu 1Buz409f09Z,Ez5508 15wm2f9g11Ka1,H088GK533cm2 17vdYOq55v71V,Tq0VjRm 306ZfWFm50U8Vi,PULd4jSD19 44EA3Tl37cf1n,45GIb3O8qaerm 200); (23小于30jpe90J0U,WuFVz6xU23YY7A30的位置是4542SxFqt,6uK8thJ于是返回3) -> 3 mysql> SELECT INTERVAL(10T8bS86,GtWa764zb61Pf0 1cge6OCuMfFl,L8JVmrw43 106StSStChx4O13o,0f9Hgd4iobtv3L 100K8qSVzF8X,2Y61vM91F4e 1000); -> 2 mysql> SELECT INTERVAL(225BXdz266D0k0,6D38P64JXkR6 23e7QE2tzimkb,og34s0vqAys 30vc53rM3eV,N60022 4481gzkV9VDJVQT,64UV541Z7uF 200); -> 0