上面的语句貌似有点问题,正确的应该为:
SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
) a3
WHERE Rank = (SELECT (COUNT(*)+1)/2 FROM Total_Sales); --下面看看过程
--1.先按照sales大小排名,如果sales相同,则按name排名
SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
/**
Name Sales Rank
-------------------- ----------- -----------
John 10 6
Jennifer 15 5
Jeff 20 4
Stella 20 3
Sophia 40 2
Greg 50 1(所影响的行数为 6 行)
**/--2.从做好排名的表中找出排名位于总数中间的记录
/**
Name Sales Rank
-------------------- ----------- -----------
Stella 20 3
**/
SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
) a3
WHERE Rank = (SELECT (COUNT(*)+1)/2 FROM Total_Sales); --下面看看过程
--1.先按照sales大小排名,如果sales相同,则按name排名
SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
/**
Name Sales Rank
-------------------- ----------- -----------
John 10 6
Jennifer 15 5
Jeff 20 4
Stella 20 3
Sophia 40 2
Greg 50 1(所影响的行数为 6 行)
**/--2.从做好排名的表中找出排名位于总数中间的记录
/**
Name Sales Rank
-------------------- ----------- -----------
Stella 20 3
**/
解决方案 »
- 关于变量赋值问题:要查找的字段为numeric类型,如果有null则先用isnull函数将其赋值为'0'然后再将其赋值给变量,但是就出错了,求指导
- 请教一个查询语句
- 换了台机子就装不上sql了,N郁闷
- 50分求从一个存储过程得到的大量数据怎样插入另一个表?
- 急!!SQL2005不能正常访问Acess数据库
- 在两台服务器做sql2000数据同步时出现 进程未能读取文件snapshot.pre,因为发生操作系统错误 53。找不到网络路径
- 如何计算数据库中的时间范围
- ADO远程连接数据库的奇怪问题
- 我用SQL7.0,把一个字段过滤,相同字段只显示一个,并汇总出有多少行。
- 求教如何写脚本语言创建数据库、表
- 请教下SQL2005的数据库我想还原到另外一台电脑上,为何还原出错啊!?
- 删除一个表中姓名重复的数据,只保留重复数据中的一条数据?怎么实现???
Stella 20 3
1楼正解
SELECT (COUNT(*)+1)/2 FROM Total_Sales
返回的 只用一个数据啊,不知楼主的最中间是不是次数字
select Top 1 Sales, [Name] from
( select Top 50 percent * from Total_Sales order by Sales, [Name]) T
order by Sales desc, [Name] desc
as(select *,rank = row_number() over (order by sales,name)
from total_sales
),
t1
as
(select cast(max(rank) as numeric(12,2)) as c from t)
select * from t1,t
where abs(rank - c/2 ) < 1
Name varchar(10),
Sales numeric(10,2)
)
insert @Total_Sales select
'John', 10
union all select
'Jennifer', 15
union all select
'Stella', 20
union all select
'Sophia', 40
union all select
'Greg', 50
union all select
'Jeff', 18 SELECT avg(Sales) Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM @Total_Sales a1, @Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
) a3
WHERE abs(Rank - (SELECT 1.0*(COUNT(*)+1)/2 FROM @Total_Sales))<1
-- 结果
Median
----------------------------------------
19.000000(所影响的行数为 1 行)
Name varchar(10),
Sales numeric(10,2)
)
insert @Total_Sales select
'John', 10
union all select
'Jennifer', 15
union all select
'Sophia', 40
union all select
'Greg', 50
union all select
'Jeff', 18 SELECT avg(Sales) Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM @Total_Sales a1, @Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
) a3
WHERE abs(Rank - (SELECT 1.0*(COUNT(*)+1)/2 FROM @Total_Sales))<1
--结果
Median
----------------------------------------
18.000000(所影响的行数为 1 行)