收益有重复值的行业内排名前5位求平均值

zyh196712 · 发表于 2010-8-25 23:55

（不管意义有多大，本人只是想提供一个解决问题的思路，与大家共同探讨这类问题的解决办法，比如：成绩排名一定会涉及类似的情形吧，当然，用数据透视表来解决对于我来说还未尝试），好了，言规正传。

如果收益没有重复值的话，行业内排名前5位求平均值（前5个大值），通过数据透视表的筛选功能即可求得，是很容易求的。但是，如果有重复值，怎么求呢？要不是所有重复值全部计算平均值，但如果只需要前5位的值计算平均值的话，用数据透视表来做并不是那么简单的。
下图是收益有重复值的行业内排名前5位求平均值的数据透视表结果：
行业内排名前5位.jpg

现以 http://club.excelhome.net/viewth ... p;extra=&page=3 中25楼的附件数据为例（其它楼层的数据好像做了修改，没有重复值）来说明用SQL如何求
收益有重复值的行业内排名前5位求平均值。
收益有重复值的行业内排名前5位求平均值SQL见下图：
行业内排名前5位求平均值.jpg

以上语句中关键是利用行业内收益"排名1"来进行主要排位，

(select 1+count(*) from
(select distinct 行业,收益 from [Sheet1$]) a where a.行业=b.行业 and a.收益>b.收益) as 排名1,

复制代码

再利用名称的大小来进行次排位获得"排名2"，

(select 1+count(*) from
(select distinct 行业,名称 from [sheet1$]) c where c.行业=b.行业 and c.名称>b.名称) as 排名2,

复制代码

利用val(排名1&排名2) 以此生成字段"排序"，做为最后排位的依据。
最后以自表查询来获得字段：行业,名称,收益,排位。

select *,
(select 1+count(*) from
(select distinct 行业,名称 from [sheet1$]) c where c.行业=b.行业 and c.名称>b.名称) as 排名2,
(select 1+count(*) from
(select distinct 行业,收益 from [Sheet1$]) a where a.行业=b.行业 and a.收益>b.收益) as 排名1,
val(排名1&排名2) as 排序 from
[sheet1$] b

复制代码

通过以上SQL的组合生成行业内收益排名前5位的数据透视表。

masterexcel · 发表于 2010-8-26 09:08

思路参见Scarlett_88版主的置顶帖SQL&Excel结合经典剖析：47期基础题呀，几多疑惑，几多玩味。我们来细细品一品1楼

1、2生成序号的方法

另一种代码

select
行业,
收益,
(
select
count(*)
from
(
select distinct
行业,
收益
from
[Sheet1$]
order by
行业,
收益 desc
)
where
行业=a.行业 and
收益>=a.收益
) as 排名
from
(
select distinct
行业,
收益
from
[Sheet1$]
order by
行业,
收益 desc
)a

复制代码

[ 本帖最后由 masterexcel 于 2010-8-26 09:09 编辑 ]

zyh196712 · 发表于 2010-8-26 19:06

