日期:2014-05-18  浏览次数:20391 次

求最小值及其数目
表格如下:
id   num
11     3
12     2
13     3
14     5
15     7
16     2
21     3
22     21
23     4
24     24
25     7
31     1
32     3
33     2  
..     ..

希望得到如下结果集:

se       min     num
1         2           2
2         3           1
3         1           1

se是id的头一个字母,min   是按se排列的num的最小值,num是最小值的数目。

------解决方案--------------------

create table T(id int, num int)
insert T select 11, 3
union all select 12, 2
union all select 13, 3
union all select 14, 5
union all select 15, 7
union all select 16, 2

union all select 21, 3
union all select 22, 21
union all select 23, 4
union all select 24, 24
union all select 25, 7

union all select 31, 1
union all select 32, 3
union all select 33, 2

select se=substring(rtrim(id), 1, 1), minValue=min(num),
num=( select count(*) from T where substring(rtrim(id), 1, 1)=substring(rtrim(A.id), 1, 1) and num=min(A.num) )
from T as A
group by substring(rtrim(id), 1, 1)

--result
se minValue num
---- ----------- -----------
1 2 2
2 3 1
3 1 1

(3 row(s) affected)