SQL语言艺术 第11章 精于计谋:挽救响应时间 基于范围的聚合

     假设有表table_info的师徒包含table_name,row_count等字段,需要查询那些表的记录数小余100行?那些表的记录数在100到10000行之间?那些表的记录数在10000到1000000行之间以及大于1000000行的表又有多少?

     测试数据

create table table_info
(table_name  varchar(25),
row_count  int)
go

insert into
table_info
values
('A',15),
(
'B',63),
(
'C',112),
(
'D',163),
(
'E',8663),
(
'F',26548),
(
'G',99654),
(
'H',154687),
(
'I',659874),
(
'J',567000000),
(
'K',315000000),
(
'L',94000000),
(
'M',2600000),
(
'N',10005600)

  

     查询语句

select
    case
        when
row_count < 100 then 'under 100 rows'   
       
when row_count >= 100 and row_count < 10000 then '100 to 10000'
       
when row_count >= 10000 and row_count < 1000000 then '10000 to 1000000'
       
else 'over 1000000 rows'
   
end as row_range,
   
COUNT(*)    row_count
from table_info
group by 
    case
            when
row_count < 100 then 'under 100 rows'   
           
when row_count >= 100 and row_count < 10000 then '100 to 10000'
           
when row_count >= 10000 and row_count < 1000000 then '10000 to 1000000'
           
else 'over 1000000 rows'
   
end

 

     结果

row_range             row_count

-----------------   -----------

100 to 10000          3

10000 to 1000000   4

over 1000000 rows  5

under 100 rows       2

(4 row(s) affected)

      该语句可以查处结果,但有一个问题,在聚合数据之前,group by会执行排序,由于已经给每个聚合赋予了一个标签,查询结果会(默认地)按照字母序列进行排序。

从使用角度来看,应该显示记录数最少的表的数量,依次类推……

     使用以下方法添加人造排序键。

select row_range,row_count
    from
   
(select
    case
        when
row_count < 100 then 1
        when row_count >= 100 and row_count < 10000 then 2
        when row_count >= 10000 and row_count < 1000000 then 3
        else 4
    end as sortkey,
   
case
        when
row_count < 100 then 'under 100 rows'   
       
when row_count >= 100 and row_count < 10000 then '100 to 10000'
       
when row_count >= 10000 and row_count < 1000000 then '10000 to 1000000'
       
else 'over 1000000 rows'
   
end as row_range,
   
COUNT(*)    row_count
from table_info
group by 
case
        when
row_count < 100 then 'under 100 rows'   
       
when row_count >= 100 and row_count < 10000 then '100 to 10000'
       
when row_count >= 10000 and row_count < 1000000 then '10000 to 1000000'
       
else 'over 1000000 rows'
   
end,
   
case
        when
row_count < 100 then 1
        when row_count >= 100 and row_count < 10000 then 2
        when row_count >= 10000 and row_count < 1000000 then 3
        else 4
    end) as a
    order by sortkey

     结果

row_range               row_count

-----------------     -----------

under 100 rows        2

100 to 10000           3

10000 to 1000000    4

over 1000000 rows   5

(4 row(s) affected)

posted on 2011-04-22 10:39  天空一角  阅读(264)  评论(0编辑  收藏  举报