原帖由 masterexcel 于 2010-8-26 09:08 发表
思路参见Scarlett_88版主的置顶帖SQL&Excel结合经典剖析：47期基础题呀，几多疑惑，几多玩味。我们来细细品一品1楼
另一种代码
select
行业,
收益,
(
select
count(*)
from
(
select dist ...

谢谢断轮回同学热烈参与讨论！

你的SQL语句是取唯一值的序。如果有重复值，并且只取从最大收益向下排的前5位，取唯一值就不行了。如电子产品中有三个收益为44的，按此语句会将另两个44剔除掉，取的值就不是955、78、48、44、44。

另外：嵌套查询子句的ORDER BY 行业,收益 desc 应该删除，子查询那里用不用都不起作用。
（ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序
任何情况下，ORDER BY子句只能出现在最后），这是我下面这个课件中看到的：快来看呀，SQL学习的好课件
看下面这个句子错误运用ORDER BY :
select top 5 行业,名称,收益 from [sheet1$] where 行业='电力' order by 收益 desc,名称 desc
union all
select top 5 行业,名称,收益 from [sheet1$] where 行业='电子产品' order by 收益 desc,名称 desc
union all
select top 5 行业,名称,收益 from [sheet1$] where 行业='房地产' order by 收益 desc,名称 desc
本想得到表2-1，2-2，2-3结合形成的表，但错误地运用规则就成了表1的效果，没有正确查出所想要的数据。似乎查询结果只与数据源的排序有关，我试着将数据源按行业-数值升序，收益-数值-降序似乎能取正确的值，（不知是不是偶然现象，如果不是偶然现象，如果想偷懒的话，就对数据源进行排序然后再用此语句

，当然，这是不可取的哦）具体还得进一步的尝试。

[ 本帖最后由 zyh196712 于 2010-8-26 19:09 编辑 ]

zyh196712 · 发表于 2010-8-26 23:53

一、如果只考虑收益的前5位取值，下面的语句可得正确前5位结果，有没有更简单的写法呢？（我还没找到相关资料
，请会的学友们贴出来）
select top 5 * from (SELECT * FROM [sheet1$] where 行业='电力' ORDER BY 行业,收益 desc) union all
select top 5 * from (SELECT * FROM [sheet1$] where 行业='电子产品' ORDER BY 行业,收益 desc) union all
select top 5 * from (SELECT * FROM [sheet1$] where 行业='房地 ORDER BY 行业,收益 desc)

二、下面的(相关子查询)不能得到正确结果，感觉这个查询写法有点怪，子查询写的较牵强，似乎没什么意义:
select  * from [sheet1$] a where 行业&"|"&名称
      in (SELECT top 5 行业&"|"&名称 FROM [sheet1$] b where a.行业=b.行业 and a.名称=b.名称 order by 收益 desc)
上句理论上似乎会执行子查询中的top n …order by ，但结果却不正确，这里的 order by 用法正确吗？我个人认为不正确。
看来order by 并不简单，还得再查查资料了。

[ 本帖最后由 zyh196712 于 2010-8-27 07:15 编辑 ]

zyh196712 · 发表于 2010-8-27 09:08

原帖由 zyh196712 于 2010-8-26 23:53 发表
二、       下面的(相关子查询)不能得到正确结果，感觉这个查询写法有点怪，子查询写的较牵强，似乎没什么意义:
      select  * from [sheet1$] a where 行业&"|"&名称
      in (SELECT top 5 行业&"|"&名称 FROM [sheet1$] b where a.行业=b.行业 and a.名称=b.名称 order by 收益 desc)

汗，之二限定条件用错了，应该是按收益来比较的，修改之二的语句如下：

select * from [sheet1$] a where 收益 in (SELECT top 5 收益 FROM [sheet1$] b where a.行业=b.行业 order by 收益 desc) order by 行业='电力',行业,收益 desc

这个语句因为top的特性，将与排名第5并列的数据都查出来了。

zyh196712 · 发表于 2010-8-27 09:48

原帖由 zyh196712 于 2010-8-26 23:53 发表
二、下面.....
这里的 order by 用法正确吗？我个人认为不正确。
看来order by 并不简单，还得再查查资料了

经过测试，在子句中用order by 这样理解：
如果子句中用：top n...order by ，可以应用，因为top n ...是根据order by 来获得查询得到的结果；
如果子句中只用:order by 对排序是不起作用，在表查询中查询的结果与子句中order by 没有关系；
在union all句子的情况：详见3楼和4楼，3楼是错误的用法。

order by 用法也不简单D，至今才知道这一点

[ 本帖最后由 zyh196712 于 2010-8-27 09:56 编辑 ]

zyh196712 · 发表于 2010-8-27 23:52

原帖由 zyh196712 于 2010-8-26 23:53 发表
select * from [sheet1$] a where 行业&"|"&名称
in (SELECT top 5 行业&"|"&名称 FROM [sheet1$] b where a.行业=b.行业 and a.名称=b.名称 order by 收益 desc)

此句错在多了个and a.名称=b.名称，如按此句，实际查询的是不同行业，不同名称的收益取值5个，而不是行业的收益取值5个。此句应该这样修改：

select * from [sheet1$] a where 行业&"|"&名称
in (SELECT top 5 行业&"|"&名称 FROM [sheet1$] b where a.行业=b.行业 order by 收益 desc)

		自动登录	找回密码
密码			免费注册

[分享] 收益有重复值的行业内排名前5位求平均值