mysql - 当日期来自2个字段时，MYSQL联合如何维护日期字段顺序？(MYSQL union how to maintain date field order when date came from 2 fields?)

Question

I have two tables Transactions and Expenses .

(我有两个表交易和费用 。)

I have written a query to get date wise transaction statement.

(我已经写了一个查询来获取明智的交易声明。)

Here Transactions table is deposit table.

(这里的交易表是存款表。)

For this query I am getting my desire result without order date.

(对于此查询，我得到没有订购日期的期望结果。)

SELECT IFNULL(date(t1.created), date(ex.created)) as Date , sum(t1.amount) as ReceiveAmount,ex.amount as ExpensesAmount 
    FROM transactions as t1
    LEFT JOIN (
        SELECT sum(e.amount) as amount, created
           FROM expenses as e 
           group by date(e.created)
        ) as ex
    ON date(ex.created) =  date(t1.created)
    GROUP BY date(t1.created)

    UNION

    SELECT IFNULL(date(t1.created), date(ex.created)) as Date, sum(t1.amount) as Receive,ex.amount as ExpensesAmount 
    FROM transactions as t1

    RIGHT JOIN (
        SELECT sum(e.amount) as amount, created
        FROM expenses as e 
        group by date(e.created)
    ) as ex
    ON date(t1.created) = date(ex.created)
    GROUP BY date(t1.created)

OUTPUT :

(输出：)

Date       ReceiveAmount    ExpensesAmount  
2018-12-04     600            NULL
2019-08-01     500            NULL
2019-10-18     500            NULL
2019-11-18     820            500  <== that should come at last.
2019-11-04     NULL           100

I need to see date ASC order.

(我需要查看日期ASC订单。)

Here last 2 date 2019-11-18 and 2019-11-04 not maintaining ORDER.

(这最后2个日期2019-11-18和2019-11-04不维护ORDER。)

How can I solve this problem ?

(我怎么解决这个问题 ？)

  ask by Satu Sultana translate from so

Answer 1

You may add an ORDER BY clause to your union query, after placing both halves of the union in parentheses:

(在将联合的两半放在括号中之后，可以在联合查询中添加ORDER BY子句：)

(SELECT IFNULL(t1.created, DATE(ex.created)) AS Date, SUM(t1.amount) AS ReceiveAmount,
     ex.amount AS ExpensesAmount 
FROM transactions as t1
LEFT JOIN
...
)

UNION ALL

(SELECT IFNULL(t1.created, DATE(ex.created)), SUM(t1.amount), ex.amount
FROM transactions as t1
RIGHT JOIN
...
)
ORDER BY Date

I assume here that you really want a UNION ALL , and not a UNION .

(我在这里假设您确实想要一个UNION ALL ，而不是UNION 。)

Note that in most other RDBMS you would have to use a formal subquery to apply an ORDER BY clause to the entire union query.

(请注意，在大多数其他RDBMS中，您必须使用正式的子查询才能将ORDER BY子句应用于整个联合查询。)