MySQL找到具有最多元素的范围 [英] Mysql find the range with most elements

问题描述

我有一个简单的mysql表，其中包含name和age列.我需要找到包含最多记录数的年龄范围(例如长度为5).请注意，范围可以是任何东西(例如1到5年或2到6年).我已经在 http://sqlfiddle.com/#!2/a65265上为它创建了一个sqlfiddle/1

I have a simple mysql table with name and age columns. I need to find the age range (say with length 5) which contains the most number of records. Please note that the range can be from anything to anything (like 1 to 5 years or 2 to 6 years). I have created a sqlfiddle for the same at http://sqlfiddle.com/#!2/a65265/1

我尝试使用DIV并在论坛中进行了搜索，但我能获得的最接近的是5-10、10-15等年龄段的预定义范围.我需要针对所有可能年龄段的通用解决方案.

I have tried using DIV and searched through the forums, but the closest i can get is predefined ranges like age 5-10, 10-15 etc. I need a more generic solution for all possible age ranges.

推荐答案

select      5 *  floor((t.age-o.offset)/5)      + o.offset      as from_age 
           ,5 * (floor((t.age-o.offset)/5) + 1) + o.offset - 1  as to_age
           ,count(*)                                            as cnt


from                    test as t 

            cross join (           select 0  as offset 
                        union all  select 1 
                        union all  select 2 
                        union all  select 3 
                        union all  select 4
                        ) as o

group by    o.offset 
           ,floor((t.age-o.offset)/5)

order by    cnt desc

limit       1

---

基本思想-
每行重复5次，偏移量在0到4之间.
每次偏移都会导致元素的不同分布，如下图所示:

---

The basic idea -
Each row is being duplicated 5 times, with offset in the range of 0 to 4.
Each offset is causing a different distribution of the elements as described in the following diagrams:

x:                0  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19
                  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
                  -------------  -------------  -------------  -------------
floor((x-0)/5):   0              1              2              3 


x:                0  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19
                  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
                  -  -------------  -------------  -------------  -------------
floor((x-1)/5):      0              1              2              3 


x:                0  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19
                  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
                  ----  -------------  -------------  -------------  -------------
floor((x-2)/5):         0              1              2              3 


x:                0  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19
                  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
                  -------  -------------  -------------  -------------  -------------
floor((x-3)/5):            0              1              2              3 


x:                0  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19
                  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
                  ----------  -------------  -------------  -------------  -------------
floor((x-4)/5):               0              1              2              3 

这篇关于MySQL找到具有最多元素的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！