组合多个SQL查询一起

Question

我有多个表项目（课程，费用和付款）

为了让我做的会话执行以下操作：

SELECT 
sess.file_id, SUM(sess.rate * sess.length) AS total 
FROM 
sess 
WHERE sess.sessionDone = 1 
GROUP BY sess.file_id 

这将返回量特定学生要

我也有另一个表“费用”

SELECT 
file_charges.file_id, SUM(file_charges.price) AS total_charges 
FROM 
file_charges 
GROUP BY file_charges.file_id 

最后付款查询：

SELECT 
file_payments.file_id, SUM(file_payments.paymentAmount) AS total_payment 
FROM 
file_payments 
GROUP BY file_payments.file_id 

我可以结合这些3的方式有：

Total = Payments - (Session + Charges) 

注意，也可能是负面，所以我可以有一个存在的会话，费用的file_id但不付款，我能有没有会议或收费的支付...

编辑：http://sqlfiddle.com/#!2/a90d9

Answer 1

的一个问题是，是否这些查询的一个可以“驱动程序”，在情况下，我们没有通过一个或多个返回给定file_id行的查询。 （例如，有可能是从sess行，但没有从file_payments如果我们想一定要包括出现在任何一个查询的每一个可能的file_id，我们可以得到所有可能的file_id列表，像这样的查询：

SELECT ss.file_id FROM sess ss 
UNION 
SELECT fc.file_id FROM file_charges fc 
UNION 
SELECT fp.file_id FROM file_payments fp 

（注：UNION运算符将删除任何重复）

能获得指定的结果集，我们可以使用查询，伴随着“左加入”其他三个原始查询的查询的轮廓。将是：

SELECT a.file_id, p.total_payment - (s.total + c.total_charges) 
    FROM a 
    LEFT JOIN s ON s.file_id = a.file_id 
    LEFT JOIN c ON c.file_id = a.file_id 
    LEFT JOIN p ON p.file_id = a.file_id 
ORDER BY a.file_id 

在该声明中，a是查询的一个替代品，它获取所有file_id值的集合（如上所示）。 s,c和p分别是您的三个原始查询的站点，分别位于sess，file_charges和file_payments。

如果有任何file_id值在任何查询中“缺失”，我们将需要用零代替缺失值。我们可以使用IFNULL函数为我们处理。

该查询应返回结果集指定：

SELECT a.file_id 
    , IFNULL(p.total_payment,0) - (IFNULL(s.total,0) + IFNULL(c.total_charges,0)) AS t 
    FROM (-- all possible values of file_id 
     SELECT ss.file_id FROM sess ss 
      UNION 
     SELECT fc.file_id FROM file_charges fc 
      UNION 
     SELECT fp.file_id FROM file_payments fp 
     ) a 
    LEFT 
    JOIN (-- the amount that a specific student should pay 
     SELECT sess.file_id, SUM(sess.rate * sess.length) AS total 
      FROM sess 
      WHERE sess.sessionDone = 1 
      GROUP BY sess.file_id 
     ) s 
    ON s.file_id = a.file_id 
    LEFT 
    JOIN (-- charges 
     SELECT file_charges.file_id, SUM(file_charges.price) AS total_charges 
      FROM file_charges 
      GROUP BY file_charges.file_id 
     ) c 
    ON c.file_id = a.file_id 
    LEFT 
    JOIN (-- payments 
     SELECT file_payments.file_id, SUM(file_payments.paymentAmount) AS total_payment 
      FROM file_payments 
      GROUP BY file_payments.file_id 
     ) p 
    ON p.file_id = a.file_id 
ORDER BY a.file_id 

（该解释这种查询不会是漂亮，有四个派生表。在大集合上，表现可能是可怕的。但返回的结果集应该符合规范。）

要小心将所有三个表连接在一起的查询......如果存在（例如）两个（或多个）行，则可能会给出不正确的结果file_payment表。

还有其他方法可以获得等效的结果集，但上面的查询回答了这个问题：“我怎样才能将这些查询的结果合并为一个总数”。

---

使用相关子查询

这里的另一种方法，在SELECT列表中使用相关子查询...

SELECT a.file_id 
    , IFNULL((SELECT SUM(file_payments.paymentAmount) FROM file_payments 
        WHERE file_payments.file_id = a.file_id) 
      ,0) 
    - (IFNULL((SELECT SUM(sess.rate * sess.length) FROM sess 
        WHERE sess.file_id = a.file_id) 
      ,0) 
     + IFNULL((SELECT SUM(file_charges.price) FROM file_charges 
        WHERE file_charges.file_id = a.file_id) 
      ,0) 
     ) AS tot 
    FROM (-- all file_id values 
     SELECT ss.file_id FROM sess ss 
      UNION 
     SELECT fc.file_id FROM file_charges fc 
      UNION 
     SELECT fp.file_id FROM file_payments fp 
     ) a 
ORDER BY a.file_id 

Answer 2

试试这个

SELECT sess.file_id, SUM(file_payments.paymentAmount) - (SUM(sess.rate * sess.length)+SUM(file_charges.price)) as total_payment FROM sess , file_charges , file_payments 
    WHERE sess.sessionDone = 1 
    GROUP BY total_payment 

编辑。这需要解决

SELECT a.file_id 
, IFNULL(p.total_payment,0) - (IFNULL(s.total,0) + IFNULL(c.total_charges,0)) AS tot 
FROM ( 
    SELECT ss.file_id FROM sess ss 
     UNION 
    SELECT fc.file_id FROM file_charges fc 
     UNION 
    SELECT fp.file_id FROM file_payments fp 
    ) a 
LEFT JOIN ( 
    SELECT sess.file_id, SUM(sess.rate * sess.length) AS total 
     FROM sess 
     WHERE sess.sessionDone = 1 
     GROUP BY sess.file_id 
    ) s 
ON s.file_id = a.file_id 
LEFT JOIN ( 
    SELECT file_charges.file_id, SUM(file_charges.price) AS total_charges 
     FROM file_charges 
     GROUP BY file_charges.file_id 
    ) c 
ON c.file_id = a.file_id 
LEFT JOIN ( 
    SELECT file_payments.file_id, SUM(file_payments.paymentAmount) AS total_payment 
     FROM file_payments 
     GROUP BY file_payments.file_id 
    ) p 
ON p.file_id = a.file_id 
ORDER BY a.file_id 

DEMO HERE