mysql - 比较日期以找出 mysql 中的 max(date) 时出错

Question

我有这个查询:

find per account, his last transaction ,for month April, for all accounts using dollars. The results should type: a) the account's code b) the last transaction's code (if there are two transactions in one day, you should type the code fore the latest transaction) c) the last transaction's date.

我的问题是:如何比较一个帐户的所有日期？

    SELECT DISTINCT 
         accounts.account_id
         ,transactions.trn_code 
         ,transactions.trn_date
    FROM accounts 
         inner join transactions on accounts.account_id = transactions.account_id
    WHERE 
         accounts.account_currency = 'DOL' 
         and  transactions.trn_date = MAX(transactions.trn_date)
         and transactions.trn_date >= 01/04/2016 and transactions.trn_date <= 31/4/2016

这会导致错误(当然)，因为我不能那样使用 MAX。但我想我必须找到每个帐户本月的最大交易日期。这个想法对吗？我怎样才能做到这一点？有什么想法吗？

Answer 1

嗨，你可以试试这个

 SELECT accounts.account_id, transactions.trn_code , transactions.trn_date
    FROM accounts inner join transactions on accounts.account_id = transactions.account_id
    WHERE accounts.account_currency = 'DOL'   
and transactions.trn_date >= 01/04/2016 and transactions.trn_date <= 31/4/2016 and transactions.trn_date=(Select MAX(transactions.trn_date) from transactions)
group by transactions.trn_code , transactions.trn_date,accounts.account_id