MySQL Vertical Horizo​​ntal Sum 和 Vertical Sum 同时

Question

我有 2 个表，其中包含一组数据，如下所示，我想获得结果，该结果将在字段 balance 中进行计算:

我卡在了 balance 字段，如何让 balance 运行？

tblIn

in_date    | code    | in_qty  
-----------|---------|---------
2016-04-01 | aaa     | 100
2016-04-03 | aaa     | 200
2016-04-06 | aaa     | 400

tblOut

out_date   | code    | out_qty  
-----------|---------|---------
2016-04-02 | aaa     | 100
2016-04-08 | aaa     | 400

结果

date       | code    | in_qty   | out_qty  | balance
-----------|---------|----------|----------|---------
2016-04-01 | aaa     | 100      | 0        | 100
2016-04-02 | aaa     | 0        | 100      | 0
2016-04-03 | aaa     | 200      | 0        | 200
2016-04-06 | aaa     | 400      | 0        | 600
2016-04-08 | aaa     | 0        | 400      | 200

查询

 SELECT
    t.date,
    t. CODE,
    t.in_qty,
    t.out_qty
FROM
    (
        SELECT
            date,
            in_qty,
            0 AS out_qty 
    FROM tblIn
        UNION ALL
        SELECT
            date,
            0 AS in_qty,
            out_qty
        FROM tblOut
    ) t
ORDER BY date ASC

Answer 1

嗯，让我们这样做吧;)

CREATE TABLE IF NOT EXISTS `tblIn` (
  `in_date` date DEFAULT NULL,
  `code` char(50) DEFAULT NULL,
  `in_qty` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `tblOut` (
    `out_date` date DEFAULT NULL,
    `code` char(50) DEFAULT NULL,
    `out_qty` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后试试这个，也许可行；)

SELECT TMP.date, TMP.code, TMP.in_qty, TMP.out_qty, @BALANCE:=@BALANCE + TMP.BALANCE AS balance
FROM (
    SELECT out_date as date,
           code,
           0 as in_qty,
           out_qty,
           0-out_qty as balance
    FROM tblOut
    UNION
    SELECT in_date as date,
           code,
           in_qty,
           0 as out_qty,
           in_qty as balance
    FROM tblIn) TMP,
    (SELECT @BALANCE:=0) B
ORDER BY TMP.date