mysql - 合并两个查询以在 mysql 中获取结果

Question

我对此感到困惑:

SELECT request_id, SUM( cc_amount ) AS amt,
       DATE_FORMAT( transaction_datetime,  '%b %y' ) AS tdate
  FROM ee_request_cc
 GROUP BY DATE_FORMAT( transaction_datetime,  '%b %y' ) 

UNION 

SELECT request_id, SUM( request_amount ) AS amt,
       DATE_FORMAT( transaction_date,  '%b %y' ) AS tdate
  FROM ee_request_paypal
 GROUP BY DATE_FORMAT( transaction_date,  '%b %y' )

我得到:

id    amt    tdate
20  86.00   Mar 12
80   5.00   Apr 12
23  55.00   Mar 12

而我想添加 3 月和 4 月的所有金额，例如:

id     amt   tdate
20  141.00  Mar 12
80    5.00  Apr 12

请建议我更改查询。

Answer 1

试试这个:

select sum(amt), DATE_FORMAT(tdate, '%b %y') from (
    SELECT amount1 AS amt, transaction_dt AS tdate
    FROM table1
    UNION ALL
    SELECT request_amount, tr_date
    FROM table2
) s
GROUP BY YEAR(tdate), MONTH(tdate)

请注意，您不应该从分组结果中获取 ID...您会选择哪个？

此外，按数字分组必须比将日期转换为字符串然后按字符串分组更快。