查找记录最大的十年，SQL Server [英] Finding the decade with largest records, SQL Server

问题描述

我有以下数据库图:

我想找到电影数量最多的十年(例如 1990 年到 2000 年).实际上它只涉及电影".表.

I want to find the decade (for example 1990 to 2000) that has the most number of movies. Actually it only deals with "Movies" table.

知道如何做到这一点吗?

Any idea on how to do that?

推荐答案

您可以使用 SQL Server 中的 LEFT 函数来获取年份的十年.十年是年份的前 3 位数字.您可以按年代分组，然后计算电影数量.如果您按电影数量对结果进行排序或排序 - 电影数量最多的十年将排在最前面.例如:

You can use the LEFT function in SQL Server to get the decade from the year. The decade is the first 3 digits of the year. You can group by the decade and then count the number of movies. If you sort, or order, the results by the number of movies - the decade with the largest number of movies will be at the top. For example:

select
count(id) as number_of_movies,
left(cast([year] as varchar(4)), 3) + '0s' as decade
from movies
group by left(cast([year] as varchar(4)), 3)
order by number_of_movies desc

这篇关于查找记录最大的十年，SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！