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

结果