sql - 使用 Sql 查询以格式化方式获取用户数据

Question

我需要在报告中显示格式化数据。我使用三个表，其中一个添加新用户，第二个用于他们的付款，最后一个用于是否有用户辞职。以下是示例表和默认数据:

CREATE TABLE [dbo].[Entry]
(
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](40) NULL,
    [Address] [nvarchar](100) NULL,
    [Email] [nvarchar](20) NULL,
    [EntryDate] [datetime] NULL
)

INSERT [dbo].[Entry] ([EmpId], [EmpName], [Address], [Email], [EntryDate]) 
VALUES (1, N'John', N'On Earth', N'john@abc.com', CAST(0x0000A58000000000 AS DateTime)),
       (2, N'Jack', N'On Earth', N'jack@abc.com', CAST(0x0000A5A800000000 AS DateTime)),
       (3, N'Jessi', N'On Earth', N'jessi@abc.com', CAST(0x0000A5CF00000000 AS DateTime)),
       (4, N'Jackson', N'On Earth', N'jackson@abc.com', CAST(0x0000A5E400000000 AS DateTime))

CREATE TABLE [dbo].[Payment]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NULL,
    [Payment] [float] NULL,
    [PayDate] [datetime] NULL
)

INSERT [dbo].[Payment] ([Id], [EmpId], [Payment], [PayDate]) 
VALUES (1, 1, 2000, CAST(0x0000A61800000000 AS DateTime)), (2, 1, 2000, CAST(0x0000A63600000000 AS DateTime)),
       (3, 1, 2000, CAST(0x0000A65500000000 AS DateTime)), (4, 1, 2000, CAST(0x0000A67400000000 AS DateTime)),
       (5, 2, 4000, CAST(0x0000A5DB00000000 AS DateTime)), (6, 2, 4000, CAST(0x0000A5F900000000 AS DateTime)),
       (7, 2, 4000, CAST(0x0000A61800000000 AS DateTime)), (8, 2, 4000, CAST(0x0000A63600000000 AS DateTime)),
       (9, 2, 4000, CAST(0x0000A65500000000 AS DateTime)), (10, 2, 4000, CAST(0x0000A67400000000 AS DateTime)),
       (11, 2, 4000, CAST(0x0000A69200000000 AS DateTime)), (12, 3, 6000, CAST(0x0000A65500000000 AS DateTime)),
       (13, 3, 6000, CAST(0x0000A67400000000 AS DateTime)), (14, 4, 8000, CAST(0x0000A7FF00000000 AS DateTime)),
       (15, 4, 8000, CAST(0x0000A98B00000000 AS DateTime))

CREATE TABLE [dbo].[Resign]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NULL,
    [ResignDate] [datetime] NULL,
    [Reason] [nchar](10) NULL
)

INSERT [dbo].[Resign] ([Id], [EmpId], [ResignDate], [Reason]) 
VALUES (1, 1, CAST(0x0000A69B00000000 AS DateTime), N'Resigned  '),
       (2, 2, CAST(0x0000A6C400000000 AS DateTime), N'Resigned')

所需的逐年输出如下:

Year 2015

Month - Jan - Feb - Mar - Apr - May - Jun - Jul - Aug - Sep - Oct - Nov - Dec
Opening - 10 - 10 - 10 - 12 - 12 - 12 - 12 - 12 - 12 - 12 - 8 - 8 //Total no. of employee
Add     -  0 -  0 -  2 -  0 -  0 -  0 -  0 -  0 -  0 -  0 - 0 - 0 //Newly added
Left    -  0 -  0 -  0 -  0 -  0 -  0 -  0 -  0 -  0 -  4 - 0 - 0 //Resigned
Closing - 10 - 10 - 12 - 12 - 12 - 12 - 12 - 12 - 12 -  8 - 8 - 8 //Closing Total

Year 2016

Month - Jan - Feb - Mar - Apr - May - Jun - Jul - Aug - Sep - Oct - Nov - Dec
Opening - 8 ----Goes on in the similar way
Add -----
Left ----
Closing ----

因此，数据应该如何在报告中显示就很清楚了。尽管我知道报告中按年份分组，并尝试类似地按月显示数据，如下所示:

SELECT 
    DATEPART(YYYY, Q.PayDate) [YEAR], 
    COUNT(m.EmpId) Total,
    SUM(CASE WHEN DATENAME(MONTH, m.EntryDate) = 'January' THEN 1 ELSE 0 END) [JAN],
    SUM(CASE WHEN DATENAME(MONTH, m.EntryDate) = 'February' THEN 1 ELSE 0 END) [FEB]  
FROM 
    Entry m 
