1

I am using MySQL 5.6, and creating a view. It seems MySQL 5.6 view does not support subquery inside a view. I have two tables like the following:

table 1 account balance date time in out 408 100.00 2018-03-09 11:36:47 100.00 0.00 408 86.00 2018-03-09 11:54:48 0.00 14.00 408 74.00 2018-03-09 11:54:48 0.00 12.00 408 21.00 2018-03-09 11:54:48 0.00 13.00 408 11.00 2018-03-09 11:54:48 0.00 10.00 408 0.00 2018-03-09 11:54:48 0.00 11.00 408 13000000.00 2018-03-09 19:15:04 13000000.00 0.00 408 12999880.00 2018-03-10 00:51:37 0.00 120.00 408 12999640.00 2018-03-10 00:51:48 0.00 240.00

table2

account name
999 bank1
408 bank2

The view is created like this:

create view test
select table1.account, table2.name, table1.balance, table1.date from (table1 join table2) where table1.account=table2.account group by table1.account, table1.date

The problem is that the view picked out the first time, showed the first balance. But I want the max time on that day. For example, the view results are like:

408 bank1 100 2018-03-09
408 bank1 12999880.00 2018-03-10

The MySQL 5.6 does not allow me to use a subquery. So is there another way to show the correct balance like this:

408 bank1 13000000.00 2018-03-09
408 bank1 12999640.00 2018-03-10

which shows the lastest balance of the last record on that day.

ttkrpink
  • 109
  • 1
  • 10
  • Possible duplicate of [MySQL: View with Subquery in the FROM Clause Limitation](https://stackoverflow.com/questions/206062/mysql-view-with-subquery-in-the-from-clause-limitation) – P.Salmon Mar 15 '18 at 07:24

0 Answers0