sql group by few range

waizai

各位高手，我有一个难题。。。。
我想弄一个SQL Query Group By Few Range....

比如说 ： 1 ~ 100
我要Group by 0 ~ 10 , 11 ~ 20, 21 ~ 30 ..........
0 ~ 10 = ***
11 ~ 20= ***
21~30=***
31~40=***
41~50=***
51~60=***
61~70=***
71~80=***
81~90=***
91~100=***

总共会有10个Result.....

Example Group By Query(one range) :-
SELECT  GroupLevel AS DLevel, COUNT(DataNum) AS TTotal
FROM DataInfo
GROUP BY GroupLevel
ORDER BY GroupLevel

我想了好久，只有一个办法。。。直接做10个 "select query" ....
感觉好奇怪
你们还有什么建议/意见吗？

tanhy

写１０个query太不flexible 了
不如写一个stored proc,把那些range fields insert 去temp table
然后才select 出来？

shinchanawang

原帖由 waizai 于 22-2-2008 05:58 PM 发表
各位高手，我有一个难题。。。。
我想弄一个SQL Query Group By Few Range....

比如说 ： 1 ~ 100
我要Group by 0 ~ 10 , 11 ~ 20, 21 ~ 30 ..........
0 ~ 10 = ***
11 ~ 20= ***
21~30=***
31~40=***
41 ...

TRY THIS EXAMPLE
*************************

SELECT
    CASE
      WHEN RANGE BETWEEN 0 AND 10
        THEN '0 - 10'
      WHEN RANGE BETWEEN 11 AND 20
        THEN '11 - 20'
      WHEN RANGE BETWEEN 21 AND 30
        THEN '21 - 30'
      WHEN RANGE BETWEEN 31 AND 40
        THEN '31 - 40'
      WHEN RANGE BETWEEN 41 AND 50
        THEN '41 - 50'
      WHEN RANGE BETWEEN 51 AND 60
        THEN '51 - 60'
      WHEN RANGE BETWEEN 61 AND 70
        THEN '61 - 70'
      WHEN RANGE BETWEEN 71 AND 80
        THEN '71 - 80'
      WHEN RANGE BETWEEN 81 AND 90
        THEN '81 - 90'
      WHEN RANGE BETWEEN 91 AND 100
        THEN '91 - 100'
      ELSE 'UNKNOWN'
    END
      AS RANGE,
    COUNT(*) AS TOTAL
  FROM ABC
  GROUP BY
    CASE
      WHEN RANGE BETWEEN 0 AND 10
        THEN '0 - 10'
      WHEN RANGE BETWEEN 11 AND 20
        THEN '11 - 20'
      WHEN RANGE BETWEEN 21 AND 30
        THEN '21 - 30'
      WHEN RANGE BETWEEN 31 AND 40
        THEN '31 - 40'
      WHEN RANGE BETWEEN 41 AND 50
        THEN '41 - 50'
      WHEN RANGE BETWEEN 51 AND 60
        THEN '51 - 60'
      WHEN RANGE BETWEEN 61 AND 70
        THEN '61 - 70'
      WHEN RANGE BETWEEN 71 AND 80
        THEN '71 - 80'
      WHEN RANGE BETWEEN 81 AND 90
        THEN '81 - 90'
      WHEN RANGE BETWEEN 91 AND 100
        THEN '91 - 100'
      ELSE 'UNKNOWN'
    END
  ORDER BY MIN(RANGE)

tanhy

尽量不要hard coding
改次你很难expand 你的functionality

tanhy

打个比方，如果你的data range 是０　－　１０００，　一个interval 是１０
难道你要hard code 100 次？

waizai

谢谢你的coding, 我会试试看。。。。。

waizai

我有想过用stored proc, 等下也会试试看。。。。
谢谢~

SeaBiscuit

介绍你另外一个方法也是用 CASE 的,比较简单。

SELECT
COUNT(CASE WHEN RANGE>=0 AND RANGE <= 10 THEN 1 END) AS 0_10_TOTAL,
.
.
.
COUNT(CASE WHEN RANGE>=91 AND RANGE <= 100 THEN 1 END) AS 91_100_TOTAL
FROM XX_TABLE

记得 CASE WHEN 里面不要有 ELSE 0 存在，否则 COUNT　就会错，变成 COUNT (*) 　去了。

kfchai1982

更容易的方法.
SELECT FLOOR((my_table.index - 1) / 10) AS group_level, count(*)  AS group_count
FROM my_table
GROUP BY group_level
ORDER BY group_level

SeaBiscuit

原帖由 kfchai1982 于 17-3-2008 03:09 PM 发表
更容易的方法.
SELECT FLOOR((my_table.index - 1) / 10) AS group_level, count(*)  AS group_count
FROM my_table
GROUP BY group_level
ORDER BY group_level

又学到东西。