SQL Server 我想知道每十年有多少电影

kulphzqa  于 2022-11-21  发布在  其他
关注(0)|答案(4)|浏览(122)

已尝试此操作,但它显示DECADE列,每行都是NULL

SELECT ReleaseDate, COUNT (Title),
CASE
        WHEN ReleaseDate BETWEEN 2010 AND 2019 THEN '2010 - 2019'
        WHEN ReleaseDate BETWEEN 2000 AND 2009 THEN '2000 - 2009'
        WHEN ReleaseDate BETWEEN 1990 AND 1999 THEN '1990 - 1999'
        WHEN ReleaseDate BETWEEN 1980 AND 1989 THEN '1980 - 1989'
        WHEN ReleaseDate BETWEEN 1970 AND 1979 THEN '1970 - 1979'
        WHEN ReleaseDate BETWEEN 1960 AND 1969 THEN '1960 - 1969'
        END AS DECADE
FROM Film
GROUP BY ReleaseDate
woobm2wo

woobm2wo1#

你可以用整数除法得到十进制数。所以,不需要CASE语句,但你必须GROUP BY十进制数

SELECT
    CONVERT(nvarchar(3), YEAR(ReleaseDate) / 10) + '0 - ' +
      CONVERT(nvarchar(3), YEAR(ReleaseDate) / 10) + '9' AS Decade,
    Count(*) AS [Count]
FROM Film
GROUP BY YEAR(ReleaseDate) / 10
ORDER BY YEAR(ReleaseDate) / 10

我假设ReleaseDate具有datedatetime类型。
结果会是这样的:
| 十年|计数|
| - -|- -|
| 1990年至1999年|十四|
| 2000年至2009年|十七岁|
| 2010年至2019年|二十三个|
| 2020年至2029年|三个|
例如:如果日期为2012-08-17,则YEAR(ReleaseDate)20122012 / 10201。我们将其转换为nvarchar(3)-〉'201',并添加字符串'0 - ',得到'2010 - '。然后我们再次执行相同的操作,但添加'9',得到2019。结果:'2010 - 2019' .
这种方法的优点是可以自动获取数据库中存储的所有十年。当新的十年开始时,可以自动获取,而不必补充CASE表达式。

gojuced7

gojuced72#

实际上,您可能希望使用CROSSAPPLY,以便可以引用别名
仅供参考-从技术上讲,这十年始于2001年

示例

SELECT B.Decade
      ,COUNT(*),
FROM Film A
CROSS APPLY ( values (
                    CASE  WHEN year(ReleaseDate) BETWEEN 2010 AND 2019 THEN '2010 - 2019'
                          WHEN year(ReleaseDate) BETWEEN 2000 AND 2009 THEN '2000 - 2009'
                          WHEN Year(ReleaseDate) BETWEEN 1990 AND 1999 THEN '1990 - 1999'
                          WHEN year(ReleaseDate) BETWEEN 1980 AND 1989 THEN '1980 - 1989'
                          WHEN year(ReleaseDate) BETWEEN 1970 AND 1979 THEN '1970 - 1979'
                          WHEN year(ReleaseDate) BETWEEN 1960 AND 1969 THEN '1960 - 1969'
                     END 
                     ) 
            ) B(Decade)
GROUP BY B.Decade
zpf6vheq

zpf6vheq3#

我猜问题可能出在时间戳的格式上,尝试在ReleaseDate上使用YEAR(),如下所示

CASE 
    WHEN YEAR(ReleaseDate) BETWEEN 2010 AND 2019 THEN '2010 - 2019' 
    ...
    ...
END
f3temu5u

f3temu5u4#

ReleaseDate似乎是一个日期,在这种情况下,您可以使用日期范围:

SELECT DECADE, COUNT(*) FROM (
SELECT 
CASE
        WHEN ReleaseDate BETWEEN '2010-01-01 00:00:00' AND '2019-01-01 00:00:00' THEN '2010 - 2019'
        WHEN ReleaseDate BETWEEN '2000-01-01 00:00:00' AND '2009-01-01 00:00:00' THEN '2000 - 2009'
        WHEN ReleaseDate BETWEEN '1990-01-01 00:00:00' AND '1999-01-01 00:00:00' THEN '1990 - 1999'
        WHEN ReleaseDate BETWEEN '1980-01-01 00:00:00' AND '1989-01-01 00:00:00' THEN '1980 - 1989'
        WHEN ReleaseDate BETWEEN '1970-01-01 00:00:00' AND '1979-01-01 00:00:00' THEN '1970 - 1979'
        WHEN ReleaseDate BETWEEN '1960-01-01 00:00:00' AND '1969-01-01 00:00:00' THEN '1960 - 1969'
        END AS DECADE
FROM Film
) t
GROUP BY DECADE

相关问题