时间戳

首页 > 解决方案 > 在 MS-Access 中加入两个使用聚合的查询

sql - 在 MS-Access 中加入两个使用聚合的查询

问题描述

这个问题要求我们加入多个表格，并根据已完成的订单和付款显示每个帐户的余额。

由于 MS-Access 特定的语法，您无法使用别名在 from 子句中加入两个查询。我尝试过使用 UNION 功能，但这似乎也不起作用。您可以在下面看到我一直在尝试连接的两个查询。

查询 1

SELECT c.clientname, SUM(p.payment) AS Total_Paid

FROM clients c INNER JOIN payments p ON c.clientnumber = p.clientnumber

GROUP BY c.clientname

结果

[Client Name] [Total_Paid]
Client A           1000    
Client B           1500    
Client C           2000

查询 2

SELECT c.clientname, SUM(i.orderamount * i.itemprice) AS Total_Owed

FROM (clients c INNER JOIN orders o ON c.clientnumber = o.clientnumber)

INNER JOIN orderinfo i ON i.ordernum = o.ordernum

GROUP BY c.clientname

结果

[Client Name] [Total_Owed]
Client A           1000    
Client B           2500    
Client C           3000

我想让我的结果是一个表，它是从 Total Owed 中减去 Total_Paid 的结果，而只使用一个查询来做到这一点。

我尝试运行此查询作为起点

(SELECT c1.clientname, SUM(p.payment) AS Total_Paid

FROM clients c1 INNER JOIN payments p ON c1.clientnumber = p.clientnumber

GROUP BY c1.clientname)

UNION

(SELECT c.clientname, SUM(i.orderamount * i.itemprice) AS Total_Owed

FROM (clients c INNER JOIN orders o ON c.clientnumber = o.clientnumber)

INNER JOIN orderinfo i ON i.ordernum = o.ordernum

GROUP BY c.clientname)

但我得到的结果是这样的

[Client Name] [Total_Paid]
Client A           1000
Client A           1000
Client B           1500
Client B           2500
Client C           2000
Client C           3000

代替

[Client Name] [Total_Owed][Total_Paid]
Client A          1000       1000 
Client B          2500       1500 
Client C          3000       2000

完成的结果应该类似于

[Client Name] [Balance]
Client A          0 
Client B          1000 
Client C          2000

如果需要任何澄清，请告诉我！

标签： sqlms-access

解决方案

您有clients、payments、orders和的表orderinfo。看起来关系是clientsJOINpayments和orders_ _client_numberordersorderinfoorder_number. 因此，您需要像上面一样将付款和订单汇总在一起，但您需要将这两个汇总金额连接为派生表，而不是使用 UNION。这将允许您减去数量，而不是在结果中垂直堆叠它们。JOIN 可以从相关表中添加列，而 UNION 可以堆叠来自不同查询的相似结构化结果。由于所需的比较是并排的，因此您需要 JOIN 对计算出的指标进行操作，就好像它们是同一行的一部分但在不同的列中一样。我使用了 LEFT JOIN 并从orders因为我可以想象有人有可能在尚未付款的情况下订购，并且您希望看到该结果仍然是他们的全部余额。INNER JOIN 只会在客户既有订单又有付款的情况下产生结果。Access 不支持 COALESCE() 或 CASE 表达式，因此 IIF() 用于摆脱没有付款的情况（这将是空的，因此不能用于减法）。

SELECT
    tot_owed.clientname
    , tot_owed.Total_Owed 
    - IIF(tot_paid.Total_Paid IS NULL, 0, tot_paid.Total_Paid) AS Balance
FROM (
    SELECT 
        c.clientname
        , c.clientnumber
        , SUM(i.orderamount * i.itemprice) AS Total_Owed
    FROM 
        clients c 
    INNER JOIN 
        orders o ON c.clientnumber = o.clientnumber
    INNER JOIN 
        orderinfo i ON i.ordernum = o.ordernum
    GROUP BY 
        c.clientname
        , c.clientnumber
) tot_owed
LEFT JOIN (
    SELECT
        c.clientname
        , c.clientnumber
        , SUM(p.payment) AS Total_Paid
    FROM 
        clients c 
    INNER JOIN 
        payments p ON c.clientnumber = p.clientnumber
    GROUP BY 
        c.clientname
        , c.clientnumber
) tot_paid ON tot_owed.clientnumber = tot_paid.clientnumber

推荐阅读