问SQL SUM()只计算子查询中的一行
EN

Stack Overflow用户

提问于 2014-11-28 02:07:43

回答 3查看 155关注 0票数 1

我在为五张表做统计。我用一个客户端数据做了一个例子。

loan

id   | status
------------
1454 | payed

付款时间表

id   | loan_id | user_client_id
-----------------------------
1456 | 1454    | 3113

payment_schedule_row

id   | payment_schedule_id | payment | payment_date
---------------------------------------------------
5013 |  1456               |    32   | 2013-11-06
5014 |  1456               |    32   | 2013-12-06
5015 |  1456               |    32   | 2013-01-05
5016 |  1456               |    32   | 2013-02-04
5017 |  1456               |    32   | 2013-03-06
5018 |  1456               |    32   | 2013-04-05
5019 |  1456               |    32   | 2013-05-05
5020 |  1456               |    32   | 2013-06-04
5021 |  1456               |    32   | 2013-07-04
5022 |  1456               |    32   | 2013-08-03
5023 |  1456               |    32   | 2013-09-02
5014 |  1456               |    32   | 2013-10-02

payment_schedule_cover

id    | payment_schedule_id |    date    | sum
----------------------------------------------
2282  | 1456                | 2013-11-08 | 34
3054  | 1456                | 2013-12-07 | 40
3776  | 1456                | 2013-01-04 | 38
4871  | 1456                | 2013-02-06 | 49
5954  | 1456                | 2013-03-06 | 40
7070  | 1456                | 2013-04-25 | 49
9029  | 1456                | 2013-05-21 | 52
10377 | 1456                | 2013-06-20 | 30
10391 | 1456                | 2013-06-21 | 30
10927 | 1456                | 2013-07-07 | 60

payment_schedule_delay

id    | payment_schedule_row_id | start_date | end_date   | delay 
----------------------------------------------------------------
1135  | 5013                    | 2013-11-07 | 2013-11-08 | 0.07
1548  | 5014                    | 2013-12-07 | 2013-12-07 | 0.03
2628  | 5016                    | 2014-02-05 | 2014-02-06 | 0.01

查询内容如下：

SELECT period, loan_sum, covers, delay
FROM
    (SELECT MAX(EXTRACT(YEAR_MONTH FROM psc.date)) AS period, 
    (SELECT SUM(psr2.payment) FROM payment_schedule_row AS psr2 WHERE psr.payment_schedule_id = psr2.payment_schedule_id) AS loan_sum,
    (SELECT SUM(psc2.sum) FROM payment_schedule_cover AS psc2 WHERE psc.payment_schedule_id = psc2.payment_schedule_id) AS covers,
    (SELECT SUM(psd2.delay) FROM payment_schedule_delay AS psd2 WHERE psr.id = psd2.payment_schedule_row_id) AS delay
    FROM loan
    INNER JOIN payment_schedule AS ps ON ps.loan_id = loan.id
    INNER JOIN payment_schedule_row AS psr ON psr.payment_schedule_id = ps.id
    INNER JOIN payment_schedule_cover AS psc ON psc.payment_schedule_id = ps.id
    WHERE loan.status = 'payed'
    GROUP BY ps.id) AS sum_by_id
GROUP BY period

查询的结果：

period  | loan_sum  | covers | delay
-----------------------------------
201407  | 384       | 422    | 0.07

除了延迟，一切都是正确的。应该是0.11 (0.07 + 0.03 + 0.01)

因此，几天来，我一直试图从查询中查找错误。也许有人能告诉我我做错了什么。

小提琴链接： http://sqlfiddle.com/#!2/21585/2

mysql

sql

回答 3

Stack Overflow用户

回答已采纳

发布于 2014-12-02 03:16:56

我终于从一个MySQL论坛上得到了答案。解决我的问题的答案是：

..。有问题..。

