cross apply
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
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
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
