主页 / dba / 问题 / 251321
Accepted
yaylitzis
Asked: 2019-10-17 21:54:41 +0800 CST

当获取由其他一些列分组的列的总和时，查询返回重复箭头

  • 772

我有表Inventory，我想sum(quantity)按列 gid、版本、仓库分组

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    100   +    name 1    +
|   gid1   +    1     +    1      +    200   +    name 1    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

以下查询有效，因为它返回 2 条记录的总和：

SELECT gid, version, warehouse, SUM(quantity) as total
FROM Inventory
GROUP BY gid, version, warehouse;

+----------+----------+-----------+----------+
|   gid    | version  |  warehouse| quantity |
+----------+----------+-----------+----------+
|   gid1   +    1     +    1      +    300   +
|   gid2   +    1     +    2      +    200   +
+----------+----------+-----------+----------+

我还尝试description在记录中添加该列：

SELECT gid, version, warehouse, SUM(quantity) as total, description
FROM Inventory
GROUP BY gid, version, warehouse;

但我收到一条错误消息：

列“描述”在选择列表中无效，因为它既不包含在聚合函数中，也不包含在 GROUP BY 子句中

我搜索了它，最后得到了这个查询：

SELECT T1.gid
      ,T1.description
      ,T1.version
      ,T1.warehouse
      ,T2.quantity
FROM Inventory T1
INNER JOIN (
    SELECT gid, version, warehouse, SUM(quantity) AS quantity
    FROM Inventory
    GROUP BY gid, version, warehouse    
) T2
ON T1.gid = T2.gid

它显示了正确的总和，但它返回 3 条记录：

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    300   +    name 1    +
|   gid1   +    1     +    1      +    300   +    name 2    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

我怎样才能只得到 2 个箭头？以下是所需的输出：

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    300   +    name 1    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

我的数据库在 SQL Server Express 2008 中。

sql-server sum

2 个回答

  1. Best Answer

    返回三行，因为 Inventory 有三行。内部加入聚合表不会过滤掉三行。为此，您可以使用 DISTINCT 或简单地分组description。看到这个小提琴。

    清楚的

    SELECT DISTINCT T1.gid
      ,T1.description
      ,T1.version
      ,T1.warehouse
      ,T2.quantity
FROM Inventory T1
INNER JOIN (
    SELECT gid, version, warehouse, SUM(quantity) AS quantity
    FROM Inventory
    GROUP BY gid, version, warehouse    
) T2
ON T1.gid = T2.gid

    按描述分组

    SELECT gid, version, warehouse, description, SUM(quantity) as total
FROM Inventory
GROUP BY gid, version, warehouse, description;
    • 2

  2. 另一种解决方案，即使(gid, version, warehouse)组包含不同的description值也适用。

    SELECT gid, 
       version, 
       warehouse, 
       SUM(quantity) AS total, 
       MAX(description) AS description
FROM Inventory
GROUP BY gid, version, warehouse;

    仅description返回组中所有值中的一个值。MIN() 也可以使用。

    当然，当一个组内的所有记录都包含相同的值时，它是适用的。

    • 1

相关问题