时间戳

首页 > 解决方案 > 创建用户计数功能

sql - 创建用户计数功能

问题描述

我得到了下表的视图：

SELECT count(Incident.ID_Incident) 
    FROM Incident WHERE Incident.End_User = 7
        AND Incident.Date BETWEEN '2018-12-01 07:54:44' AND '2018-12-18 00:00:00';
GO

桌子：

CREATE TABLE Incident(
ID_Incident INT         IDENTITY(1,1)   NOT NULL,
Date        DATETIME                    NULL,
End_User    INT                         NOT NULL,

以下是一些表值：

('2018-12-06 12:33:05', 003),
('2018-12-13 14:32:12', 010),
('2018-12-06 01:26:03', 015),
('2018-12-04 05:38:50', 012),
('2018-12-10 19:13:08', 011),
('2018-12-12 13:28:39', 005),
('2018-12-05 11:57:29', 006),
('2018-12-17 15:28:31', 007),
('2018-12-07 01:39:22', 007),
('2018-12-12 17:01:47', 009),
('2018-12-03 09:54:26', 010);

我需要把那个视图变成一个函数。我不知道为什么它不起作用。

任何建议都会很棒。

我写了这个：”

CREATE FUNCTION Total_case (@ID_End_User INT)
    RETURNS INT AS
        BEGIN
            DECLARE @ID_End_User_Search     INT

            SELECT @ID_End_User_Search = COUNT(ID_Incident)
                FROM Incident i
                    WHERE i.End_User = @ID_End_User_Search 
                        AND i.Date BETWEEN '2018-12-13 14:32:12' AND '2018-12-18 00:00:00'
                RETURN (@ID_End_User_Search)
            END

GO


DECLARE @SearchValue INT;

EXEC @SearchValue = [dbo].Total_case 
    @ID_End_User = 7;
SELECT @SearchValue AS 'Total case per user.'
GO

编辑：更改标签并添加表格

标签： sqlsql-serverfunctioncount

解决方案

问题是您使用了在 WHERE 子句中的函数中创建的变量进行过滤。它已经是空的，因此您收到 0 结果。更改WHERE i.End_User = @ID_End_User_Search为WHERE i.End_User = @ID_End_User然后您将看到结果。这也是因为您在函数中使用了静态日期范围的示例吗？如果否，这不是有效的功能，您需要在每次需要更改日期范围时修改您的功能。

这是更正的功能：

CREATE FUNCTION Total_case (@ID_End_User INT)
    RETURNS INT AS
        BEGIN
            DECLARE @ID_End_User_Search     INT

            SELECT @ID_End_User_Search = COUNT(ID_Incident)
                FROM Incident i
                    WHERE i.End_User = @ID_End_User
                        AND i.Date BETWEEN '2018-12-13 14:32:12' AND '2018-12-18 00:00:00'
                RETURN (@ID_End_User_Search)
            END

GO

这是创建函数的更好方法：

ALTER FUNCTION Total_case (@ID_End_User INT, @DateFrom DATETIME, @DateTo DATETIME)
    RETURNS INT AS
        BEGIN
            DECLARE @ID_End_User_Search     INT

            SELECT @ID_End_User_Search = COUNT(ID_Incident)
                FROM Incident i
                    WHERE i.End_User = @ID_End_User 
                        AND i.Date BETWEEN @DateFrom AND @DateTo
                RETURN (@ID_End_User_Search)
            END

GO

仅为一位用户选择：

SELECT DBO.Total_case(7,'2018-12-01 14:32:12' ,'2018-12-19 00:00:00')

为所有用户选择：

SELECT *, DBO.Total_case(END_USER,'2018-12-01 14:32:12' ,'2018-12-19 00:00:00') TotalIncidents
FROM    (SELECT DISTINCT End_User 
        FROM Incident ) I

推荐阅读