如何在 mysql 中更正此查询，当我使用 MAX 时，它给了我第一笔交易 [英] how to correct this query in mysql it's giving me the first transaction when Im using MAX

问题描述

SELECT a.accountid,a.accountname, IFNULL(accountbalance,0)
FROM transactionstable tt
JOIN (SELECT accountid ,MAX(transactiondate) transactiondate
        FROM transactionstable 
        WHERE transactiondate<"2014-04-16"
        GROUP BY accountid ) t
USING (transactiondate ,accountid )
RIGHT JOIN allaccounts a  ON (tt.accountid=a.accountid)
GROUP BY a.accountid
ORDER BY a.accountname ;

我有很多交易，其中 (transactiondate ,accountid) 所以它不是唯一的，我需要最后一个交易(transactionid 是唯一的)但我不知道在哪里添加它，例如:

I have many transactions where (transactiondate ,accountid) so its not unique, i need the last transaction (transactionid is unique) but i dont know where to add it to have for example:

trnx id        accountid             date              accbalance
  1               222              2014-04-16            2000
  2               222              2014-04-16            1900
  3               222              2014-04-16            1850
  4 ...

所以我需要得到最后的余额:1850，而我得到的第一个余额是 2000 :/

so I need to get the last balance: 1850 while im getting the first balance 2000 :/

非常感谢您的帮助

编辑

我得到了正确答案，但花费了太多时间(37 秒，因为该表包含大约 400 万条记录):

i got the correct answer but its taking too much time (37secs because the table contains around 4millions records):

SELECT a.accountid,a.accountname, IFNULL(accountbalance,0)
FROM transactionstable tt
JOIN (SELECT accountid ,MAX(transactiondate) transactiondate,MAX(transactionid) transactionid
        FROM transactionstable
        WHERE transactiondate<"2014-04-15 23:59:59" 
        GROUP BY accountid
        HAVING MAX(transactiondate) ) t
USING ( transactionid )
RIGHT JOIN allaccounts a  ON (tt.accountid=a.accountid)
ORDER BY a.accountname ;

推荐答案

您可以使用 not exists 获取最后一笔交易的方法更轻松地执行此逻辑:

You can more easily do this logic using the not exists method of getting the last transaction:

SELECT a.accountid, a.accountname, IFNULL(accountbalance,0)
FROM allaccounts a LEFT JOIN
     transactionstable tt
     ON tt.accountid = a.accountid
WHERE NOT EXISTS (SELECT 1
                  FROM transactionstable tt2
                  WHERE tt2.accountid = tt2.accountid and
                        (tt2.transactiondate > tt.transactiondate or
                         tt2.transactiondate = tt.transactiondate and
                         tt2.trans_id > tt.trans_id
                        )
                 )
ORDER BY a.accountname;

如果你只能使用transactionid，那么试试这个:

If you can just use transactionid, then try this:

SELECT a.accountid, a.accountname, IFNULL(accountbalance,0)
FROM allaccounts a LEFT JOIN
     transactionstable tt
     ON tt.accountid = a.accountid
WHERE NOT EXISTS (SELECT 1
                  FROM transactionstable tt2
                  WHERE tt2.accountid = tt.accountid and
                        tt2.transactionid > tt.transactionid
                 )
ORDER BY a.accountname;

并且，还要在 transactiontable(accountid, transactionid) 上创建索引.

And, also create an index on transactiontable(accountid, transactionid).

这篇关于如何在 mysql 中更正此查询，当我使用 MAX 时，它给了我第一笔交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！