MySQL ３つのテーブルを結合 差を降順で並べ替えたい

Question

【productsテーブル】
id
name

【ashopsテーブル】--A店で調査した商品価格と日時
id
product_id
price
research_date

【bshopsテーブル】--B店で調査した商品価格と日時
id
product_id　
price　
research_date

同じproducts_idで
ashops.research_dateが最新のashops.price（A店の最新価格）と
bshops.research_dateが最新のbshops.price（B店の最新価格）の差を
降順で並べ替えたいのですがSQL文の書き方がわかりません。

SELECT P.name,A.price,max(A.reserch_date),B.price,max(B.reserch_date),A.price-B.price
from products as P
INNER JOIN ashops as A on P.id = A.product_id
JOIN bshops as B on P.id = B.product_id
GROUP BY P.id;

ここまでは書いてみましたが、これだと最新のreserch_dateと同じ行のpriceが出力されません。
↓SQL出力結果

↓bshopのデータ

テーブル構造などが変わってもかまいません。良い方法があればご教授ください。

Comments

[yambejp]

テーブルとデータのサンプルとどういう結果を想定しているかの
具体例を提示ください
Cakeはとりあえず保留でSQLだけで考えるところからはじめてください

[azuapricot]

PHPっていうよりはただのSQLにみえます

[Orlofsky]

MySQLのテーブル定義は SHOW CREATE TABLE products などに修正してください。
https://www.dbonline.jp/mysql/table/index2.html

[tosba]

ありがとうございます。SQLの質問に変更しました

[yambejp]

ちょっと理解が深まっていないようなので
サンプルデータは以下の形式でつけてください
create table products (・・・・)；
insert into products values(・・・・),(・・・・),・・・・

Answer 1

MySQLのバージョンにもよりますが、ROW_NUMBER()を使用して、各ショップのproduct_idごとにresearch_date の降順で項番を振ることでそれぞれのproduct_idの最新データを取得することができます。

コード
SELECT  
    P.name,
    A.price,
    A.research_date,
    B.price,
    B.research_date,
    A.price-B.price
FROM 
    products as P
INNER JOIN (
    SELECT
        product_id,
        research_date,
        price
    FROM (
    SELECT
        product_id,
        research_date,
        price,
        ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY research_date DESC) N
    FROM
        ashops 
    ) AS INNER_A 
    WHERE N = 1
    )AS A on P.id = A.product_id
INNER JOIN (
    SELECT
        product_id,
        research_date,
        price
    FROM (
    SELECT
        product_id,
        research_date,
        price,
        ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY research_date DESC) N
    FROM
        bshops 
    ) AS INNER_B 
    WHERE N = 1
    )AS B on P.id = B.product_id