求次大/次小的记录
表格如下：
date     num
07-1     1
07-1     3
07-1     4
07-1     2
07-1     2
07-2     1
07-2     1
07-2     22
07-2     11
07-2     66
...       ...

想得到如下结果集
date     总数   次小值   数目   次大值   数目
07-1     5           2             2         3             1
07-2     5           11           1         22           1
..         ..         ..           ..         ..         ..

数目指次大/次小值的个数。


------解决方案--------------------
--這個將就用一下吧,
select [date],
总数=(select count(*) from T a where [date]=T.[date]),
次小值=min(T.num),
次小值数目=(select count(*) from T b where [date]=T.[date] and num=min(T.num)),
次大值=max(T.num),
次大值数目=(select count(*) from T c where [date]=T.[date] and num=max(T.num))
from T
where not exists
(select 1 from (select [date],min(num) as mi ,max(num) as ma from T group by [date]) e
where [date]=T.[date] and (mi=T.num or ma=T.num))
group by [date]

------解决方案--------------------
create table T([date] varchar(10), num int)
insert T select '07-1 ', 1
union all select '07-1 ', 3
union all select '07-1 ', 4
union all select '07-1 ', 2
union all select '07-1 ', 2
union all select '07-2 ', 1
union all select '07-2 ', 1
union all select '07-2 ', 22
union all select '07-2 ', 11
union all select '07-2 ', 66

select [Date], 总数=count(*),
次小值=(select min(num) from T where [date]=A.[date] and num <> min(A.num)),
数目=( select count(*) from T where [date]=A.[date] and num=
(select min(num) from T where [date]=A.[date] and num <> min(A.num))
),
次大值=(select max(num) from T where [date]=A.[date] and num <> max(A.num)),
数目=( select count(*) from T where [date]=A.[date] and num=
(select max(num) from T where [date]=A.[date] and num <> max(A.num))
)
from T as A
group by [date]

--result
Date 总数 次小值 数目 次大值 数目
---------- ----------- ----------- ----------- ----------- -----------
07-1 5 2 2 3 1
07-2 5 11 1 22 1

(2 row(s) affected)