SQL SERVER Cross Apply

同事的一个问题，列出所有用户返点最高的一条数据

代码

SELECT  B. *   FROM  ( SELECT   DISTINCT   [ KeyinID ]   FROM  DE_AtmosphereEvaluation) A
CROSS  APPLY
(
SELECT   TOP   1  B. *    FROM  DE_AtmosphereEvaluation B  WHERE  A. [ KeyinID ] = B. [ KeyinID ]   AND  B. [ PointID ]   NOT   IN

(

SELECT   [ ID ]   FROM   [ DE_AtmosphereMonitoringPoint ]   WHERE   [ ESource ] = ' 上风向 ' ) 

ORDER   BY  B. [ Scoring ]   DESC  

) B

 

 

 

代码

-- 建立一个表值函数：  
CREATE   FUNCTION   [ dbo ] . [ GetTop ]
(  
     @Star   varchar ( 50 )
)
RETURNS   TABLE
AS
RETURN
(
     select   top   3  Star,SongName,Hot  from  songstop  where  star  =   @Star   order   by  hot  desc
)

-- 使用cross apply 来连接 
select   distinct  b.star,b.songname,b.hot  from  songstop s
cross  apply dbo.GetTop(s.star) b
order   by  star  asc ,hot  desc

 

--   使用   partition by 分区  
select   *   from  
( select  Star,SongName,Hot,ROW_NUMBER()  OVER  (PARTITION  BY  Star  ORDER   BY  Hot  DESC )  AS  ROWNO  from  songstop ) a  where  ROWNO <= 3

 

 

 

 

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/liuyong/archive/2010/11/22/1868136.html