假設我有一個桌面電子游戲，我想找到從 1997 年開始每隔 3 年發布的游戲數量。
視頻游戲
| 視頻游戲ID | 標題 | 年 |
|---|---|---|
| 1 | 金眼007 | 1997 |
| 2 | 古墓麗影 II | 1997 |
| 3 | 半衰期 | 1998 |
| 4 | 模擬人生 | 2000 |
| 5 | 俠盜獵車手 (III) | 2001年 |
| 6 | 王國之心 | 2003年 |
| 7 | 魔獸世界 | 2004年 |
| 8 | ES4：遺忘 | 2006年 |
| 9 | 黑洛杉磯 | 2011 |
| 10 | 孤島驚魂3 | 2012 |
| 11 | 暗黑破壞神III | 2012 |
由表可知，預期產出應為 Year (1997-1999) = 3, Year (2000-2002) = 2, Year(2003 - 2005) = 2, Year(2006-2008) = 1, Year (2009 - 2011) ) = 1 和年份 (2012-2014)= 2
這是我解決代碼的嘗試：
SELECT COUNT(videogameid) AS number_of_videogames
FROM videogames
WHERE INTERVAL(1997,2,2,2,2,2,2)
GROUP BY YEAR;
出于某種原因，當每個間隔應該只有 6 行時，我回傳了超過 100 行的答案。
uj5u.com熱心網友回復：
2022-03-06 更新
列出沒有參考年份數字的總數似乎很奇怪 :-) 但如果這就是你想要的，請嘗試：
WITH recursive ReleaseYears AS (
-- build list of years
SELECT MIN(`year`) AS YearNum
FROM VideoGames
UNION ALL
SELECT YearNum 1
FROM ReleaseYears
WHERE YearNum <= ( SELECT MAX(`year`) 2 FROM VideoGames)
)
SELECT COUNT(vg.videogameid) AS Num
FROM ( -- group year numbers in groups of 3
SELECT FLOOR((ROW_NUMBER() OVER(ORDER BY (SELECT YearNum)) -1) / 3) 1 AS YearGroup
, YearNum
FROM ReleaseYears
) y LEFT JOIN VideoGames vg ON y.YearNum = vg.`year`
GROUP BY YearGroup
HAVING COUNT(vg.videogameid) > 0
結果：
| 編號 | | --: | | 3 | | 2 | | 2 | | 1 | | 1 |
原始答案
如果你想以“(min-max)”格式顯示年份：
WITH recursive ReleaseYears AS (
SELECT MIN(`year`) AS YearNum
FROM VideoGames
UNION ALL
SELECT YearNum 1
FROM ReleaseYears
WHERE YearNum <= ( SELECT MAX(`year`) 2 FROM VideoGames)
)
SELECT YearGroup
, CONCAT_WS('-', MIN(YearNum), MAX(YearNum)) AS YearRange
, COUNT(vg.videogameid) AS Num
FROM (
SELECT FLOOR((ROW_NUMBER() OVER(ORDER BY (SELECT YearNum)) -1) / 3) 1 AS YearGroup
, YearNum
FROM ReleaseYears
) y LEFT JOIN VideoGames vg ON y.YearNum = vg.`year`
GROUP BY YearGroup
HAVING COUNT(vg.videogameid) > 0
;
結果：
年組 | 年份范圍 | 編號
--------: | :-------- | --:
1 | 1997-1999 | 3
2 | 2000-2002 | 2
3 | 2003-2005 | 2
4 | 2006-2008 | 1
5 | 2009-2011 | 1
6 | 2012-2014 | 2
db<>fiddle here
uj5u.com熱心網友回復：
關于我認為你能得到的最簡單的方法是使用遞回 CTE 來生成你的年份，然后簡單地將它外連接到你的源資料并像這樣分組為 3：
with recursive n as (
select Min(year) yn, Ceiling((max(year)-Min(year))/3.0)*3 min(year) maxyear, Min(year) minyear
from t
union all
select yn 1, maxyear, minyear
from n
where yn < maxyear
)
select Min(yn) FromYear, Max(yn) toYear, Count(year) qty
from n
left join t on t.year=yn
group by floor((yn - minyear) /3);
演示資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/438964.html