新专栏中的 MySQL 摘要

Question

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

我被困在余额字段中，如何让余额运行？

tblIn

in_date    | code    | in_qty  
-----------|---------|---------
2016-04-01 | aaa     | 100
2016-04-02 | 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     | 100      | 0        | 200
2016-04-02 | aaa     | 0        | 100      | 100
2016-04-03 | aaa     | 200      | 0        | 300
2016-04-06 | aaa     | 400      | 0        | 700
2016-04-08 | aaa     | 0        | 400      | 300

查询(感谢@1000111)

SELECT 
t.*,
@prevBalance := (t.in_qty - t.out_qty) + IFNULL(@prevBalance,0) AS balance
FROM 
(
    SELECT 
        in_date date,
        code,
        in_qty,
        0 AS out_qty
    FROM tblin

    UNION 

    SELECT 
        out_date,
        code,
        0,
        out_qty
    FROM tblout
) t , (SELECT @prevBalance := NULL) var
ORDER BY t.date;

这个查询将得到RESULT中的结果，但是如果我想要这种结果怎么办？ MySQL 会将截至 2016 年 4 月 3 日的摘要作为单行，并继续提供下一个日期的详细信息。

结果1

date       | code    | in_qty   | out_qty  | balance
-----------|---------|----------|----------|---------
2016-04-03 | aaa     | 0        | 0        | 300
2016-04-06 | aaa     | 400      | 0        | 700
2016-04-08 | aaa     | 0        | 400      | 300

Answer 1

尝试这个查询..我用2016-04-02测试了它，但请随意更改注释的一行，将日期更改为您喜欢的任何日期

SELECT @startDate date,
       -- all this below to determine code since code has to come from somewhere
       -- for now we'll just select code of the latest date that is before
       -- or equal to @startDate
       (SELECT TCode.code FROM
          (SELECT code,in_date FROM tblin   WHERE in_date <= @startDate
           UNION ALL
           SELECT code,out_date FROM tblout WHERE out_date <= @startDate)TCode
         ORDER BY TCode.in_date DESC
        LIMIT 1
        ) as Code,
        0,0,
        balance 
        FROM
        (SELECT @prevbalance :=(SELECT SUM(in_qty) FROM tblin,
-- Change the date in below line to any date you desire as @startDate is used throughout this whole query
                                (SELECT @startDate := DATE('2016-04-02'))TDATE WHERE in_date <= @startDate)-
                               (SELECT SUM(out_qty) FROM tblout WHERE out_date <= @startDate) as balance)T4
UNION ALL
SELECT * FROM
  (SELECT T.*,@balance := @balance + (t.in_qty - t.out_qty) AS balance
   FROM 
    (SELECT in_date date,code,in_qty,0 AS out_qty FROM tblin WHERE in_date > @startDate
     UNION ALL
     SELECT out_date,    code,     0,     out_qty FROM tblout WHERE out_date > @startDate
    )T,(SELECT @balance:=@prevbalance)initial
   ORDER BY T.date ASC,T.in_qty DESC
  )T3;

sqlfiddle

更新要防止 NULL，只需使用 IFNULL()

SELECT @startDate date,
       -- all this below to determine code since code has to come from somewhere
       -- for now we'll just select code of the latest date that is before
       -- or equal to @startDate
       (SELECT TCode.code FROM
          (SELECT code,in_date FROM tblin   WHERE in_date <= @startDate
           UNION ALL
           SELECT code,out_date FROM tblout WHERE out_date <= @startDate)TCode
         ORDER BY TCode.in_date DESC
        LIMIT 1
        ) as Code,
        0,0,
        balance 
        FROM
        (SELECT @prevbalance :=IFNULL((SELECT SUM(in_qty) FROM tblin,
-- Change the date in below line to any date you desire as @startDate is used throughout this whole query
                                                               (SELECT @startDate := DATE('2016-03-02'))TDATE WHERE in_date <= @startDate)-
                                      (SELECT SUM(out_qty) FROM tblout WHERE out_date <= @startDate),0
                                     ) 
                               as balance
        )T4
UNION ALL
SELECT * FROM
  (SELECT T.*,@balance := @balance + (t.in_qty - t.out_qty) AS balance
   FROM 
    (SELECT in_date date,code,in_qty,0 AS out_qty FROM tblin WHERE in_date > @startDate
     UNION ALL
     SELECT out_date,    code,     0,     out_qty FROM tblout WHERE out_date > @startDate
    )T,(SELECT @balance:=@prevbalance)initial
   ORDER BY T.date ASC,T.in_qty DESC
  )T3;