有一张表test如下：

create table test(id int identity(1,1) primary key,num int )

 

插入数据：

insert into test( num) values (1);insert into test( num) values (2);insert into test( num) values (8);insert into test( num) values (15);insert into test( num) values (12);insert into test( num) values (13);insert into test( num) values (14);insert into test( num) values (16);insert into test( num) values (17);insert into test( num) values (5);insert into test( num) values (6);insert into test( num) values (7);insert into test( num) values (16);insert into test( num) values (18);insert into test( num) values (9);insert into test( num) values (10);insert into test( num) values (11);insert into test( num) values (12);insert into test( num) values (19);insert into test( num) values (20);insert into test( num) values (3);insert into test( num) values (4);insert into test( num) values (19);insert into test( num) values (20);insert into test( num) values (17);insert into test( num) values (18);

 

问题：请用一条sql语句查询 统计出num在 1~6,  10~17,  19~20 这三个范围内的个数分别是多少？

 

解法如下：

select COUNT (case when num between 1 and 6 then 1 end ) as [1-6],COUNT( case when num between 10 and 17 then 2 end ) as [10-17],COUNT (case when num between 19 and 20 then 3 end ) as [19-20]from test

 

如果问题是问1~5, 6~10,11~15这样成倍数(有规律)的话，则可以这样写：

select COUNT(*) from test group by num/5