SQL加入查询获取借方余额不等于0的项目名称，日期和借方余额总和？

SELECT dbo.items.NAME, 
       CONVERT(VARCHAR(20), dbo.warehouse.date, 101)AS Date, 
       Sum (dbo.warehouse.debit)                    AS Quantity 
FROM   dbo.warehouse 
       INNER JOIN dbo.items 
               ON dbo.warehouse.businessunitid = dbo.items.businessunitid 
                  AND dbo.warehouse.itemid = dbo.items.itemid 
GROUP  BY dbo.items.NAME, 
          CONVERT(VARCHAR(20), dbo.warehouse.date, 101)

SQL Join Query to Get Item Name , Date and Sum of Debit Balance where Debit balance Not Equal to 0

SQL Join查询获取借方余额不等于0的项目名称，日期和借方余额总和

2 个解决方案

#1

It looks like you are using SQL Server.

看起来您正在使用SQL Server。

SELECT i.NAME, 
       CONVERT(VARCHAR(20), w.date, 101 ) AS Date, 
       Sum(w.debit) AS Quantity 
FROM dbo.warehouse w INNER JOIN
     dbo.items i
     ON w.businessunitid = i.businessunitid AND
        w.itemid = i.itemid 
GROUP BY i.NAME, CONVERT(VARCHAR(20), w.date, 101) 
HAVING Sum(w.debit) > 0;

This assumes that w.debit is never negative. (In financial applications, dollar amounts are often always positive.) Of course, if it can be negative, you might want:

这假设w.debit永远不会消极。（在金融应用程序中，美元金额通常总是正数。）当然，如果它可能是负数，您可能需要：

HAVING Sum(w.debit) <> 0;

or:

要么：

HAVING Sum(w.debit) < 0;

Depending on the circumstances.

视情况而定。

In many other databases, you can use the alias in the HAVING clause:

在许多其他数据库中，您可以使用HAVING子句中的别名：

HAVING Quantity > 0

but SQL Server doesn't support this.

但SQL Server不支持此功能。

Also note the use of table aliases. This makes it easier to write and to read queries.

另请注意表别名的使用。这样可以更轻松地编写和读取查询。

#2

You are looking for having clause. Append this to your query:

你正在寻找有条款。将此附加到您的查询：

HAVING Sum (dbo.warehouse.debit) <> 0

Quoting HAVING (Transact-SQL) docs -- your sql looks like t-sql --

引用HAVING（Transact-SQL）文档 - 你的sql看起来像t-sql -

HAVING (Transact-SQL)

HAVING（Transact-SQL）

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

指定组或聚合的搜索条件。 HAVING只能与SELECT语句一起使用。 HAVING通常用于GROUP BY子句。不使用GROUP BY时，HAVING的行为类似于WHERE子句。

#1