SQL视图如何按组和求和

Question

我有一个条目表和一个成员表，我想根据成员名称总结所有的条目。我已经创建了一个视图来完成这个任务，但我在尝试正确使用语法时遇到了困难。

CREATE VIEW [dbo].[Members_View] AS
  SELECT Members.ID, Members.Name, Members.Email, 
  (SELECT COUNT(*) WHERE AssignedTo = Members.Name) as ECount
  From Members JOIN dbo.Entries ON Members.[Name] = Entries.[AssignedTo]
  Group By
    Name,
    Members.ID,
    Members.Email,
    Entries.AssignedTo

如果我去掉分组，每个条目中的 ECount 列仅显示数字 1，但是相同名称的多个条目都会被列出。一旦进行分组，同名项仅显示一个，但是每个条目仍然只会有 1 个计数。如何进行分组并求和？

- mitchellJ

Answer 1

我认为你可以只进行简单的聚合查询，省略Entries.AssignedTo：

CREATE VIEW [dbo].[Members_View] AS
  SELECT Members.ID, Members.Name, Members.Email, 
         COUNT(*) as ECount
  From Members JOIN
       dbo.Entries
       ON Members.[Name] = Entries.[AssignedTo]
  Group By
    Name,
    Members.ID,
    Members.Email;

- Gordon Linoff

---

谢谢！这正是我所需要的。 - mitchellJ

Answer 2

你可以在子查询中计算条目数，然后将其与主表连接。

CREATE VIEW [dbo].[Members_View] AS 
SELECT Members.ID, Members.Name, Members.Email, entries.ECount
    FROM Members
    JOIN (SELECT AssignedTo, COUNT(*) AS ECount FROM Entries GROUP BY AssignedTo) entries ON entries.AssignedTo = Members.Name

- Danny