代码之家 › 专栏 › 技术社区 › Kiran Shahi Jonny

在带有count()函数的sql server中,如何将单个列检索为两列?

sql-server-2008 sql-server sql

Kiran Shahi Jonny · 技术社区 · 7 年前

我有下面的桌子

ID          UserID      Email                           Status          Count
1           16          kiran.shahi@example.com         True            2
2           16          aibrahim@sbcglobal.net          False           3
3           16          russotto@icloud.com             False           3
4           16          seurat@optonline.net            False           3
5           16          paley@aol.com                   False           3
6           16          tmaek@aol.com                   False           3
7           16          baveja@verizon.net              False           3
8           16          wonderkid@mac.com               False           3
9           16          mkearl@live.com                 False           3
10          16          solomon@att.net                 False           3
11          16          enintend@gmail.com              False           3
12          16          rbarreira@outlook.com           False           3
13          16          pavel@yahoo.ca                  False           3
NULL        NULL        NULL                            NULL            NULL

并遵循存储过程

    @UserID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
    @UserID AS UserID, 
    COUNT(T1.Status) AS Active, 
    COUNT(T2.Status) AS Inavtive 
    FROM UserStatus T1
    JOIN UserStatus T2
    ON T1.UserID = T2.UserID
    WHERE T1.Status = 1 AND T2.Status = 0 AND T2.UserID = @UserID  
END

又回来了

    UserID      Active          Inavtive
1   16          12              12

我怎样才能达到

    UserID      Active          Inavtive
1   16          1               12

3 回复 | 直到 7 年前

Jayasurya Satheesh 7 年前

而不是 COUNT 尝试使用 SUM . 这样地

SELECT 
        @UserID AS UserID, 
        SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) AS Active, 
        SUM(CASE WHEN Status = 0 THEN 1 ELSE 0 END) AS Inavtive 
    FROM UserStatus 
    WHERE 
    UserID = @UserID

Hoang Tran 7 年前

您可以使用以下总和:

SELECT 
    SUM(CASE WHEN Status =1 THEN 1 ELSE 0 end) as Active, 
    SUM(CASE WHEN Status =1 THEN 0 ELSE 1 end) as InActive 
FROM UserStatus 
    WHERE 
    UserID = @UserID

Kiran Shahi Jonny 7 年前

我试着在里面实施这个案子 count() 而不是 sum() 功能是因为 求和() 如果没有记录,则函数返回null 计数() 返回有助于在我的代码中进行计算的值。下面的代码运行良好。就我而言 Status 列必须是 bit 这样我的条件就满足了 true 和 false 记录。

@UserID INT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT 
            @UserID AS UserID, 
            COUNT(CASE WHEN Status = 1 THEN ID END) AS Active, 
            COUNT(CASE WHEN Status = 0 THEN ID END) AS Inavtive 
        FROM UserStatus 
        WHERE 
        UserID = @UserID 
    END