时间戳

首页 > 解决方案 > Sum price based on date and print based on clubs in mysql

mysql - Sum price based on date and print based on clubs in mysql

问题描述

I have two tables and those are football and football assets.

The Football looks like:-

id | pair | name | date | price | sell | fetched
---------------------------------------------------------------------------------
6    |  luca-antonelli  | Luca Antonelli | 2018-12-20 04:57:36 | 0.50 | 0.47 | 1
7    |  luca-antonelli  | Luca Antonelli | 2018-09-06 12:39:58 | 0.65 | 0.52 | 1
8    |  luca-antonelli  | Luca Antonelli | 2018-10-15 16:45:28 | 0.59 | 0.49 | 1
9    |  ryan-fraser     | Ryan Fraser    | 2018-03-28 08:56:57 | 5.51 | 0.47 | 1
10   |  ryan-fraser     | Ryan Fraser    | 2018-04-20 06:19:07 | 7.28 | 0.49 | 1
11   |  jordon-ibe      | Jordon Ibe     | 2018-10-14 22:52:58 | 11.23 | 0.17 | 1
12   |  jordon-ibe      | Jordon Ibe     | 2018-11-25 19:37:26 | 2.31 | 0.29 | 1

So you can see there are three rows so from here it will pick the price based on date The football assets looks like:-

    id | pair | Club
   -----------------------------------
    86 |  luca-antonelli  | Liverpool
    87 |  ryan-fraser     | Liverpool
    88 |  jordon-ibe      | Liverpool

So the club liverpool has 3 players. If you look into the football table you will see they have different prices. So it will pick the latest price based on datetime for each player and then add it up. pair is the common column between two tables. So there are many records like this. I want to sum the price of all the players of that club but the price will be the latest. price will take it from date column from football table. The output looks like:-

club | price
------------------
Liverpool | 28.07

The query I have tried is:-

SELECT club, SUM(price) as price from football JOIN football_assets ON football_assets.pair = football.pair WHERE club IN ('Paris Saint-Germain', 'Lille', 'Lyon')

One player have many prices but I want only their latest price to be summed. The above query is summing all the prices. I want that optimization.

Any help will be appreciated.

标签： mysqlsql

解决方案

通过使用子查询尝试如下

select fa.club,sum(a.price) as price from

  (
  select f.* football f
   where f.date=(select max(date) from football f1 where f1.pair=f.pair)
  ) a join  footballassets fa on a.pair=fa.pair
  group by fa.club

通过使用相关子查询，我选择了所有最新价格，然后使用加入资产表来获取俱乐部

推荐阅读