--我先创建一个临时表、(主要的懒得建表)
CREATE TABLE #Year
(
DateYear VARCHAR(20), --年份
Number INT
)
---模拟往临时表中插入三年的数据
INSERT #Year
( DateYear, Number )
VALUES ( '2018-08-08', -- DateYear - varchar(20)
50 -- Number - int
)
INSERT #Year
( DateYear, Number )
VALUES ( '2018-08-10', -- DateYear - varchar(20)
80 -- Number - int
)
INSERT #Year
( DateYear, Number )
VALUES ( '2018-08-18', -- DateYear - varchar(20)
100 -- Number - int
)
INSERT #Year
( DateYear, Number )
VALUES ( '2017-08-18', -- DateYear - varchar(20)
101 -- Number - int
)
INSERT #Year
( DateYear, Number )
VALUES ( '2017-08-20', -- DateYear - varchar(20)
120 -- Number - int
)
INSERT #Year
( DateYear, Number )
VALUES ( '2016-08-20', -- DateYear - varchar(20)
120 -- Number - int
)
INSERT #Year
( DateYear, Number )
VALUES ( '2016-08-21', -- DateYear - varchar(20)
124 -- Number - int
)
--模拟了三年的数据,我在这里进行了排序,这样可以很清晰的看出来每一年的数据
SELECT * FROM #Year ORDER BY DateYear,Number
--接下来我们先给每一年的数据做个排名
SELECT * FROM (SELECT a.*,ROW_NUMBER() OVER( PARTITION BY YEAR(DateYear) ORDER BY Number DESC) AS PM FROM #Year a) c
--做了排名之后是不是很容易就能获取到了
--在排名的基础上加一个 PM=1的条件就可以了
SELECT * FROM (SELECT a.*,ROW_NUMBER() OVER( PARTITION BY YEAR(DateYear) ORDER BY Number DESC) AS PM FROM #Year a) c WHERE c.PM=1