yaylitzis's questions

我有表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 中。

我有一张表用户

+----------+----------+
|  ID_EMP  | isActive |
+----------+----------+
|   100    +    1     +
|   101    +    1     +
+----------+----------+

和一个表UsersInfo

+----------+------------+
|  ID_EMP  |   FRDATE   |
+----------+------------+
|   100    +   null     +
|   101    + 2019-10-10 +
+----------+------------+

当不为空时，我尝试Users.isActive用 0更新列。UsersInfo.FRDATE我阅读了类似的问题并以以下查询结束，它没有按预期工作，因为它正在更新所有列0

UPDATE [dbMyDB].[dbo].[Users]
SET [dbMyDB].[dbo].[Users].[isActive] = 0
FROM [dbMyDB].[dbo].[Users] USERS
INNER JOIN
    [dbMyDB].[dbo].[UsersInfo] INFO
ON USERS.ID_EMP = INFO.ID_EMP
WHERE EXISTS 
(
    SELECT FRDATE FROM [dbMyDB].[dbo].[UsersInfo]
    WHERE FRDATE IS NOT NULL
)

我缺少什么？谢谢！