表格如下:
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
.. .. .. .. .. ..数目指次大/次小值的个数。
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]
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', 66select [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)
是的是的。
求第三大/小,第四大/小,第五大/小
------
暈, 後面的不好做啊