SQL:返回包含具有相同值的行数的列

Question

我有一个查询从我的数据库中返回系统和区域，如下所示:

SELECT  Areas.ID AreaID,
    Areas.Name AreaName,
              Systems.*

FROM Systems
INNER JOIN Areas ON Areas.ID = Systems.AreaID
WHERE ....

这会返回如下所示的数据:

|  AreaID   |  AreaName   | SystemName  | ...
|     1     |    area1    |     sys1    |
|     1     |    area1    |     sys2    |
|     1     |    area1    |     sys3    |
|     1     |    area1    |     sys4    |
|     2     |    area2    |     sys5    |
|     2     |    area2    |     sys6    |

我想返回一个附加列，其中包含返回的每个区域中的系统数量，因此我最终得到如下内容:

|  AreaID   |  AreaName   | SystemName  |  noOfSystems  | ...
|     1     |    area1    |     sys1    |       4       |
|     1     |    area1    |     sys2    |       4       |
|     1     |    area1    |     sys3    |       4       |
|     1     |    area1    |     sys4    |       4       |
|     2     |    area2    |     sys5    |       2       |
|     2     |    area2    |     sys6    |       2       |

即有4个系统，area id为1和2，area id为2。

如何做到这一点？我确定我听说过可以执行此操作的内置函数，但我找不到我想要的东西。

Answer 1

您需要使用聚合函数 COUNT()，然后使用 GROUP BY。这可以在相关子查询中完成:

SELECT  Areas.AreaID AreaID,
    Areas.areaname AreaName,
    s1.SystemName,
    (select count(systemname) 
     from systems s2
     where s1.areaid = s2.areaid
     group by areaid) noOfSystems
FROM Systems s1
INNER JOIN Areas 
    ON Areas.AreaID = s1.AreaID
WHERE ....

参见 SQL Fiddle with Demo

或者您可以使用您加入的子查询来获取总数:

SELECT  Areas.ID AreaID,
    Areas.Name AreaName,
    s1.SystemName,
    s2.NoOfSystems
FROM Systems s1
INNER JOIN Areas 
    ON Areas.ID = s1.AreaID
INNER JOIN
(
    select COUNT(SystemName) NoOfSystems,
        AreaID
    from systems
    group by AreaID
) s2
    on s1.AreaID= s2.AreaID
WHERE ....

参见 SQL Fiddle with Demo

此版本使用子查询来获取总计数，然后将其连接回 Systems 表以在需要时返回其他列。

或者，如果您的 RDBMS 通过使用 Count() over() 具有此选项，则可以使用窗口函数。 :

SELECT  Areas.ID AreaID,
    Areas.Name AreaName,
    Systems.SystemName,
    COUNT(SystemName) over(partition by Areas.ID, Areas.AreaName) as NoOfSystems
FROM Systems
INNER JOIN Areas 
    ON Areas.ID = Systems.AreaID
WHERE ....

参见 SQL Fiddle with Demo