2

趣味SQL 又來了! 以今天為基準,往前400年,往後400年,統計13號出現在星期幾的總數.

``````+-----+-------+
| dow | count |
+-----+-------+
|   0 |  1374 |
|   1 |  1370 |
|   2 |  1370 |
|   3 |  1374 |
|   4 |  1368 |
|   5 |  1376 |
|   6 |  1368 |
+-----+-------+
(7 rows)
``````

``````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;
``````

2 個回答

0

iT邦大師 1 級 ‧ 2020-02-03 21:29:12

``````declare @now date = '2020-2-3'; --以今天為基準
--統計13號出現在星期幾的總數
with cte as (
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;
``````

Demo Link SQL Server 2012 | db<>fiddle

2
tw70126_tw
iT邦新手 5 級 ‧ 2020-02-04 11:58:54
``````DECLARE @StartDate DATE = '19800901'
, @EndDate DATE = '20201001';

WITH allDays AS
(
SELECT DATEADD(DAY, 0, @StartDate) AS generate_Date
UNION ALL