mysql - 不要使用 mysql 从具有两个表的数据库中选择数据

Question

我有2个表sale和receipt。表结构和结果结构如下所示。

销售

date          total     sale_type

15-8-2014      50       credit

16-8-2014      100      credit

17-8-201       200      return

18-8-2014      300      return

收据

  date           net_amount


  15-8-2014         100

  16-8-2014         200

  17-8-2014         300

结果

date            sale      receipt


15-8-2014       50         100

16-8-2014       100        200

17 -8-2014      200        300

18-8-2014       300 

使用我的查询，我得到了这些结果结构，但我还想得到 sale_type='credit' 和 sale_type='return"情况下的总和。有人帮助我吗？

我的查询是

    select date,total,net_amount from
     (select  date, total, null as net_amount, 2 as sort_col from sale union
all select date, 
null as total, net_amount as net_amount, 1 as sort_col from receipt)
 as a order by date desc, sort_col desc

Answer 1

以下查询(使用 JOIN 和 CASE 是否能获得预期结果？

SELECT
    s.date,
    s.total AS sale,
    r.net_amount AS receipt,
    SUM(CASE
        WHEN s.sale_type = 'credit' THEN s.total
        ELSE 0
    END) AS sum_credit,
    SUM(CASE
        WHEN s.sale_type = 'return' THEN s.total
        ELSE 0
    END) AS sum_return
FROM sale s
LEFT JOIN receipt r
ON s.date = r.date
GROUP BY s.date, s.total, r.net_amount;