求SQL区间统计查询语句~~~~~~
例如我有一张表AA,数据如下:
AA:
a b
a1 1
a2 3
a3 4
a4 7
要查询 0-10 中,每个5区间的字段条数,即查询结果为:
0-5 有3条记录;
5-10有1条记录;
查询语句怎么写?
(ORACLE数据库)
[解决办法]
俺不懂ORACLE,用MS-SQL举个例子,应该大同小异吧
create table AA (a varchar(2), b int)
insert into AA
select 'a1 ', 1 union all
select 'a2 ', 3 union all
select 'a3 ', 4 union all
select 'a4 ', 7
go
declare @t table(r varchar(20), c int)
declare @i int, @maxb int
select @maxb=max(b) from AA
set @i=0
while @i <@maxb
begin
insert into @t select ltrim(str(@i,2))+ '- '+ltrim(str(@i+5,2)), count(*)
from AA where b> =@i and b <@i+5
set @i=@i+5
end
select * from @t
go
drop table AA
go
[解决办法]
create table AA (a varchar(2), b int)
insert into AA
select 'a1 ', 1 union all
select 'a2 ', 3 union all
select 'a3 ', 4 union all
select 'a4 ', 7
go
declare @i int, @maxb int, @c int
select @maxb=max(b) from AA
set @i=0
while @i <@maxb
begin
select @c=count(*) from AA where b> =@i and b <@i+5
print ltrim(str(@i,2))+ '- '+ltrim(str(@i+5,2))+ '有 '+ltrim(str(@c,2))+ '条记录 '
set @i=@i+5
end
go
drop table AA
go
-----------------------------------------
结果:
(所影响的行数为 4 行)
0-5有3条记录
5-10有1条记录