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

zyh196712

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

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

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

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

masterexcel

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

1、2生成序号的方法

另一种代码

1. 
   
2. select
   
3.         行业,
   
4.         收益,
   
5.         (
   
6.         select
   
7.                 count(*)
   
8.         from
   
9.                 (
   
10.                 select distinct
    
11.                         行业,
    
12.                         收益
    
13.                 from
    
14.                         [Sheet1$]
    
15.                 order by
    
16.                         行业,
    
17.                         收益 desc
    
18.                 )
    
19.         where
    
20.                 行业=a.行业 and
    
21.                 收益>=a.收益
    
22.         ) as 排名
    
23. from
    
24.         (
    
25.         select distinct
    
26.                 行业,
    
27.                 收益
    
28.         from
    
29.                 [Sheet1$]
    
30.         order by
    
31.                 行业,
    
32.                 收益 desc
    
33.         )a
    

复制代码

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

zyh196712

原帖由 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

一、        如果只考虑收益的前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

原帖由 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

原帖由 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

原帖由 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)