子查询中的Group运算符看不到关联子查询和内的聚合。这些款项需要移出一个水平。
外部查询的Group组没有聚合；它只是按
一个查询，如选择a，b，c和(D).group a可以返回b和c的任意结果，除非a与b和c之间存在严格的1:1关系，这在您的子查询中不太可能实现。
相关子查询效率低下，正如您的两个阶段联接所演示的那样。
delay相关子查询没有连接到任何东西

因此，将相关子查询逻辑移到FROM子句，加入delay查询，按子句修改Group，我们就有了.

select psc.period, psc.sum, psr.payments, sum(psd.delay) as delay
from loan
join payment_schedule as ps on ps.loan_id = loan.id
join(
    select payment_schedule_id, sum(payment) as payments
    from payment_schedule_row
    group by payment_schedule_id
) as psr on psr.payment_schedule_id = ps.id
join (
    select payment_schedule_id, sum(sum) as sum, max( extract(year_month from date) ) as period
    from payment_schedule_cover
    group by payment_schedule_id
) psc on ps.id = psc.payment_schedule_id
join payment_schedule_row psr2 on ps.id = psr2.payment_schedule_id
join (
    select payment_schedule_row_id, sum(delay) as delay
    from payment_schedule_delay
    group by payment_schedule_row_id
) as psd on psr2.id = psd.payment_schedule_row_id
where loan.status = 'payed'
group by psc.period, psc.sum, psr.payments;

票数 0

Stack Overflow用户

发布于 2014-11-28 02:13:38

SELECT period, loan_sum, covers, delay
FROM
    (SELECT MAX(EXTRACT(YEAR_MONTH FROM psc.date)) AS period, 
    (SELECT SUM(psr2.payment) FROM payment_schedule_row AS psr2 WHERE psr.payment_schedule_id = psr2.payment_schedule_id) AS loan_sum,
    (SELECT SUM(psc2.sum) FROM payment_schedule_cover AS psc2 WHERE psc.payment_schedule_id = psc2.payment_schedule_id) AS covers,
    (SELECT SUM(psd2.delay) FROM payment_schedule_delay AS psd2 WHERE psr.id = psd2.payment_schedule_row_id) AS delay
    FROM loan
    INNER JOIN payment_schedule AS ps ON ps.loan_id = loan.id
    INNER JOIN payment_schedule_row AS psr ON psr.payment_schedule_id = ps.id
    INNER JOIN payment_schedule_cover AS psc ON psc.payment_schedule_id = ps.id
    INNER JOIN payment_schedule_delay AS psd ON psr.id = psd.payment_schedule_row_id
    WHERE loan.status = 'payed'
    GROUP BY ps.id) AS sum_by_id
GROUP BY period

票数 0

Stack Overflow用户

发布于 2014-11-28 02:58:12

SELECT period, loan_sum, covers, delay
FROM
    (SELECT MAX(EXTRACT(YEAR_MONTH FROM psc.date)) AS period, 
    (SELECT SUM(psr2.payment) FROM payment_schedule_row AS psr2 WHERE psr.payment_schedule_id = psr2.payment_schedule_id) AS loan_sum,
    (SELECT SUM(psc2.sum) FROM payment_schedule_cover AS psc2 WHERE psc.payment_schedule_id = psc2.payment_schedule_id) AS covers,
    (SELECT SUM(psd2.delay) FROM payment_schedule_delay AS psd2 WHERE psr.id IN /* IN operator will allow for multiple values like psr.id IN (5013,5014,5016) */ psd2.payment_schedule_row_id) AS delay
    FROM loan
    INNER JOIN payment_schedule AS ps ON ps.loan_id = loan.id
    INNER JOIN payment_schedule_row AS psr ON psr.payment_schedule_id = ps.id
    INNER JOIN payment_schedule_cover AS psc ON psc.payment_schedule_id = ps.id
    WHERE loan.status = 'paid'
    GROUP BY ps.id) AS sum_by_id
GROUP BY period