INNER JOIN 
    Payment q ON Q.EmpId = M.EmpId 
LEFT JOIN
    Resign OUT ON OUT.EmpId = m.EmpId 
WHERE 
    m.EmpId NOT IN (SELECT DISTINCT p.EmpId FROM Resign p)
    AND m.EmpId IN (SELECT DISTINCT k.EmpId FROM Payment k)
GROUP BY 
    DATEPART(YYYY, Q.PayDate), DATEPART(MM, m.EntryDate);

上面的查询返回如下内容:

YEAR  Total JAN FEB
--------------------
2016  2     0   0
2017  1     0   0
2018  1     0   0

但我不确定如何在与给定示例相同的查询行中显示新添加的以及已辞职的用户？

注意:我对任何查询语言持开放态度。因此期待您的想法能够以适当的方式实现。

Answer 1

如果您计划运行其中许多报告工具，那么您绝对应该将精力转移到 SSRS 等报告工具上。它将使事情简化很多倍。话虽这么说，这是一种可以产生您上面提到的结果的方法。

DECLARE @Entry TABLE
(
    [EmpId] [int]  NOT NULL,
    [EmpName] [nvarchar](40) NULL,
    [Address] [nvarchar](100) NULL,
    [Email] [nvarchar](20) NULL,
    [EntryDate] [datetime] NULL
)

INSERT @Entry ([EmpId], [EmpName], [Address], [Email], [EntryDate]) 

VALUES
       (100, N'Early', N'On Earth', N'john@abc.com', '01/01/2015'),
       (1, N'John', N'On Earth', N'john@abc.com', CAST(0x0000A58000000000 AS DateTime)),
       (2, N'Jack', N'On Earth', N'jack@abc.com', CAST(0x0000A5A800000000 AS DateTime)),
       (3, N'Jessi', N'On Earth', N'jessi@abc.com', CAST(0x0000A5CF00000000 AS DateTime)),
       (4, N'Jackson', N'On Earth', N'jackson@abc.com', CAST(0x0000A5E400000000 AS DateTime))

DECLARE @Payment TABLE 
(
    [Id] [int]  NOT NULL,
    [EmpId] [int] NULL,
    [Payment] [float] NULL,
    [PayDate] [datetime] NULL
)

INSERT @Payment ([Id], [EmpId], [Payment], [PayDate]) 
VALUES (1, 1, 2000, CAST(0x0000A61800000000 AS DateTime)), (2, 1, 2000, CAST(0x0000A63600000000 AS DateTime)),
       (3, 1, 2000, CAST(0x0000A65500000000 AS DateTime)), (4, 1, 2000, CAST(0x0000A67400000000 AS DateTime)),
       (5, 2, 4000, CAST(0x0000A5DB00000000 AS DateTime)), (6, 2, 4000, CAST(0x0000A5F900000000 AS DateTime)),
       (7, 2, 4000, CAST(0x0000A61800000000 AS DateTime)), (8, 2, 4000, CAST(0x0000A63600000000 AS DateTime)),
       (9, 2, 4000, CAST(0x0000A65500000000 AS DateTime)), (10, 2, 4000, CAST(0x0000A67400000000 AS DateTime)),
       (11, 2, 4000, CAST(0x0000A69200000000 AS DateTime)), (12, 3, 6000, CAST(0x0000A65500000000 AS DateTime)),
       (13, 3, 6000, CAST(0x0000A67400000000 AS DateTime)), (14, 4, 8000, CAST(0x0000A7FF00000000 AS DateTime)),
       (15, 4, 8000, CAST(0x0000A98B00000000 AS DateTime))

DECLARE  @Resign TABLE
(
    [Id] [int]  NOT NULL,
    [EmpId] [int] NULL,
    [ResignDate] [datetime] NULL,
    [Reason] [nchar](10) NULL
)

INSERT @Resign ([Id], [EmpId], [ResignDate], [Reason]) 
VALUES (1, 1, CAST(0x0000A69B00000000 AS DateTime), N'Resigned  '),
       (2, 2, CAST(0x0000A6C400000000 AS DateTime), N'Resigned')

DECLARE @StartDate DATETIME = '01/01/2015'
DECLARE @EndDate DATETIME = '12/01/2016'

