SQL 通过近30天生成一张日期维度表，去关联业务生成报表数据

1.需求：通过SQL Server 语句动态生成近30天的一张表数据，然后去关联业务按日期分组的业务数据。

 WITH
    TransDate AS (
        select
            DATEADD(DAY, number, GETDATE() -30) AS EveryDate
        FROM
            (
                SELECT
                    TOP 30 ROW_NUMBER() OVER (
                        ORDER BY
                            (
                                SELECT
                                    NULL
                            )
                    ) -1 AS number
                FROM
                   UserRecords
            ) T
    )
SELECT
    B.EveryDate,
    A.UserIds,
    A.StartDate,
    A.StopDate,
    A.AttendanceOrg,
    A.JobType
FROM
    TransDate B
    LEFT JOIN UserRecords A on A.StartDate <= B.Sys_AttendanceDate
    AND A.StopDate >= B.EveryDate
WHERE
    A.JobType = 2

　　

关键使用 RowNumber() OVER 函数思想。按照每日维度拆分。