mysql - 根据两个查询的比较计算列

Question

我正在尝试根据两个查询的比较来计算列。以使用同一张表的这两个查询为例。唯一的区别是日期。

-Query for Monday
|Balance: 1,000

-Query for Tuesday
|Balance: 900

比较后，我尝试创建的列应该是这样的(基于这两个查询):

-Column "payment" 
|Payment: 100

我该怎么办？

Answer 1

您可以加入两个查询:

SELECT     monday_payment - tuesday_payment AS balance
FROM       (SELECT payment AS monday_payment
            FROM   some_table
            WHERE  some_date = /* monday */) m -- Assume this returns one line
CROSS JOIN (SELECT payment AS tuesday_payment
            FROM   some_table
            WHERE  some_date = /* monday */) t -- Assume this returns one line