SQL2000中值与平均值的查询问题

[huangxuecheng10]

假设有表A，字段a1,a2,a3
数据如下：
a1 a2 a3
a 3 5
a 4 6
b 9 3
b 8 6
b 7 9
最后查询所得结果按字段a1的不同，分别取出a2的中值与a3的平均值
即：
a1 a2 a3
a 3.5 5.5
b 8 6

求各位大大指点~~~

[王向飞]

--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1([a1] varchar(1),[a2] int,[a3] int)
insert #1
select 'a',3,5 union all
select 'a',4,6 union all
select 'b',9,3 union all
select 'b',8,6 union all
select 'b',7,9

select a1,cast(AVG(a2*1.0) as numeric(12,1)),cast(AVG(a3*1.0) as numeric(12,1))
from #1
group by a1

[「已注销」]

SELECT  a1,cast(SUM(a2)*1.0/COUNT(a2) AS DECIMAL(6,1))[a2]
       ,cast(SUM(a3)*1.0/COUNT(a3) AS DECIMAL(6,1))[a3]
FROM #tb t
GROUP BY t.a1

a1         a2                                     a3
---------- --------------------------------------- ---------------------------------------
a          3.5                                     5.5
b          8.0                                     6.0

(2 row(s) affected)

[huangxuecheng10]

LS的大大，这样子取的a2字段是平均值吧？我给的数据正好是中值=平均值了...实际要求的是a2的中值...

[王向飞]

那中值怎么算？给点特殊的测试数据

[--小F--]

中值问题 参考SQL 2005技术内幕之T-SQL查询

[huangxuecheng10]

我知道中值在2005那书里有，不过现在需要将中值和平均值在一条语句中同时求出来

[huangxuecheng10]

[Quote=引用 4 楼 wxf163 的回复:]
那中值怎么算？给点特殊的测试数据
[/Quote]
就把数据改下吧，如下：
a1 a2 a3
a 3 5
a 7 6
a 4 3
a 9 4
b 9 3
b 7 6
b 8 9
b 6 12
b 5 15

得出来的数据应该是：
a1 a2 a3
a 5.5 4.5
b 7 9

[Limpire]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(a1 varchar(8), a2 float, a3 float)
insert into #
select 'a', 3, 5 union all
select 'a', 7, 6 union all
select 'a', 4, 3 union all
select 'a', 9, 4 union all
select 'b', 9, 3 union all
select 'b', 7, 6 union all
select 'b', 8, 9 union all
select 'b', 6, 12 union all
select 'b', 5, 15

-- 临时表
select id = identity(int,1,1), * into #temp from # order by a1, a2

select a1, 
	a2 = avg(case when n between nx/2 and nx/2+nx%2 then a2 end),
	a3 = avg(a3)
from
(
	select *,
		n = id - (select isnull(max(id),0)+1 from #temp where a1<t.a1),
		nx = (select count(1) from #temp where a1=t.a1) - 1
	from #temp t
) a
group by a1
/*
a1       a2                     a3
-------- ---------------------- ----------------------
a        5.5                    4.5
b        7                      9
*/

[「已注销」]

---中值与平均值：

SELECT a1,
	a2=CAST(((SELECT MAX(a2) FROM (SELECT TOP 50 PERCENT a2 FROM #tp p WHERE t.a1=p.a1 ORDER BY a2)M)
	+
	(SELECT MIN(A2) FROM (SELECT TOP 50 PERCENT A2 FROM #TP P WHERE P.A1=T.A1 ORDER BY a2 DESC)N))/2.0 AS DECIMAL(5,2))
	,cast(AVG(a3*1.0) AS DECIMAL(5,2))AS a3
FROM #tp t
GROUP BY a1

a1   a2                                      a3
---- --------------------------------------- ---------------------------------------
a    5.50                                    4.50
b    7.00                                    9.00

(2 row(s) affected)

[dawugui]

create table tb(a1 varchar(10),a2 int,a3 int)
insert into tb values('a', 3 ,5)
insert into tb values('a', 7 ,6)
insert into tb values('a', 4 ,3)
insert into tb values('a', 9 ,4)
insert into tb values('b', 9 ,3)
insert into tb values('b', 7 ,6)
insert into tb values('b', 8 ,9)
insert into tb values('b', 6 ,12)
insert into tb values('b', 5 ,15)
go

select m.a1 , a2 = 
       cast(avg(n.a2*1.0) as decimal(18,1)), 
       m.a3 from
(select a1 , count(1) a2 , cast(avg(a3*1.0) as decimal(18,1)) a3 from tb group by a1 ) m,
(select t.* , px = (select count(1) from tb where a1 = t.a1 and (a2 < t.a2 or (a2 = t.a2 and a3 < t.a3))) + 1 from tb t) n
where m.a1 = n.a1 and ((m.a2 % 2 = 1 and n.px = m.a2 / 2 + 1) or (m.a2 % 2 = 0 and (n.px = m.a2 / 2 + 1 or n.px = m.a2 / 2)))
group by m.a1 , m.a3

drop table tb

/*
a1         a2                   a3                   
---------- -------------------- -------------------- 
a          5.5                  4.5
b          7.0                  9.0

（所影响的行数为 2 行）
*/