在使用 Union 查询时创建运行余额总计 [英] Create a Running Balance Total on Query with Union

问题描述

我们有一个网络应用程序可以跟踪客户的奖励积分.我们正在使用 MySQL 数据库.我最初有一个查询，它从单个表中提取数据，并显示每笔交易中的点数以及余额 (RunningTotal)，代码如下:

We have a webapp that tracks rewards points for customers. We are using a MySQL database. I originally had a query that pulled data from a single table and showed the amount of points in each transaction along with the balance (RunningTotal) the code is below:

SELECT DateSubmitted
, PointTotal as Points
,   ( SELECT SUM( PointTotal ) 
        FROM ptrans_detail x 
            WHERE x.CustID = a.CustID
            AND ( x.DateSubmitted < a.DateSubmitted OR x.DateSubmitted = a.DateSubmitted) ) AS RunningTotal 
, comment 
   FROM ptrans_detail a 
    WHERE CustID='10009' 
    Order by TransID Desc

这工作正常，直到发现 ptrans_detail 表中不存在一些条目并且该问题发布在此处:从带有一些重复记录的 2 个 MySQL 表中查询数据

This worked fine until it was discovered that there were some entries that didn't exists in the ptrans_detail table and that question was posted here: Query Data from 2 MySQL tables with some duplicate records

按照建议，我使用 UNION 将 2 个表中的 2 个查询组合起来以获取所有记录，该查询是:

As suggested, I used UNION to combine 2 queries from 2 tables to get ALL the records, that query is:

SELECT CustID
      , DateSubmitted
      , Type
      , Points
          FROM `trans_summary`
            WHERE CustID = '10009'
UNION

SELECT CustID
, DateSubmitted
, Type
, PointTotal 
    FROM `ptrans_detail` 
       WHERE CustID = '10009'
            and DateSubmitted NOT IN 
           (SELECT DateSubmitted FROM 
             `trans_summary` 
              WHERE CustID = '10009')

效果很好，但现在我想在第一个查询中添加 RunningTotal.这可能吗?

That worked great but now I would like to add the RunningTotal to this like in the first query. Is this possible?

推荐答案

我建议你使用union all，除非你真的需要union.

I would advise you to use union all, unless you really need union.

在 MySQL 中获得运行总和的最简单方法是使用变量:

The easiest way to get a running sum in MySQL is to use variables:

SELECT t.*,
       (@sump := if(@c = CustId, @sump + Points,
                    if(@c := CustId, Points, Points)
                   )
       ) as runningTotal
FROM (SELECT CustID, DateSubmitted, Type, Points
      FROM trans_summary
      WHERE CustID = '10009'
      UNION ALL   -- Maybe it should be `UNION`
      SELECT CustID, DateSubmitted, Type, PointTotal 
      FROM ptrans_detail 
      WHERE CustID = '10009' AND
            DateSubmitted NOT IN (SELECT ts.DateSubmitted FROM trans_summary ts WHERE ts.CustID = '10009')
    ) t CROSS JOIN
    (SELECT @c := -1, @sump := 0) params
ORDER BY CustId, DateSubmitted;

这篇关于在使用 Union 查询时创建运行余额总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！