SELECT DATEADD( day, row_num-1,
YMD( DATEPART( year, CURRENT DATE ),
DATEPART( month, CURRENT DATE ), 1 ) )
AS day_of_month
FROM sa_rowgenerator( 1, 31, 1 )
WHERE DATEPART( month, day_of_month ) =
DATEPART( month, CURRENT DATE )
ORDER BY row_num;
SELECT row_num AS r1, row_num+9999
AS r2, COUNT( PostalCode ) AS zips_in_range
FROM sa_rowgenerator( 0, 99999, 10000 ) D LEFT JOIN Employees
ON PostalCode BETWEEN r1 AND r2
GROUP BY r1, r2
ORDER BY 1;
以下示例生成 10 行数据并将其插入到 NewEmployees 表中:
INSERT INTO NewEmployees ( ID, Salary, Name )
SELECT row_num,
CAST( RAND() * 1000 AS INTEGER ),
'Mary'
FROM sa_rowgenerator( 1, 10 );
SELECT row_num AS r1, row_num+9999
AS r2, COUNT( PostalCode ) AS zips_in_range
FROM sa_rowgenerator( 0, 99999, 10000 ) D LEFT JOIN Employees
ON PostalCode BETWEEN r1 AND r2
GROUP BY r1, r2
ORDER BY 1;
以下示例生成 10 行数据并将其插入到 NewEmployees 表中:
INSERT INTO NewEmployees ( ID, Salary, Name )
SELECT row_num,
CAST( RAND() * 1000 AS INTEGER ),
'Mary'
FROM sa_rowgenerator( 1, 10 );
CREATE VIEW Dates AS
SELECT DATEADD( day, row_num, '0001-01-01' ) AS d
FROM sa_rowgenerator( 0, 3652058, 1 );
以下查询将返回 1900 至 2058 年之间包含 54 周的所有年份。
SELECT DATEADD ( day, row_num, '1900-01-01' ) AS d, DATEPART ( week, d ) w
FROM sa_rowgenerator ( 0, 63919, 1 )
WHERE w = 54;
SELECT 1 AS tag, null AS parent, null AS [enc_obj!1!row_num] FROM sa_rowgenerator(1, 2) UNION select 1 ,1 ,row_num FROM sa_rowgenerator(1, 2) ORDER BY 3, 1 FOR JSON EXPLICIT;