获得状态计数有许多状态项

问题描述：

DECLARE @DevicesAndStatuses TABLE (Id BIGINT,[Status] INT); 

DECLARE @myId BIGINT; 
SET @myId = 1;

里面上面的表格，我可以有成千上万的IDS（ID可以是重复的）和状态1-50范围。获得某个特定ID所有状态的最有效方法是什么？

，我有传统的方法如下：

SELECT 
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 1) AS Status1, 
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 2) AS Status2, 
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 3) AS Status3, 
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 4) AS Status4, 
... 
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 50) AS Status50, 

FROM @DevicesAndStatuses WHERE Id = @myId

是否有可能为让所有的状态[1-50]的计数特定ID的任何更好的解决方案？

最终的结果应该是包含50列显示的每一个状态作为状态1，状态2，...，Status50 *计数（）

答

当然单行：

SELECT Status, COUNT(*) 
FROM @DevicesAndStatuses 
WHERE Id = @myId  
GROUP BY Status

这返回所有Status值为Id = @myId，并且它们的计数 - 在一个简单语句中

我想要的结果为每地位状态1，状态2，... COUNT（*），Status50 – MHOOS

答

我的第一个建议是使用group by：

SELECT status, count(*) 
FROM @DevicesAndStatuses 
WHERE Id = @myId 
GROUP BY status;

获取所需信息的最简单方法，但是在多行中。

如果你想多列，然后使用条件汇总：

SELECT SUM(CASE WHEN [Status] = 1 THEN 1 ELSE 0 END) AS Status1, 
     SUM(CASE WHEN [Status] = 2 THEN 1 ELSE 0 END) AS Status2, 
     SUM(CASE WHEN [Status] = 3 THEN 1 ELSE 0 END) AS Status3, 
     SUM(CASE WHEN [Status] = 4 THEN 1 ELSE 0 END) AS Status4, 
     . . . 
FROM @DevicesAndStatuses 
WHERE Id = @myId

我想结果的单排50列：状态1，状态2，...，status50 – MHOOS

这将做到这一点....填写其他46列的状态，你将全部设置。 –

答

在这里你去

SELECT MAX(id) AS Id, status, COUNT(*) 
    FROM @DevicesAndStatuses 
WHERE Id = @myId 
GROUP BY status;

或

SELECT id AS Id, status, COUNT(*) 
    FROM @DevicesAndStatuses 
WHERE Id = @myId 
GROUP BY id,status;

是的Prabhat，我非常想念在Group by中加入id。按照我的理解，他希望看到身份和身份以及相关数量。所以应该是 – Venkat

SELECT id AS ID，状态，COUNT（*） FROM DevicesAndStatuses WHERE Id = myId GROUP BY id，status; – Venkat

答

你需要使用Dynamic Pivot Query实现这个：

我已经用一个通用的例子来完成它，但是如果你需要一个更具体的版本，可以戳我。不过，您需要使用Temp Table而不是Table Variable。

STUFF命令用于从字符串的开头删除,。

CREATE TABLE #Items 
(
    Item INT IDENTITY(1,1), 
    [Status] INT 
) 

INSERT #Items 
(Status) 
VALUES 
(1),(1),(1),(1),(1),(1),(1),(2),(2),(2),(2),(3),(3),(4),(4),(4),(4),(4),(4),(4),(4),(5); 


DECLARE @StatusList NVARCHAR(MAX) = N'', 
     @SumSelector NVARCHAR(MAX) = N'' 

SELECT @StatusList = CONCAT(@StatusList, N',', QUOTENAME(s.Status)), 
     @SumSelector = CONCAT(@SumSelector, N',', N'SUM(', QUOTENAME(s.Status), N') AS Status_', s.Status) 
     FROM (SELECT DISTINCT [Status] FROM #Items) AS s 

SELECT @StatusList = STUFF(@StatusList, 1, 1, N''), 
     @SumSelector = STUFF(@SumSelector, 1, 1, N'') 

DECLARE @StatusPivotQuery NVARCHAR(MAX) = CONCAT(N' 

    SELECT ', @SumSelector, N' 
    FROM #Items AS s 
    PIVOT 
    (
     COUNT(s.[Status]) 
     FOR s.[Status] IN(', @StatusList, N') 
    ) AS pvt ') 

EXEC sys.sp_executesql @StatusPivotQuery 

DROP TABLE #Items

获得状态计数有许多状态项

相关推荐