sql - 一个(稍微复杂的)SQL 查询？

我有这个复杂的(对我来说)需求，并且希望能对它进行 SQL 查询。这是交易:

在 Access 文件中有一个名为“费用”的表。

该表包含“类别”、“金额”、“付款人”和“IsShare”等字段。

类别可以是 Gas、Groceries、Home 等...

Amount 是费用的美元金额。

付款人可以是我或我的兄弟(“我”或“兄弟”)。

IsShare 可以是 true 也可以是 false。基本上，如果 IsShare 为真，则费用将一分为二。否则，付款人将支付所有费用。

我需要一个 SQL 查询来计算特定类别的所有费用。应该这样计算:

我已支付的所有非共享项目的 100%(Payer = 'Me' AND IsShare = false)，

我已支付的所有共享项目的 50%(Payer = 'Me' AND IsShare = true)，以及

我兄弟支付的所有共享项目的 50%(Payer = 'Bro' AND IsShare = true)。

查询应返回所有这三项的总和。

到目前为止，我有一个初步查询，但它返回不正确的结果。有人可以帮我改正吗？

非常感谢你。

return @"SELECT Sum(Amount) AS TotalAmount " +
        "FROM Expenses " +
        "WHERE Category = 'Groceries' " +
        "AND Payer = 'Me' " +
        "AND Share = false " +
        "GROUP BY Category " +
        "UNION " +
        "SELECT 0.5 * Sum(Amount) AS TotalAmount " +
        "FROM Expenses " +
        "WHERE Category = 'Groceries' " +
        "AND Payer = 'Me' " +
        "AND Share = true " +
        "GROUP BY Category " +
        "UNION " +
        "SELECT 0.5 * Sum(Amount) AS TotalAmount " +
        "FROM Expenses " +
        "WHERE Category = 'Groceries' " +
        "AND Payer = 'Bro' " +
        "AND Share = true ";
        "GROUP BY Category";

最佳答案

我认为您要做的事情不需要 UNION。这将传递一次数据并将值放在各个列中。您可能需要将 0.5 转换或转换为特定类型，我没有机会运行它。

SELECT
     Category,
     SUM(CASE WHEN Payer='me' AND NOT IsShare THEN Amount ELSE 0 END) as IPaid,
     SUM(CASE WHEN Payer='me' AND IsShare THEN Amount * 0.5 ELSE 0 END) as SharedPay,
     SUM(CASE WHEN Payer='bro' AND IsShare THEN Amount * 0.5 ELSE 0 END) as BrotherPay
FROM Eexpenses
WHERE Category = 'Groceries'
GROUP BY Category

对于 MS Access :

SELECT
     Category,
     SUM(IIF(Payer='me' AND NOT IsShare, Amount,0)) as IPaid,
     SUM(IIF(Payer='me' AND IsShare, Amount * 0.5, 0)) as SharedPay,
     SUM(IIF(Payer='bro' AND IsShare, Amount * 0.5, 0)) as BrotherPay
FROM Eexpenses
WHERE Category = 'Groceries'
GROUP BY Category

将三列相加:

SELECT Category, IPaid, SharedPay, BrotherPay, IPay + SharedPay + BotherPay as Total
FROM (
    SELECT
         Category,
         SUM(IIF(Payer='me' AND NOT IsShare, Amount,0)) as IPaid,
         SUM(IIF(Payer='me' AND IsShare, Amount * 0.5, 0)) as SharedPay,
         SUM(IIF(Payer='bro' AND IsShare, Amount * 0.5, 0)) as BrotherPay
    FROM Eexpenses
    WHERE Category = 'Groceries'
    GROUP BY Category
) as T1

关于sql - 一个(稍微复杂的)SQL 查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9755610/