代码之家 › 专栏 › 技术社区 › TheTXI

SQL查询中有多个计数

group-by sql-server-2000 tsql

TheTXI · 技术社区 · 16 年前

给出以下简单的表格结构:

Departments
PK - DeptID    DeptName
--------------------------
1              Department 1
2              Department 2
3              Department 3
4              Department 4

Groups
PK - GroupdID    DeptID
--------------------------
1                1
2                1
3                3 
4                4
5                2
6                3
7                1
8                3

Inventory
PK - ItemID    GroupID
--------------------------
1              2
2              3
3              8
4              1
5              4
6              5
7              1
8              2
9              2
10             3
11             7

有没有一种不使用子查询(这很容易)的方法,在这里我可以得到部门列表、每个部门中组的计数以及每个部门中的库存计数?

实例输出:

DeptID    DeptName          GroupCount      ItemCount
-----------------------------------------------------
1         Department 1      3               6
2         Department 2      1               1
3         Department 1      3               3
4         Department 4      1               1

我的直觉告诉我,这只是一个简单的问题,让小组的发言正确,但到目前为止,我是一个空白。如果它确实需要使用子查询,这不是问题。我只是想确认一下,以备将来参考。

注释 :使用SQL Server 2000解决此特定问题

4 回复 | 直到 16 年前

Quassnoi 16 年前

SELECT  d.deptID,
        COUNT(DISTINCT g.GroupID) AS Groups,
        COUNT(DISTINCT i.ItemID) AS Items
FROM    Departments d
LEFT JOIN 
        Groups g
ON      g.deptID = d.deptID
LEFT JOIN
        Items i
ON      i.GroupID = g.GroupID
GROUP BY
        d.deptID

结果如下:

deptID  Groups  Items
-----   ------  -----
1       3       6 
2       1       1
3       3       3
4       1       1

这也会产生正确的 0 是为了 Departments 那没有 Groups 或者只有 组 没有 Items .

Lieven Keersmaekers 16 年前

下面至少是获得结果的一种方法。

SELECT d.DeptID, d.DeptName, ISNULL(g.Groups, 0), ISNULL(t.TotalItems, 0)
FROM 
  Departments d
  LEFT OUTER JOIN (
    SELECT d.DeptID, Groups = COUNT(*)
    FROM Departments d
         INNER JOIN Groups g ON g.DeptID = d.DeptID
    GROUP BY d.DeptID
  ) g ON g.DeptID = d.DeptID
  LEFT OUTER JOIN (
    SELECT d.DeptID, TotalItems = COUNT(*)
    FROM Departments d
         INNER JOIN Groups g ON g.DeptID = d.DeptID
         INNER JOIN Inventory i ON i.GroupID = g.GroupID
    GROUP BY d.DeptID
  ) t ON t.DeptID = d.DeptID

KM. 16 年前

这是我的尝试…

declare @Depatments table
(
DeptID  int
,DeptName  varchar(15)
)

declare @Groups table
(
GroupID  int
,DeptID  int
)

declare @Inventory table
(
ItemID    int
,GroupID  int
)

INSERT INTO @Depatments VALUES (1,'Department 1')
INSERT INTO @Depatments VALUES (2,'Department 2')
INSERT INTO @Depatments VALUES (3,'Department 3')
INSERT INTO @Depatments VALUES (4,'Department 4')

INSERT INTO @Groups VALUES (1,1)
INSERT INTO @Groups VALUES (2,1)
INSERT INTO @Groups VALUES (3,3)
INSERT INTO @Groups VALUES (4,4)
INSERT INTO @Groups VALUES (5,2)
INSERT INTO @Groups VALUES (6,3)
INSERT INTO @Groups VALUES (7,1)
INSERT INTO @Groups VALUES (8,3)

INSERT INTO @Inventory VALUES (1 ,2)
INSERT INTO @Inventory VALUES (2 ,3)
INSERT INTO @Inventory VALUES (3 ,8)
INSERT INTO @Inventory VALUES (4 ,1)
INSERT INTO @Inventory VALUES (5 ,4)
INSERT INTO @Inventory VALUES (6 ,5)
INSERT INTO @Inventory VALUES (7 ,1)
INSERT INTO @Inventory VALUES (8 ,2)
INSERT INTO @Inventory VALUES (9 ,2)
INSERT INTO @Inventory VALUES (10,3)
INSERT INTO @Inventory VALUES (11,7)


--works with derived tables
SELECT
    d.DeptName,dt_g.CountOf AS GroupCount, dt_i.CountOf AS InventotyCount
    FROM @Depatments  d
        LEFT OUTER JOIN (SELECT
                             COUNT(*) AS CountOf,DeptID
                             FROM @Groups
                             GROUP BY DeptID
                        ) dt_g ON d.DeptID=dt_g.DeptID
        LEFT OUTER JOIN (SELECT
                             COUNT(*) AS CountOf,g.DeptID
                             FROM @Groups               g
                                 INNER JOIN @Inventory  i ON g.GroupID=i.GroupID
                             GROUP BY DeptID
                        ) dt_i ON d.DeptID=dt_i.DeptID

uriDium 16 年前

抱歉,我没有坐在我的SQL Server前面。这是你的第一次尝试。我可能不明白你想要的结果是正确的,但也许你可以把它作为一个起点?

SELECT 
  Department.DeptId,
  Department.DeptName,
  Group.GroupId,
  COUNT (Inventory.GroupId) as TotalItems
FROM
  Department
  INNER JOIN Groups
    On (Department.DeptId = Groups.DeptId)
  INNER JOIN Inventory
    On (Inventory.GroupId = Groups.GroupId)
GROUP BY
  Department.DeptId,
  Department.DeptName
  Group.GroupId,
  Inventory.GroupId