sql - 将计数追加为表列

Question

我有以下 SQL 查询:

SELECT Device, Value    
FROM tbTest 
WHERE Class LIKE 'OperatingSystem'  
AND Type LIKE 'Caption'     
ORDER BY Device 

结果如下表:

Device    Value
ITADMIN   Microsoft Windows Server 2016 Datacenter
NB6826    Microsoft Windows 10 Pro
TOPDESK   Microsoft Windows Server 2008 R2 Standard 
W1DC1     Microsoft Windows Server 2012 R2 Datacenter
W1DC2     Microsoft Windows Server 2012 R2 Datacenter
W1SRVSEP  Microsoft Windows Server 2008 R2 Standard 
W2DC1     Microsoft Windows Server 2008 R2 Standard 

我想要实现的是计算一行中某个字段的所有出现次数，并将其作为新列附加到每一行。所以基本上我需要一个输出这个的查询:

Device    Value                                        Count
ITADMIN   Microsoft Windows Server 2016 Datacenter     1
NB6826    Microsoft Windows 10 Pro                     1
TOPDESK   Microsoft Windows Server 2008 R2 Standard    3
W1DC1     Microsoft Windows Server 2012 R2 Datacenter  2
W1DC2     Microsoft Windows Server 2012 R2 Datacenter  2
W1SRVSEP  Microsoft Windows Server 2008 R2 Standard    3
W2DC1     Microsoft Windows Server 2008 R2 Standard    3

有什么办法吗？我所有的尝试都导致了无法处理的查询或无法使用的输出。任何帮助将不胜感激!

Answer 1

您需要一个窗口计数函数，它将计算值列中每个不同值的记录数

   SELECT Device,
           Value,
           COUNT(value) OVER (PARTITION BY value)
    FROM tbTest 
    WHERE Class LIKE 'OperatingSystem'  
    AND Type LIKE 'Caption'     
    ORDER BY Device