将mysql设置为shell(Setting mysql as shell)

 另一种方法是编写一个按RefProject分组的子查询，完成总和然后加入你的表。 
 
SELECT x.RefProject, x.ProjectName, x.DateCreated, x.PlannedDueDate, T.Cnt
FROM Projects AS x
LEFT JOIN (   SELECT RefProject, COUNT(RefUser) AS Cnt
              FROM ProjectUser
              GROUP BY RefProject) AS T
    ON T.RefProject = x.RefProject
LEFT JOIN ProjectUsers AS a
    ON x.RefProject = a.RefProject
WHERE a.RefUser = 1;
 
 使用标量函数或多语句表值函数将会降低性能，这个和你的查询都很好，只需很好地索引表，这样就可以了。 
 
 或者，如果分组成为问题，您可以创建索引视图并与其连接。 这会增加一些写入开销，但会对读取性能产生奇迹。 
 
CREATE VIEW RefProjectCountView
WITH SCHEMABINDING
AS
SELECT RefProject, COUNT_BIG(RefUser) AS Cnt
FROM ProjectUser
GROUP BY RefProject;
GO

CREATE UNIQUE CLUSTERED INDEX PK_RefProjectCountView
ON RefProjectCountView (RefProject);
GO

SELECT x.RefProject, x.ProjectName, x.DateCreated, x.PlannedDueDate, T.Cnt
FROM Projects AS x
LEFT JOIN RefProjectCountView AS T WITH(NOEXPAND)
    ON T.RefProject = x.RefProject
LEFT JOIN ProjectUsers AS a
    ON x.RefProject = a.RefProject
WHERE a.RefUser = 1;
 
 SQL Server需要WITH(NOEXPAND)提示来查询视图索引，而不是展开完整的select语句。 

Alternative would be to write a sub-query that groups by RefProject, does the sum and then joins your table.
 
SELECT x.RefProject, x.ProjectName, x.DateCreated, x.PlannedDueDate, T.Cnt
FROM Projects AS x
LEFT JOIN (   SELECT RefProject, COUNT(RefUser) AS Cnt
              FROM ProjectUser
              GROUP BY RefProject) AS T
    ON T.RefProject = x.RefProject
LEFT JOIN ProjectUsers AS a
    ON x.RefProject = a.RefProject
WHERE a.RefUser = 1;
 
Using a scalar function or multi-statement table valued function will draw performance down, both this and your query are fine, just index tables well and this is going to work alright.
 
Alternatively, if grouping becomes a problem, you can create an indexed view and join with it. This adds some write overhead, but does miracles on read performance.
 
CREATE VIEW RefProjectCountView
WITH SCHEMABINDING
AS
SELECT RefProject, COUNT_BIG(RefUser) AS Cnt
FROM ProjectUser
GROUP BY RefProject;
GO

CREATE UNIQUE CLUSTERED INDEX PK_RefProjectCountView
ON RefProjectCountView (RefProject);
GO

SELECT x.RefProject, x.ProjectName, x.DateCreated, x.PlannedDueDate, T.Cnt
FROM Projects AS x
LEFT JOIN RefProjectCountView AS T WITH(NOEXPAND)
    ON T.RefProject = x.RefProject
LEFT JOIN ProjectUsers AS a
    ON x.RefProject = a.RefProject
WHERE a.RefUser = 1;
 
WITH(NOEXPAND) hint is needed for SQL Server to query view index and not to expand the full select statement.