sql - 使用 SQL Server 存储过程构建报告

Question

构建显示来自多个表的数据并且可以按某些字段(例如按日期)搜索的报告的最佳做法是什么？

报告结构如下:

                Today    Yesterday  Last 7 days     All
New Users         0         10        20           1000
Sold Products     11        21        31           25000

我能想到的只是用存储过程和输出参数构建报告，它工作得很好，但无论是在 SQL Server 端还是在应用程序中，这都是一项繁琐的工作。我只是不知道还有什么其他方法可以做到这一点。

也许可以通过使用游标或使用不同的存储过程或其他一些技术来完成。你能帮我找到更好的方法吗？感谢您的帮助。

这是我目前的做法

CREATE PROCEDURE sp_Statistics
    @DateFrom datetime = NULL, 
    @DateTo datetime = NULL,

    @UsersRegisteredToday int = 0 OUTPUT,
    @UsersRegisteredYesterday int = 0 OUTPUT, 
    @UsersRegisteredLastSevenDays int = 0 OUTPUT,
    @UsersRegisteredTotal int = 0 OUTPUT,
    @UsersRegisteredDate int = 0 OUTPUT,

    @SoldProductsToday int = 0 OUTPUT,
    @SoldProductsYesterday int = 0 OUTPUT, 
    @SoldProductsLastSevenDays int = 0 OUTPUT,
    @SoldProductsTotal int = 0 OUTPUT,
    @SoldProductsDate int = 0 OUTPUT
AS
BEGIN

--------------------- REGISTERED USERS -----------------------
IF (@DateFrom IS NOT NULL OR @DateTo IS NOT NULL)
    BEGIN
        SELECT @UsersRegisteredDate = COUNT(*)
        FROM [User] u
        WHERE DATEADD(DAY, DATEDIFF(D, 0, u.Date), 0) BETWEEN @DateFrom AND @DateTo
    END
ELSE IF @DateFrom IS NULL AND @DateTo IS NULL
    BEGIN
        SELECT @UsersRegisteredToday = COUNT(DISTINCT u.idUser)
        FROM [User] u
        LEFT JOIN [Order] e ON u.idUser = e.idUser
        WHERE DATEDIFF(d, GETDATE(), u.Date) = 0

        SELECT @UtilizadoresYesterday = COUNT(DISTINCT u.idUser)
        FROM [User] u
        LEFT JOIN [Order] e ON u.idUser = e.idUser
        WHERE DATEDIFF(d, GETDATE(), u.Date) = -1

        SELECT @UsersRegisteredLastSevenDays = COUNT(DISTINCT u.idUser)
        FROM [User] u
        LEFT JOIN [Order] e ON u.idUser = e.idUser
        WHERE DATEDIFF(d, GETDATE(), u.Date) >= -7

        SELECT @UsersRegisteredTotal = COUNT(DISTINCT u.idUser)
        FROM [User] u
        LEFT JOIN [Order] e ON u.idUser = e.idUser
    END

--------------------- SOLD PRODUCTS --------------------------
IF (@DateFrom IS NOT NULL OR @DateTo IS NOT NULL)
    BEGIN
        SELECT @SoldProductsDate = COUNT(*)
        FROM [Order] e
        WHERE e.idPaymentState = 2
              AND
              DATEADD(DAY, DATEDIFF(D, 0, e.Date), 0) BETWEEN @DateFrom AND @DateTo
    END
ELSE IF @DateFrom IS NULL AND @DateTo IS NULL
    BEGIN

        SELECT @SoldProductsToday = COUNT(*) 
        FROM [Order] e 
        LEFT JOIN [User] u ON e.idUser = u.idUser
        WHERE e.idPaymentState = 2 AND DATEDIFF(d, GETDATE(), e.Date) = 0

        SELECT @SoldProductsYesterday = COUNT(*)
        FROM [Order] e
        LEFT JOIN [User] u ON e.idUser = u.idUser
        WHERE e.idPaymentState = 2 AND DATEDIFF(d, GETDATE(), e.Date) = -1

        SELECT @SoldProductsLastSevenDays = COUNT(*)
        FROM [Order] e
        LEFT JOIN [User] u ON e.idUser = u.idUser
        WHERE e.idPaymentState = 2 AND DATEDIFF(d, GETDATE(), e.Date) >= -7

        SELECT @SoldProductsTotal = COUNT(*)
        FROM [Order] e
        LEFT JOIN [User] u ON e.idUser = u.idUser
        WHERE e.idPaymentState = 2
    END
END

Answer 1

我不会使用输出参数，而是在 sp 中创建一个临时表并将数据插入其中，然后在所有数据都存在后对其进行选择。

如果您在应用程序中使用网格或如 Charleh 所建议的使用 SSRS 的更好选择，则创建报告本身应该相当容易。然后，您可以将 SSRS 报告查看器嵌入到您的应用程序中。