从单个SQL查询中获取两个不同的值

Question

有办法简化/优化mysql/mariadb查询吗？最终，我需要来自单个查询的两个单独的数据:最新的事务名称和所有事务支付金额的总和。

这是可行的，但很难看，因为它重复了联接和WHERE子句：

    SELECT
        SUM(btp.allocated_amount),
        (
        SELECT
            bt.name
        FROM
            `tabBank Transaction Payments` as btp
        LEFT JOIN
            `tabBank Transaction` bt ON bt.name=btp.parent
        WHERE
            btp.payment_document = 'Journal Entry'
        AND
            bt.docstatus = 1
        ORDER BY
            bt.date desc
        LIMIT 1
        ) AS name
        FROM
            `tabBank Transaction Payments` as btp
        LEFT JOIN
            `tabBank Transaction` bt ON bt.name=btp.parent
        WHERE
            btp.payment_document = 'Journal Entry'
        AND
            bt.docstatus = 1;

我原以为像这样的东西会起作用，但它不起作用。给出的名字是任意的，而不是按照命令第一个：

        SELECT
        (SELECT SUM(allocated_amount)),
        (SELECT name LIMIT 1)
        FROM
        (
        SELECT
            btp.allocated_amount,
            bt.name
        FROM
            `tabBank Transaction Payments` as btp
        LEFT JOIN
            `tabBank Transaction` bt ON bt.name=btp.parent
        WHERE
            btp.payment_document = 'Journal Entry'
        AND
            bt.docstatus = 1
        ORDER BY
            bt.date desc
        ) AS temp;

编辑：

示例数据(注:示例已简化)：

tabBank交易支付

| parent | payment_document | allocated_amount |
------------------------------------------------
| doc1   | Journal Entry    | 10.00            |
| doc1   | Journal Entry20  | 4000.00          |
| doc2   | Journal Entry    | 20.00            |
| doc2   | Journal Entry20  | 5000.00          |
| doc3   | Journal Entry    | 30.00            |
| doc3   | Journal Entry20  | 6000.00          |

tabBank事务

| name | date       | docstatus |
---------------------------------
| doc1 | 2022-01-01 | 1         |
| doc2 | 2022-02-01 | 1         |
| doc3 | 2022-03-01 | 1         |

所需结果：(60.00，doc3)

mariadb版本: 10.2.27

Answer 1

感谢施德施的建议和对窗口函数的介绍，我不知道它的存在。我把它简化了，最后得到了这个。

对于由于分区而需要多个结果行的其他情况，需要使用ROW_NUMBER()。

SELECT *
FROM (
    SELECT
        SUM(btp.allocated_amount) OVER() total_allocated_amount,
        FIRST_VALUE(bt.name) OVER(ORDER BY bt.date desc) latest_name
    FROM
        `tabBank Transaction Payments` as btp
    LEFT JOIN
        `tabBank Transaction` bt ON bt.name=btp.parent
    WHERE
        btp.payment_document = 'Journal Entry'
    AND
        bt.docstatus = 1
) t1
LIMIT 1

Answer 2

我们可以尝试使用ROW_NUMBER & SUM窗口函数来实现它。

ROW_NUMBER通过bt.date desc获得了最后一行的名称

OVER子句对于基于一组记录执行计算的窗口函数是必不可少的，PARTITION BY定义了划分行的组。

更多细节，我们可以看到窗口函数的概念与句法

SELECT total_allocated_amount,name
FROM (
    SELECT
        SUM(btp.allocated_amount) OVER() total_allocated_amount,
        ROW_NUMBER() OVER(ORDER BY bt.date desc) rn,
        bt.name
    FROM
        `tabBank Transaction Payments` as btp
    LEFT JOIN
        `tabBank Transaction` bt ON bt.name=btp.parent
    WHERE
        btp.payment_document = 'Journal Entry'
    AND
        bt.docstatus = 1
) t1
WHERE rn = 1

木琴