我想通过使用clientId获取该帐号的最新交易。我还没有正确的解决方案。
我已经尝试选择 *从 transactions
中选择 * accountno
=" a6b55"订单 clientids
desc limit 1;
clientids date time cardno accountno transactiontype currbalance
---------------------------------------------------------------------------
1 2019-02-26 5:18AM 865505 A6B55 withdraw 20,000
2 2019-02-26 6:15PM 865505 A6B55 deposit 30,000
3 2019-02-26 9:10PM 78805 6979A deposit 10,0000
我希望给我帐户进行的最新交易。例如,就像您在我的表帐户上看到的那样,没有A6B55进行了两项交易,而该帐户的客户端是1和2,我只想获得2号客户端,日期,时间,cardno,cardno,transactionType和curralance。谢谢!
请尝试此解决方案;
create table transactions (clientid INT PRIMARY KEY, date DATE, time TIME, CardNo INT, accountno VARCHAR(100), Transactiontype VARCHAR(20), curbalance float);
mysql> desc transactions;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| clientid | int(11) | NO | PRI | NULL | |
| date | date | YES | | NULL | |
| time | time | YES | | NULL | |
| CardNo | int(11) | YES | | NULL | |
| accountno | varchar(100) | YES | | NULL | |
| Transactiontype | varchar(20) | YES | | NULL | |
| curbalance | float | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> insert into transactions values(1, '2019-02-26', '05:18:00', 865505, 'A6B55', 'withdraw', 20000);
mysql> insert into transactions values(2, '2019-02-26', '18:15:00', 865505, 'A6B55', 'deposit', 30000);
mysql> insert into transactions values(3, '2019-02-26', '21:10:00', 78805, '6979A', 'deposit', 100000);
mysql> select * from transactions;
+----------+------------+----------+--------+-----------+-----------------+------------+
| clientid | date | time | CardNo | accountno | Transactiontype | curbalance |
+----------+------------+----------+--------+-----------+-----------------+------------+
| 1 | 2019-02-26 | 05:18:00 | 865505 | A6B55 | withdraw | 20000 |
| 2 | 2019-02-26 | 18:15:00 | 865505 | A6B55 | deposit | 30000 |
| 3 | 2019-02-26 | 21:10:00 | 78805 | 6979A | deposit | 100000 |
+----------+------------+----------+--------+-----------+-----------------+------------+
mysql> select * from transactions where accountno = 'A6B55' ORDER BY time desc LIMIT 1;
+----------+------------+----------+--------+-----------+-----------------+------------+
| clientid | date | time | CardNo | accountno | Transactiontype | curbalance |
+----------+------------+----------+--------+-----------+-----------------+------------+
| 2 | 2019-02-26 | 18:15:00 | 865505 | A6B55 | deposit | 30000 |
+----------+------------+----------+--------+-----------+-----------------+------------+
祝您有美好的一天:(欢呼...