Mssql查询问题

Question

我有一个MSSQL数据库和下表

何以

+----------+----------+----------+
| Item No_ | Bin Code | Quantity |
+----------+----------+----------+
| 0000955  | K2-3-3   | -2       |
+----------+----------+----------+
| 0000955  | C2-2-4   | 3        |
+----------+----------+----------+
| 0000955  | K2-3-3   | 5        |
+----------+----------+----------+
| 0000955  | K2-3-3   | 1        |
+----------+----------+----------+
| 0000955  | C2-2-4   | -1       |
+----------+----------+----------+
| 0000955  | K2-3-3   | -10      |
+----------+----------+----------+
| 0000955  | C2-2-4   | 7        |
+----------+----------+----------+
| 0000955  | K2-3-3   | 3        |
+----------+----------+----------+
| 0000955  | C2-2-4   | 8        |
+----------+----------+----------+

项目

+----------+--------+
| Item No_ | Desc   |
+----------+--------+
| 0000054  | Spring |
+----------+--------+
| 0000087  | Ring   |
+----------+--------+

当我执行查询时

select [Bin Code],
sum([Quantity])
from dbo.[whe]
where [Item No_]='0000955'GROUP BY [Bin Code]

得到返回的结果

+----------+----------+
| Bin Code | Quantity |
+----------+----------+
| K2-3-3   | -3       |
+----------+----------+
| C2-2-4   | 17       |
+----------+----------+

但我需要这样的东西

select  we.[Bin Code],
sum(we.[Quantity]),
it.[Item No_],
it.[Desc],
from dbo.[whe] as we,
dbo.[item] as it
and it.[No_]=we.[Item No_] 

所以我想要这样的东西

+-----------------+--------+----------+----------+
| Item No_ | Desc   | Bin Code | Quantity |
+-----------------+--------+----------+----------+
| 0000955         | Valve  | K2-3-3   | -3       |
+-----------------+--------+----------+----------+
| 0000955         | Valve  | C2-2-4   | 17       |
+-----------------+--------+----------+----------+

但是查询是不可执行的，并且得到的错误如下

列'dbo.Item.No_‘在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

Answer 1

如果要显示desc列，可以使用下面的查询。

select t1.[Bin Code],t1.[Quantity],t1.[Item No_],t2.[Desc] (select [Bin Code],
sum([Quantity]) as [Quantity],[Item No_]
from dbo.[whe]
where [Item No_]='0000955'GROUP BY [Bin Code],[Item No_] ) t1
left join dbo.[Item] t2
on (t1.[Item No_]=t2.[Item No_])