SQL:每年每月最高的两个温度

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Temperature](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Month] [int] NULL,
[Year] [int] NULL,
[Temperature] [int] NULL,
CONSTRAINT [PK_Temperature] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--每年每月最高的两个温度(版本1)
select t.* from (
select [Temperature],[YEAR],[Month],ROW_NUMBER() over(partition by [YEAR],[Month] order by [Temperature] desc) rownumber from [Temperature]
)t where t.rownumber<=2



--每年每月最高的两个温度(版本2)
select * from [Temperature] a where (
select COUNT(*) from [Temperature] b where a.Year=b.Year and a.Month=b.Month and a.Temperature<b.Temperature
)<2 order by a.Year,a.Month

转载于:https://www.cnblogs.com/xuxu-dragon/p/4885958.html