在 PostgreSQL 中，我們使用：
SELECT * FROM generate_series(1, 100)
SELECT * FROM generate_series('2020-01-01 00:00'::timestamp,'2020-01-02 00:00'::timestamp,'1 minutes')
我們如何在 Microsoft SQL Server 中創建時間序列？
uj5u.com熱心網友回復：
您可以使用遞回“翻譯”T-SQL 中的陳述句：
DECLARE @datetime datetime2(0) = '2020-01-01 00:00:00'
DECLARE @start int = 1
DECLARE @stop int = 100
; WITH rCTE AS (
SELECT @start - 1 AS rn
UNION ALL
SELECT rn 1 FROM rCTE WHERE rn < @stop
)
SELECT DATEADD(minute, rn, @datetime)
FROM rCTE
OPTION (MAXRECURSION 0)
uj5u.com熱心網友回復：
SQL Server 沒有“簡單”功能。取而代之的是，杰夫·莫登（Jeff Moden）提出了很多想法，并支持計分表。反過來又被其他人撿了起來。
喜歡這個代碼>
/*
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/
Source: http://sqlsunday.com/downloads/
WHAT: Returns a gregorian calendar table for a specific interval of dates.
DISCLAIMER: This script may not be suitable to run in a production
environment. I cannot assume any responsibility regarding
the accuracy of the output information, performance
impacts on your server, or any other consequence. If
your juristiction does not allow for this kind of
waiver/disclaimer, or if you do not accept these terms,
you are NOT allowed to store, distribute or use this
code in any way.
USAGE: @from: Starting date
@to: Ending date
VERSION: 2018-07-17
*/
ALTER FUNCTION Calendar.Dates(@from date, @to date)
RETURNS TABLE
AS
RETURN (
--- Generic number table with 1000 rows:
WITH n(i) AS (
SELECT 0
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS x1(i)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS x2(i)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS x3(i)),
--- One row for each date between @from and @to.
--- Uncomment x3 if you need more than 1 million days. :)
n2(i) AS (
SELECT TOP (DATEDIFF(day, @from, @to) 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM n AS x1, n AS x2 -- , n AS x3, n AS x4
),
--- i is an incrementing integer, starting with 0 on @from.
dt(i, [date]) AS(
SELECT i, DATEADD(day, i, @from) AS [date]
FROM n2)
SELECT dt.i,
dt.[date] AS [Date],
CAST(YEAR(dt.[date]) AS smallint) AS [Year],
CAST(DATEPART(quarter, dt.[date]) AS tinyint) AS [Quarter],
CAST(CAST(YEAR(dt.[date]) AS char(4)) ' Q' CAST(DATEPART(quarter, dt.[date]) AS char(1)) AS char(7)) AS Year_quarter,
CAST(MONTH(dt.[date]) AS tinyint) AS [Month],
CAST(CAST(YEAR(dt.[date]) AS char(4)) '-' REPLACE(STR(MONTH(dt.[date]), 2, 0), ' ', '0') AS char(7)) AS Year_month,
CAST(DAY(dt.[date]) AS tinyint) AS [Day],
CONVERT(char(10), dt.[date], 121) AS Date_ISO,
CAST(dt1.iso_week_year AS smallint) AS ISO_week_year,
CAST(DATEPART(isowk, dt.[date]) AS tinyint) AS [ISO_week],
CAST(CAST(dt1.iso_week_year AS varchar(4)) ' W' REPLACE(STR(DATEPART(isowk, dt.[date]), 2, 0), ' ', '0') AS char(8)) AS ISO_year_week,
CAST(dt1.weekday_iso AS tinyint) AS ISO_weekday_number,
CAST(DATEPART(week, dt.[date]) AS tinyint) AS US_week,
CAST(CAST(YEAR(dt.[date]) AS varchar(4)) ' W' REPLACE(STR(DATEPART(week, dt.[date]), 2, 0), ' ', '0') AS char(8)) AS US_year_week,
CAST(dt1.weekday_us AS tinyint) AS US_weekday_number,
DATENAME(weekday, dt.[date]) AS Weekday_name,
CAST(DATEPART(dy, dt.[date]) AS smallint) AS Day_of_year,
30*(MONTH(dt.[date])-1) (CASE WHEN dt.[date]=EOMONTH(dt.[date]) THEN 30 ELSE DAY(dt.[date]) END) AS Day_of_year_30E_360
FROM dt
--- Calculation steps:
CROSS APPLY (
VALUES (
1 (DATEPART(dw, dt.[date]) @@DATEFIRST-2)%7,
1 (DATEPART(dw, dt.[date]) @@DATEFIRST-1)%7,
YEAR(dt.[date]) (CASE WHEN DATEPART(isowk, dt.[date])>50 AND DATEPART(dy, dt.[date])<7 THEN -1
WHEN DATEPART(isowk, dt.[date])=1 AND DATEPART(dy, dt.[date])>300 THEN 1
ELSE 0 END)
)) AS dt1(weekday_iso, weekday_us, iso_week_year)
)
GO
它的核心使用一個數字表來生成日期和其他屬性。如果需要，這可以更改為時間。
uj5u.com熱心網友回復：
這解決了為給定日期范圍生成每分鐘日期和時間的問題。最初的概念是十多年前由 Itzik Ben-Gan 提供的。它可以變成像@KnutBoehnert 那樣的函式。詳細資訊在評論中。請注意，DATEADD 的日期部分數量限制為 INT 的正限制，對于這個問題，它是 2,147,483,647 分鐘，當該數量的分鐘添加到 1900-01-01 00:00:00。
DECLARE @LoDT DATETIME2(0) = '2020-01-01 00:00' --Inclusive
,@HiDT DATETIME2(0) = '2020-01-02 00:00' --Inclusive
;
WITH
--====== Create up to 16 rows of one's as the initial row source for the Tally "Pseudo-Cursor"
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N)),
--====== Create one numbered row per second in the desired date range starting at 0.
-- I call this a "Pseudo-Cursor" because it's a "set based" way to count without using an explicit loop
-- or a recursive CTE which is even slower and more resource intensive that a properly built WHILE loop.
Tally(N) AS (SELECT N = 0 UNION ALL --Starts with "0"
SELECT TOP(DATEDIFF(mi,@LoDT,@HiDT)) --Controls the number of minute rows to be created
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --Number of minutes to add to @LoDT for each row.
FROM E1 a,E1 b,E1 c,E1 d,E1 e,E1 f,E1 g,E1 h) --Up to 16^8 or 4,294,967,296 rows thanks to CROSSJOIN.
--===== Create the final date squence by adding the minute rows created above to @LoDT
-- to create the date/time sequence.
SELECT DateSeq = DATEADD(mi,t.N,@LoDT)
FROM Tally t
ORDER BY t.N
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/432834.html
標籤：sql服务器 tsql sql-server-2008