求分组求中间值的SQL

Question

表ATYPE    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
要求按TYPE NAME分组查出PRICE对应的中值.中值定义为,如果分组的记录条数奇数时,就是从高到低排序最中间的那条记录,如果分组的记录条数为偶数时,就取中间两条记录的PRICE的平均值
以上表需生成结果为
TYPE NAME PRICE
2     A    4.55    (注:为5.4+3.7的平均值)
2     B    6.7
1     B    4.7
大家看看有什么好的想法没.先谢了

Answer 1

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (TYPE INT,NAME VARCHAR(1),PRICE NUMERIC(2,1))
INSERT INTO @T
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--SQL查询如下:;WITH Liang AS
(
    SELECT
        TYPE,NAME,PRICE,
        asc_rnk=ROW_NUMBER() OVER(PARTITION BY TYPE,NAME ORDER BY PRICE),
        desc_rnk=ROW_NUMBER() OVER(PARTITION BY TYPE,NAME ORDER BY PRICE DESC)
    FROM @T
)
SELECT TYPE,NAME,AVG(PRICE) AS PRICE
FROM Liang
WHERE asc_rnk IN(desc_rnk,desc_rnk+1,desc_rnk-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 行受影响)
*/

Answer 2

我很郁闷，中午没有去吃饭，搞这个问题，结果又一次被小梁给抢先了
适当给点点分哈 吃饭去了
set nocount on 
declare @t table (Type int ,Name char,Price numeric(2,1))
insert into @t 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 
/*
2    A    4.55    (注:为5.4+3.7的平均值) 
2    B    6.7 
1    B    4.7 
*/select Type,
Name,
sum(case 
when mpx%2=1 and px=mpx/2+1 then Price 
when mpx%2=0 and (px=mpx/2 or px=mpx/2+1) then Price/2.0 
else 0
end ) as AvgPrice 
from 
(
select *,
px=(select count(1)+1 from @t where Type=t.Type and Name=t.Name and Price>t.Price) ,
mpx=(select count(1) from @t where Type=t.Type and Name=t.Name )
from @t t 
) x
group by  Type,Name
order by Type,Name

Answer 3

上面是SQLSERVER2000的
这个是结果/*
Type Name AvgPrice
1 B 4.700000
2 A 4.550000
2 B 6.700000
*/

Answer 4

谢谢大家.
我开始的思路是和GDC_ZhaoYZ0304360一样的.
想用一下WITH AS 加上OVER的用法,所以上来发帖了,果然得到想要结果..PS:GDC_ZhaoYZ0304360,你姓赵?

Answer 5

这个有误
当中间有几个相同的数值时，px不是排序的
如
col px
5.3 1
4.3 2
4.3 2引用语句的结果为8.6