原文:Sql示例说明如何分组后求中间值--【叶子】
这里所谓的分组后求中间值是个什么概念呢？
我举个例子来说明一下：
假设我们现在有下面这样一个表：
type name price
----------- ---- ------------
2 A 5.4
2 A 3.7
2 B 4.3
1 B 4.7
2 B 6.7
2 B 9.8
3 a 3.0
3 a 4.0
3 a 10.0
3 a 20.0
我们要得到什么样的效果呢？
按type和name进行分组后求中间值：
是不是就是这样呢：
select [type],name,AVG(price) from @table
group by type,name
/*
type name
----------- ---- ---------------------------------------
2 A 4.550000
3 a 9.250000
1 B 4.700000
2 B 6.933333
*/
不是的，我所说的中间值和平均数还不太一样，平均数就是均值，但是中间值是说分组如果有奇数个就是中间那个，如果有偶数个就是中间两个值的均值。
例如上面数据中3，a分组后有3，4，10，20四个数，那中间值就是4和10个均值。
例如上面按2，B分组后有4.3 和6.7 及9.8三个值，那中间值就是6.7
我们如何能达到这样效果呢，写个简单的例子：
declare @table table ([type] int,name varchar(1),price numeric(3,1))
insert into @table
select 2,'A',5.4 union all
select 2,'A',3.7 union all
select 2,'B',4.3 union all
select 1,'B',4.7 union all
select 2,'B',6.7 union all
select 2,'B',9.8 union all
select 3,'a',3 union all
select 3,'a',4 union all
select 3,'a',10 union all
select 3,'a',20
--sql查询如下:
;with maco as
(
select
[type],name,price,
m_asc=row_number() over(partition by type,name order by price),
m_desc=row_number() over(partition by type,name order by price desc)
from @table
)
--select * from liang
select [type],name,avg(price) as price
from maco
where m_asc in(m_desc,m_desc+1,m_desc-1)
group by type,name
order by type,name
/*
type name price
----------- ---- -----------
1 B 4.700000
2 A 4.550000
2 B 6.700000
3 a 7.000000
*/