SQL Return Table with where 子句和總表值的出現

我很難弄清楚如何實現這一目標。我知道如何在 C# 中實現它，但不知道 SQL。

假設我有下表：

我想返回整個表，並用一個額外的列顯示routeID按名稱的總出現次數，所以where name='Bob'看起來像：

但是，如果我寫類似

declare @ct as nvarchar(5)
set @ct = (SELECT COUNT(RouteId) from <table>)

select *, @ct
from <table>
where name = 'Bob'

我得到了所有路線 ID 的總數，而不僅僅是行中顯示的那個。

我嘗試查看計算列，但據我所知，它不支持這種類型的查詢。

誰能指出我正確的方向？

COUNT您可以在派生表（子查詢）中使用視窗化

SELECT *
FROM (
   SELECT *,
     Total = COUNT(*) OVER (PARTITION BY t.RouteId)
   FROM MyTable t
) t
WHERE t.Name = 'Bob';

db<>小提琴

另外三種選擇：

子查詢

SELECT 
   MT.*,
   Total =
       (
           SELECT COUNT_BIG(*) 
           FROM dbo.MyTable AS MT2
           WHERE
               MT2.RouteId = MT.RouteId
       )
FROM dbo.MyTable AS MT
WHERE 
   MT.[Name] = 'Bob';

申請

SELECT 
   MT.*,
   A.Total
FROM dbo.MyTable AS MT
CROSS APPLY 
(
       SELECT Total = COUNT_BIG(*) 
       FROM dbo.MyTable AS MT2
       WHERE MT2.RouteId = MT.RouteID
       GROUP BY ()
) AS A
WHERE 
   MT.[Name] = 'Bob';

加入

SELECT 
   MT.*, 
   T.Total 
FROM dbo.MyTable AS MT
JOIN
(
   SELECT 
       MT2.RouteId, 
       Total = COUNT_BIG(*)
   FROM dbo.MyTable AS MT2
   GROUP BY MT2.RouteId
) AS T
   ON T.RouteId = MT.RouteId
WHERE
   MT.[Name] = 'Bob';

db<>fiddle線上展示

引用自：https://dba.stackexchange.com/questions/313941