英文:
How to filter results by last 10 years in int column
问题
我想要创建一个查询，始终返回最近的税年以及过去的10个税年。我尝试了以下查询，但只返回了从1980年到2023年的所有年份。再次说明，尽管它是年份，但这不是日期列，而是小整数。
SELECT Taxyear
FROM Property
GROUP BY Taxyear
HAVING Taxyear BETWEEN Max(Taxyear) - 10 AND Max(Taxyear)
我期望的结果是：
2013
2014
2015
2016
2017
2018
2019
等等...
英文:
I have a table with a year field called Taxyear that are of the integer type. I want to create a query that will always give me the most recent taxyear, plus the past 10. If this was for one and done data request I would just filter by taxyear between 2023 and 2013 but I need this query to always look at the most recent 10 taxyears. There is more to this query but I'm breaking it down as simple as I can to isolate where the problem is that I'm having.
I have tried this query below but this just gives me all the years from 1980 - 2023. Again this isn't a date column even though it's year, it's smallint.
SELECT Taxyear
FROM Property
GROUP BY Taxyear
HAVING taxyear between Max(taxyear) -10 and Max(taxyear)
The results I'm expecting are
2013
2014
2015
2016
2017
2018
2019
etc...
答案1
得分: 1
原始查询不起作用的原因是GROUP BY条件本身是taxyear，所以max(taxyear)意味着当前行tax year的最大值，这总是成立。
有许多计算这个的方法，我这里只列出了两种方法：
方法1（使用限制子句，数据将按降序显示，2023、2022、...、2013）：
SELECT
taxyear
FROM
Property
GROUP BY taxyear
ORDER BY taxyear DESC
LIMIT 11
方法2（使用窗口函数，数据将以升序显示，2013、2014、...、2023）：
SELECT
DISTINCT taxyear
FROM
(SELECT
taxyear,
MAX(taxyear) OVER () AS max_taxyear
FROM
Property
) t
WHERE
max_taxyear - taxyear <= 10
ORDER BY taxyear
英文:
The reason why the original query doesn't work is the group by condition itself is taxyear, so max(taxyear) means max of current row tax year, which is always true.
There are many ways to calculate this, I just list 2 methods here:
Method 1(using limit clause, data will show in descending order, 2023,2022,...,2013):
SELECT
taxyear
FROM
Property
GROUP BY taxyear
ORDER BY taxyear DESC
LIMIT 11
Method 2 (using window function, data will show in ascending order, 2013,2014,...,2023):
select
distinct taxyear
from
(SELECT
taxyear,
max(taxyear) over() as max_taxyear
from
Property
) t
where
max_taxyear - taxyear <=10
order by taxyear
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论