試過這個，但它顯示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
uj5u.com熱心網友回復：
您可以通過整數除法獲得十年。因此，不需要 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有一個dateordatetime型別。
結果將是這樣的：
| 十年 | 數數 |
|---|---|
| 1990 - 1999 | 14 |
| 2000 - 2009 | 17 |
| 2010 - 2019 | 23 |
| 2020 - 2029 | 3 |
示例：如果日期是2012-08-17，那么YEAR(ReleaseDate)是2012，2012 / 10是201。我們將其轉換為nvarchar(3)->'201'并添加'0 - '給出'2010 - '. 然后我們再次做同樣的事情，但添加'9'而不是 get 2019。結果：'2010 - 2019'。
這種方法的優點是您可以自動獲得存盤在資料庫中的所有十年。當新的十年開始時，您會自動獲得它，而無需補充您的 CASE 運算式。
uj5u.com熱心網友回復：
實際上，您可能想要使用 CROSS APPLY 以便您可以參考別名
僅供參考 - 從技術上講，這個十年始于 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
uj5u.com熱心網友回復：
我猜這個問題可能是時間戳的格式，嘗試像這樣在 ReleaseDate 上使用YEAR()
CASE
WHEN YEAR(ReleaseDate) BETWEEN 2010 AND 2019 THEN '2010 - 2019'
...
...
END
uj5u.com熱心網友回復：
ReleaseDate似乎是一個日期，在這種情況下您可以使用日期范圍：
SELECT ReleaseDate, COUNT (Title),
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
GROUP BY ReleaseDate
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/532263.html