以今天為基準,往前400年,往後400年,統計13號出現在星期幾的總數.
例如這樣的.
+-----+-------+
| dow | count |
+-----+-------+
| 0 | 1374 |
| 1 | 1370 |
| 2 | 1370 |
| 3 | 1374 |
| 4 | 1368 |
| 5 | 1376 |
| 6 | 1368 |
+-----+-------+
(7 rows)
看來13號星期五是最多的呢.
我是用這樣的方式:
select extract(dow from d) dow
, count(*)
from generate_series(date 'today' - interval '400 years'
, date 'today' + interval '400 years'
, interval '1 day') as g(d)
where extract(day from d) = 13
group by 1
order by 1;
已邀請的邦友 {{ invite_list.length }}/5
首先謝謝大大又提供好玩的題目 :D
以下是第一直覺寫法,使用SQL-Server的T-SQL來解這個問題
假如有其他靈感會再更新
之前舊的作法Demo Link SQL Server 2012 | db<>fiddle
更新後作法:
主要處理邏輯 : 只需要知道800年總共有9600個月 = 只會有9600個13號來處理資料就可以
declare @now date = '2020-2-3'; --以今天為基準
--統計13號出現在星期幾的總數
with cte as (
select datepart(dw, convert(nvarchar(8),dateadd(month,val,dateadd(year,-400,@now)),121) + '13') week
from (
--藉由spt_values + cross join得到多rows資料
select row_number() over (order by(select 1)) - 1 val
from master..spt_values T1,master..spt_values T2
where t1.type = 'P'
) T
where val <= 800 * 12 - 1
)
select week dow ,count(1) count
from cte
group by week
order by dow;
DECLARE @StartDate DATE = '19800901'
, @EndDate DATE = '20201001';
WITH allDays AS
(
SELECT DATEADD(DAY, 0, @StartDate) AS generate_Date
UNION ALL
SELECT DATEADD(DAY, 1, generate_Date)
FROM AllDays
WHERE generate_Date < @EndDate
)SELECT DATEPART(weekday,generate_Date) as dayOfWeek,count (0) countTimes
FROM AllDays
where day(generate_Date) = 13
group by DATEPART(weekday,generate_Date)
OPTION (MAXRECURSION 0)
iThome鐵人賽
看影片追技術