多左连接SQL查询中如何统计所有行数？

你的SQL查询无法返回结果的核心原因是：SELECT子句中同时包含聚合函数COUNT(*)和大量非聚合列，但未指定GROUP BY子句，这违反了SQL的聚合规则（多数数据库会直接抛出语法错误）。

根据你的需求，分三种常见场景给出修改方案：

场景1：仅统计关联后的总行数 #

如果只需要获取所有符合关联条件的记录总数，直接保留COUNT(*)并移除其他列即可：

$sqlQuery = "SELECT COUNT(*) AS TotalRows
FROM
  tblCampaignLists
  LEFT JOIN tblClients ON tblClients.ClientId = tblCampaignLists.ClientId
  LEFT JOIN tblCampaigns ON tblCampaigns.CampaignId = tblCampaignLists.CampaignId
  LEFT JOIN tblCampaignFilters ON tblCampaignFilters.FilterId = tblCampaignLists.CampaignFilter";

执行后会返回一个名为TotalRows的字段，值为所有关联记录的行数。

场景2：保留所有列，同时显示总行数 #

如果需要获取每条记录的完整数据，同时在每行显示整个结果集的总行数，使用窗口函数COUNT(*) OVER()：

$sqlQuery = "SELECT 
  COUNT(*) OVER() AS TotalRows,
  tblCampaignLists.CampaignListId,
  tblCampaignLists.ClientId,
  tblCampaignLists.CampaignId,
  tblCampaignLists.CampaignFilter,
  tblClients.ClientName,
  tblClients.ClientState,
  tblClients.ClientCreationDate,
  tblClients.ClientEmail, 
  tblClients.ClientAddressCounty,
  tblCampaigns.CampaignName,
  tblCampaigns.CampaignDescription,
  tblCampaigns.OrganisationId,
  tblCampaignFilters.FilterId,
  tblCampaignFilters.Section,
  tblCampaignFilters.TitleIds,
  tblCampaignFilters.FeatureIds,
  tblCampaignFilters.EditionIds,
  tblCampaignFilters.NotInEditionId
FROM
  tblCampaignLists
  LEFT JOIN tblClients ON tblClients.ClientId = tblCampaignLists.ClientId
  LEFT JOIN tblCampaigns ON tblCampaigns.CampaignId = tblCampaignLists.CampaignId
  LEFT JOIN tblCampaignFilters ON tblCampaignFilters.FilterId = tblCampaignLists.CampaignFilter";

窗口函数会计算整个结果集的行数，将其作为额外字段添加到每一行中。

场景3：按指定维度分组统计行数 #

如果需要按某个/某些字段分组（比如按客户ID统计每个客户的相关记录数），则需要添加GROUP BY子句，将所有非聚合列放入GROUP BY中：

$sqlQuery = "SELECT 
  tblCampaignLists.ClientId,
  tblClients.ClientName,
  COUNT(*) AS ClientRowCount
FROM
  tblCampaignLists
  LEFT JOIN tblClients ON tblClients.ClientId = tblCampaignLists.ClientId
  LEFT JOIN tblCampaigns ON tblCampaigns.CampaignId = tblCampaignLists.CampaignId
  LEFT JOIN tblCampaignFilters ON tblCampaignFilters.FilterId = tblCampaignLists.CampaignFilter
GROUP BY tblCampaignLists.ClientId, tblClients.ClientName";

执行后会返回每个客户对应的记录数。

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