如何按零和分组并包含零和

本文关键字：包含零 何按零 | 更新日期: 2023-09-27 18:31:36

我正在尝试编写一个查询，该查询返回用户在我的应用程序中创建的每个类别中的事务总和。 我当前的查询工作正常，但如果尚未在该类别下进行任何交易，则不会显示类别。 我希望我的查询显示每个类别，即使所有内容的总和为 0。

我

查找了如何做到这一点，并被建议使用左/右外部连接，但它没有做我想要的。

我正在通过 Windows C# 应用程序访问 Microsoft Access 2007 数据库。这是我当前的查询：

SELECT Categories.CategoryName ,
       SUM( Transactions.TransactionCredit ) as CreditSum ,
       SUM( Transactions.TransactionDebit  ) as DebitSum  ,
       SUM( Transactions.TransactionCredit ) -
       SUM( Transactions.TransactionDebit  ) as Difference ,
       SUM( Transactions.TransactionDebit  ) /
       ( SELECT SUM(TransactionDebit)
         FROM Transactions
         WHERE AccountID = {currentAccountId}
       ) as 'Percent of Total Debit'
FROM Categories
LEFT JOIN Transactions on Categories.CategoryName = Transactions.CategoryName
WHERE Transactions.AccountID = {currentAccountId}
  AND TransactionDate >= {startDate}
  AND TransactionDate <= {endDate}
GROUP BY Categories.CategoryName

这是我的 c# 应用程序中的编码方式：

queries.returnQuery("SELECT Categories.CategoryName, " +
"SUM(Transactions.TransactionCredit) as CreditSum, " +
"SUM(Transactions.TransactionDebit) as DebitSum, " +
"SUM(Transactions.TransactionCredit) - SUM(Transactions.TransactionDebit) as Difference, " +
"SUM(Transactions.TransactionDebit) / (SELECT SUM(TransactionDebit) FROM Transactions WHERE AccountID = " + currentAccountID + ") as 'Percent of Total Debit' " +
"FROM Categories LEFT JOIN Transactions on Categories.CategoryName = Transactions.CategoryName " +
"WHERE Transactions.AccountID = " + currentAccountID +
(enableDateFilter ? " AND Transactions.TransactionDate >= #" + startDate + "# AND Transactions.TransactionDate <= #" + endDate + "# " : " ") +
" GROUP BY Categories.CategoryName");

如何按零和分组并包含零和

没有双引号，您的查询将更容易遵循。 无论如何，问题是where子句引用了Transactions表，撤消了left outer join。 将这些条件移到on子句中。 在您的情况下，最简单的方法是将where更改为and：

"SELECT Categories.CategoryName, " +
"SUM(Transactions.TransactionCredit) as CreditSum, " +
"SUM(Transactions.TransactionDebit) as DebitSum, " +
"SUM(Transactions.TransactionCredit) - SUM(Transactions.TransactionDebit) as Difference, " +
"SUM(Transactions.TransactionDebit) / (SELECT SUM(TransactionDebit) FROM Transactions WHERE AccountID = " + currentAccountID + ") as 'Percent of Total Debit' " +
"FROM Categories LEFT JOIN Transactions on Categories.CategoryName = Transactions.CategoryName " +
"and Transactions.AccountID = " + currentAccountID +
(enableDateFilter? " AND TransactionDate >= #" + startDate + "# AND TransactionDate <= #" + endDate + "# " : " ") +
"GROUP BY Categories.CategoryName"

由于您在 Categories 和 Transactions 之间使用LEFT JOIN，因此您应该期望事务表上有NULL值。在 JOIN 子句中包含您的帐户 ID 过滤器将解决问题：

DECLARE @currentAccountId INT
SELECT  @currentAccountId = 1
;WITH   Transactions AS
(
    SELECT  *
    FROM    (   
    VALUES
                (1, 1000., 2000., 'Category B'),
                (2, 2000., 1000., 'Category B'),
                (1, 1000., 1000., 'Category C')
            )
            T ( AccountID, TransactionCredit, TransactionDebit, CategoryName)
),      Categories AS
(
    SELECT  *
    FROM    (
    VALUES
                ('Category A'),
                ('Category B'),
                ('Category C')
            )
            C (CategoryName)
)
SELECT      Categories.CategoryName ,
            SUM( Transactions.TransactionCredit ) as CreditSum ,
            SUM( Transactions.TransactionDebit  ) as DebitSum  ,
            SUM( Transactions.TransactionCredit ) -
            SUM( Transactions.TransactionDebit  ) as Difference ,
            SUM( Transactions.TransactionDebit  ) /
            (
                SELECT  SUM(TransactionDebit)
                FROM    Transactions
                WHERE   AccountID = @currentAccountId
            ) AS 'Percent of Total Debit'
FROM        Categories
LEFT JOIN   Transactions
    ON      Categories.CategoryName = Transactions.CategoryName
            AND Transactions.AccountID = @currentAccountId
            --AND TransactionDate >= {startDate}
            --AND TransactionDate <= {endDate}
GROUP BY    Categories.CategoryName

结果

CategoryName    CreditSum   DebitSum    Difference  Percent of Total Debit
---------------------------------------------------------------------------------
Category A      NULL        NULL        NULL        NULL
Category B      1000        2000        -1000       0.666666
Category C      1000        1000        0           0.333333

我面临的问题是，在使用 JOIN 时，您必须使用将两个表进行比较的条件。 就我而言，我没有这样做。 我使用的是用户在 c# 应用程序中选择的参数，因此我使用 SUBQUERY 代替事务表并获得了预期的结果。 请参阅下面的解决方案：

C# 中的解决方案：

queries.returnQuery("SELECT Categories.CategoryName, " +
                           "SUM(SUBQUERY.TransactionCredit) as CreditSum, " +
                           "SUM(SUBQUERY.TransactionDebit) as DebitSum, " +
                           "SUM(SUBQUERY.TransactionCredit) - SUM(SUBQUERY.TransactionDebit) as Difference " +
                           "FROM Categories LEFT JOIN " +
                               "(SELECT CategoryName, TransactionCredit, TransactionDebit FROM Transactions WHERE AccountID = " + currentAccountID + 
                               (enableDateFilter ? " AND TransactionDate >= #" + startDate + "# AND TransactionDate <= #" + endDate + "# " : " ") + 
                               ") AS SUBQUERY " + 
                           "ON Categories.CategoryName = SUBQUERY.CategoryName " +
                           "GROUP BY Categories.CategoryName");

解决方案不在 C# 中：

SELECT Categories.CategoryName ,
       SUM( SUBQUERY.TransactionCredit ) as CreditSum ,
       SUM( SUBQUERY.TransactionDebit  ) as DebitSum  ,
       SUM( SUBQUERY.TransactionCredit ) -
       SUM( SUBQUERY.TransactionDebit  ) as Difference ,
       SUM( SUBQUERY.TransactionDebit  ) /
       ( SELECT SUM(TransactionDebit)
         FROM Transactions
         WHERE AccountID = {currentAccountId}
       ) as 'Percent of Total Debit'
FROM Categories
LEFT JOIN 
(
    SELECT CategoryName, 
           TransactionCredit, 
           TransactionDebit 
    FROM Transactions 
    WHERE AccountID = {currentAccountID}
      AND TransactionDate >= {startDate}
      AND TransactionDate <= {endDate}
) as SUBQUERY 
ON Categories.CategoryName = SUBQUERY.CategoryName
GROUP BY Categories.CategoryName
相关文章：