sql-server - 如何计算每个 GroupBy 行(SQL Server)中包含多少行 "rolled up"？

Question

我有这个问题:

SELECT TOP 8 M.membername, 
             P.MemberNo
FROM   prosql05.bidata.dbo.priceexceptionhistorycsr P 
       JOIN prosql05.cpsdata.dbo.members M 
         ON P.memberno = M.memberno 
WHERE  invoicedate BETWEEN '2016-09-04' AND '2016-09-10' 
ORDER  BY invoicedate

...给我的数据结果如下:

我有这个查询:

SELECT TOP 8 M.membername, 
             P.MemberNo
FROM   dbo.priceexceptionhistorycsr P 
       JOIN cpsdata.dbo.members M 
         ON P.memberno = M.memberno 
WHERE  invoicedate BETWEEN '2016-09-04' AND '2016-09-10' 
and unit = 'FOODBUY'
GROUP BY M.membername, P.Memberno
order by m.membername

...将成员名称组合在一起，以便我得到:

我需要计算每个分组行中代表的行数； IOW，对于上面的数据，我需要查看:

MemberName  MemberNo    Count
----------  --------    -----
Freedom Fresh   110     1
FSA Loveland    028     3

我怎样才能得到它？

Answer 1

只需在Select中添加Count聚合即可

select MemberName, MemberNo, count(1) as [Count]
From 
(
--your existing query without Group by
) A
Group by MemberName, MemberNo 

也许可以这样简化

SELECT TOP 8 M.membername, 
             M.MemberNo,
             Count(1) as [Count]
FROM   dbo.priceexceptionhistorycsr P 
       JOIN cpsdata.dbo.members M 
         ON P.memberno = M.memberno 
WHERE  invoicedate BETWEEN '2016-09-04' AND '2016-09-10' 
and unit = 'FOODBUY'
GROUP BY M.membername, M.Memberno
order by m.membername

为了进一步提高性能，我们可以添加一个非聚集索引

CREATE NONCLUSTERED INDEX IX_members_invoicedate_unit 
    ON cpsdata.dbo.members (invoicedate,unit) include (membername,MemberNo)

Index 上面考虑了 invoicedate，unit 列来自 cpsdata.dbo.members 表。如果不是，请澄清以进行必要的更改