求最小值及其数目
表格如下：
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)
