含UNION的SQL查询/存储过程总行数统计（COUNT(*)报错）

我来帮你搞定这个统计问题！你遇到的报错大概率是因为原查询里包含了ORDER BY子句——当把带ORDER BY的查询作为子查询套在COUNT(*)外面时，SQL Server不允许未加TOP/OFFSET的子查询带有排序（因为子查询返回的是集合，排序对集合本身没有意义）。下面给你几种可行的解决方法：

方法一：直接统计总行数（最简方案） #

把你的整个查询去掉最后的order by ProjectID desc作为子查询，外层用COUNT(*)统计即可：

SELECT COUNT(*) AS TotalRows
FROM (
    SELECT ProjectName, ProjectReference, ProjectID, SiteSupervisor, PrincipalContractor, PrincipalDesigner 
    FROM CompanyProjects 
    WHERE EndDate < GETDATE() 
    UNION 
    (
        SELECT CompanyProjects.ProjectName AS ProjectName, CompanyProjects.ProjectReference AS ProjectReference, 
               CompanyProjects.ProjectID AS ProjectID, CompanyProjects.SiteSupervisor AS SiteSupervisor, 
               CompanyProjects.PrincipalContractor AS PrincipalContractor, CompanyProjects.PrincipalDesigner AS PrincipalDesigner 
        FROM CompanyProjects 
        INNER JOIN CompanyCurrentReview ON companyprojects.ProjectID = CompanyCurrentReview.ReviewProjectId 
            AND CompanyCurrentReview.IsReviewComplete = 0 
            AND CompanyCurrentReview.DateTime BETWEEN DATEADD(YEAR, -2, GETDATE()) AND GETDATE() 
            AND ProjectID NOT IN (SELECT ProjectID FROM CompanyProjects WHERE EndDate < GETDATE())
    ) 
    UNION 
    (
        SELECT ProjectName, ProjectReference, ProjectID, SiteSupervisor, PrincipalContractor, PrincipalDesigner 
        FROM CompanyProjects 
        WHERE CreatedDate BETWEEN DATEADD(YEAR, -2, GETDATE()) AND GETDATE() 
            AND ProjectID NOT IN (
                (SELECT CompanyProjects.ProjectID 
                 FROM CompanyProjects 
                 INNER JOIN CompanyCurrentReview ON companyprojects.ProjectID = CompanyCurrentReview.ReviewProjectId 
                     AND CompanyCurrentReview.IsReviewComplete = 0 
                     AND CompanyCurrentReview.DateTime between DateAdd(year,-2,GETDATE() ) AND GETDATE()) 
                UNION 
                SELECT ProjectID FROM CompanyProjects WHERE EndDate < GETDATE()
            )
    )
) AS result;

方法二：同时返回结果和总行数 #

如果你既要拿到原查询的排序结果，又要知道总行数，可以用COUNT(*) OVER()窗口函数，这样每一行数据都会附带总行数：

SELECT *, COUNT(*) OVER() AS TotalRows
FROM (
    SELECT ProjectName, ProjectReference, ProjectID, SiteSupervisor, PrincipalContractor, PrincipalDesigner 
    FROM CompanyProjects 
    WHERE EndDate < GETDATE() 
    UNION 
    (
        SELECT CompanyProjects.ProjectName AS ProjectName, CompanyProjects.ProjectReference AS ProjectReference, 
               CompanyProjects.ProjectID AS ProjectID, CompanyProjects.SiteSupervisor AS SiteSupervisor, 
               CompanyProjects.PrincipalContractor AS PrincipalContractor, CompanyProjects.PrincipalDesigner AS PrincipalDesigner 
        FROM CompanyProjects 
        INNER JOIN CompanyCurrentReview ON companyprojects.ProjectID = CompanyCurrentReview.ReviewProjectId 
            AND CompanyCurrentReview.IsReviewComplete = 0 
            AND CompanyCurrentReview.DateTime BETWEEN DATEADD(YEAR, -2, GETDATE()) AND GETDATE() 
            AND ProjectID NOT IN (SELECT ProjectID FROM CompanyProjects WHERE EndDate < GETDATE())
    ) 
    UNION 
    (
        SELECT ProjectName, ProjectReference, ProjectID, SiteSupervisor, PrincipalContractor, PrincipalDesigner 
        FROM CompanyProjects 
        WHERE CreatedDate BETWEEN DATEADD(YEAR, -2, GETDATE()) AND GETDATE() 
            AND ProjectID NOT IN (
                (SELECT CompanyProjects.ProjectID 
                 FROM CompanyProjects 
                 INNER JOIN CompanyCurrentReview ON companyprojects.ProjectID = CompanyCurrentReview.ReviewProjectId 
                     AND CompanyCurrentReview.IsReviewComplete = 0 
                     AND CompanyCurrentReview.DateTime between DateAdd(year,-2,GETDATE() ) AND GETDATE()) 
                UNION 
                SELECT ProjectID FROM CompanyProjects WHERE EndDate < GETDATE()
            )
    )
) AS result 
ORDER BY ProjectID desc;

针对含UNION的存储过程的统计方法 #

如果是存储过程里的UNION逻辑需要统计行数，你可以用这两种方式：

• 方式一：临时表中转统计
  先把存储过程的结果插入临时表，再对临时表执行COUNT：

  CREATE TABLE #TempResults (
      ProjectName VARCHAR(255),
      ProjectReference VARCHAR(255),
      ProjectID INT,
      SiteSupervisor VARCHAR(255),
      PrincipalContractor VARCHAR(255),
      PrincipalDesigner VARCHAR(255)
  );
  
  INSERT INTO #TempResults
  EXEC YourStoredProcedureName; -- 替换成你的存储过程名
  
  SELECT COUNT(*) AS TotalRows FROM #TempResults;
  
  DROP TABLE #TempResults;
  

• 方式二：修改存储过程返回统计数
  在存储过程里先统计行数，再返回原结果，比如：

  CREATE PROCEDURE YourStoredProcedureName
  AS
  BEGIN
      -- 先返回总行数
      SELECT COUNT(*) AS TotalRows
      FROM (
          -- 这里放入你的UNION查询内容（去掉ORDER BY）
      ) AS result;
  
      -- 再返回原查询的排序结果
      SELECT ProjectName, ProjectReference, ProjectID, SiteSupervisor, PrincipalContractor, PrincipalDesigner 
      FROM (
          -- 这里放入你的UNION查询内容
      ) AS result 
      ORDER BY ProjectID desc;
  END
  

内容的提问来源于stack exchange，提问作者assaduzzaman samrat