;WITH Calendar as
 (
    SELECT CalendarDate = @StartDate, CalendarYear = DATEPART(YEAR, @StartDate), CalendarMonth = DATEPART(MONTH, @StartDate)
    UNION ALL
    SELECT CalendarDate = DATEADD(MONTH, 1, CalendarDate),  CalendarYear = DATEPART(YEAR, CalendarDate), CalendarMonth = DATEPART(MONTH, CalendarDate)
    FROM Calendar
    WHERE DATEADD (MONTH, 1, CalendarDate) <= @EndDate
 )
 ,Employees AS
 (
     SELECT
        E.EmpID,
        StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, EntryDate), 0),
        EndDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, ResignDate), 0)
    FROM     
        @Entry E
        LEFT OUTER JOIN @Resign R ON R.EmpId = E.EmpId
)
,NormalizedAndUnpivoted AS
(
    SELECT
        *,
        CalendarMonth = DATEPART(MONTH,CalendarDate),
        CalendarYear = DATEPART(YEAR,CalendarDate),
        GroupField = CAST(DATEPART(MONTH,CalendarDate) AS NVARCHAR(50))+'_'+CAST(DATEPART(YEAR,CalendarDate) AS NVARCHAR(50)),
        SortOrder = CASE 
            WHEN Property='Opening' THEN 1
            WHEN Property='Addition'  THEN 2
            WHEN Property='Subtraction'  THEN 3
            WHEN Property='Total'  THEN 4
        END
    FROM
    (
        SELECT 
            C.CalendarDate,
            Opening =  COUNT(DISTINCT EmpExisting.EmpID) ,
            Addition = COUNT(DISTINCT EmpStarted.EmpID),
            Subtraction = COUNT(DISTINCT EmpEnd.EmpID),
            Total = COUNT(DISTINCT EmpExisting.EmpID) + COUNT(DISTINCT EmpStarted.EmpID) - COUNT(DISTINCT EmpEnd.EmpID)
        FROM 
            Calendar C
            LEFT OUTER JOIN Employees EmpExisting ON EmpExisting.StartDate < C.CalendarDate AND (EmpExisting.EndDate IS NULL OR EmpExisting.EndDate >=  C.CalendarDate)
            LEFT OUTER JOIN Employees EmpStarted ON EmpStarted.StartDate = C.CalendarDate
            LEFT OUTER JOIN Employees EmpEnd ON EmpEnd.EndDate = C.CalendarDate
        GROUP BY
            C.CalendarDate      
    )AS X
    UNPIVOT(
        PivotValue FOR Property IN (Opening, Addition, Subtraction, Total)
    ) AS U
)
,Pivoted AS
(
    SELECT
        CalendarYear,
        Property,
        SortOrder,
        Janurary=SUM(CASE WHEN CalendarMonth=1 THEN PivotValue ELSE NULL END),
        Feburary=SUM(CASE WHEN CalendarMonth=2 THEN PivotValue ELSE NULL END),
        March=SUM(CASE WHEN CalendarMonth=3 THEN PivotValue ELSE NULL END),
        April=SUM(CASE WHEN CalendarMonth=4 THEN PivotValue ELSE NULL END),
        May=SUM(CASE WHEN CalendarMonth=5 THEN PivotValue ELSE NULL END),
        June=SUM(CASE WHEN CalendarMonth=6 THEN PivotValue ELSE NULL END),
        July=SUM(CASE WHEN CalendarMonth=7 THEN PivotValue ELSE NULL END),
        August=SUM(CASE WHEN CalendarMonth=8 THEN PivotValue ELSE NULL END),
        September=SUM(CASE WHEN CalendarMonth=9 THEN PivotValue ELSE NULL END),
        October=SUM(CASE WHEN CalendarMonth=10 THEN PivotValue ELSE NULL END),
        November=SUM(CASE WHEN CalendarMonth=11 THEN PivotValue ELSE NULL END),
        December=SUM(CASE WHEN CalendarMonth=12 THEN PivotValue ELSE NULL END)
    FROM
        NormalizedAndUnpivoted
    GROUP BY
        CalendarYear,
        Property,
        SortOrder
)

SELECT * FROM Pivoted
ORDER BY
    CalendarYear,
    SortOrder
OPTION (MAXRECURSION 1000)

结果:

CalendarYear Property                       SortOrder   Janurary    Feburary    March       April       May         June        July        August      September   October     November    December
------------ ------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2015         Opening                        1           0           1           1           1           1           1           1           1           1           1           1           1
2015         Addition                       2           1           0           0           0           0           0           0           0           0           0           0           0
2015         Subtraction                    3           0           0           0           0           0           0           0           0           0           0           0           0
2015         Total                          4           1           1           1           1           1           1           1           1           1           1           1           1
2016         Opening                        1           1           2           3           4           5           5           5           5           5           5           4           3
2016         Addition                       2           1           1           1           1           0           0           0           0           0           0           0           0
2016         Subtraction                    3           0           0           0           0           0           0           0           0           0           1           1           0
2016         Total                          4           2           3           4           5           5           5           5           5           5           4           3           3