SQL見るだけクエリ#014 一番高額で売れている商品を注文ID別で抽出する。MAX関数

記入日：2023-01-15　編集日：2023-01-15

SQL見るだけクエリ。今回は売上テーブルから、注文別に一番販売額の高い商品を取得してみようと思います。

以下のsql_orderテーブルには、注文商品ごとにレコードがインサートされています。

売上レコードを、オーダーごとにグループ化しながら、その中で一番高い販売額の商品のみを取得していきます。 複数商品購入された中で何を沢山お金を出して買ったのかを判断する事ができます。

さていつもの様に、テーブルの情報を取得するSQLに関して、注目する部分は、以下の9~28行目です。

まずは、売上レコード（sql_orderテーブル）から、グループ毎（order_id）に販売額（price*quantity）の最高値を取得します。

これだけですと、最高額の一覧は取得できますが、idや名前（name）などが取得できませんので、 INNER JOINを使って、その他のカラムも取得できるように、結合していきます。

try {
    include_once '../model/class.php';

    // データベース接続
    $database = new Database();
    $db = $database->connect();

    // SQLを準備し、パラメータをバインドする
    $query = 'SELECT
        A.id,
        A.name,
        A.item_id,
        A.price,
        A.quantity,
        A.price*A.quantity AS total,
        A.order_id
    FROM
        sql_order AS A
    INNER JOIN (SELECT
        order_id,
        MAX(price*quantity) AS MaxPrice
    FROM
        sql_order
    GROUP BY
        order_id) AS B
    ON A.order_id = B.order_id
    AND A.price*A.quantity = B.MaxPrice
    ORDER BY id ASC';
    $stmt = $db->prepare($query);

    // 実行する
    $stmt->execute();

    // 結果を取得する
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);

    // データベースから切断する
    $database->disconnect();

} catch(PDOException $e) {
    echo $e->getMessage();
}

これを実行すると、二次元連想配列で、取得することができます。

            Array
(
    [0] => Array
        (
            [id] => 3
            [name] => 伊藤 明日香
            [item_id] => sku-0003
            [price] => 9000
            [quantity] => 1
            [total] => 9000
            [order_id] => 1
        )

    [1] => Array
        (
            [id] => 6
            [name] => 原田 和美
            [item_id] => sku-0003
            [price] => 9000
            [quantity] => 2
            [total] => 18000
            [order_id] => 2
        )

    [2] => Array
        (
            [id] => 8
            [name] => 高橋 由美子
            [item_id] => sku-0002
            [price] => 2500
            [quantity] => 5
            [total] => 12500
            [order_id] => 3
        )

    [3] => Array
        (
            [id] => 12
            [name] => 田中 明日香
            [item_id] => sku-0003
            [price] => 9000
            [quantity] => 2
            [total] => 18000
            [order_id] => 4
        )

    [4] => Array
        (
            [id] => 15
            [name] => 小林 美佐子
            [item_id] => sku-0003
            [price] => 9000
            [quantity] => 2
            [total] => 18000
            [order_id] => 5
        )

    [5] => Array
        (
            [id] => 18
            [name] => 渡辺 雄一
            [item_id] => sku-0003
            [price] => 9000
            [quantity] => 1
            [total] => 9000
            [order_id] => 6
        )

    [6] => Array
        (
            [id] => 19
            [name] => 佐藤 智子
            [item_id] => sku-0001
            [price] => 3000
            [quantity] => 12
            [total] => 36000
            [order_id] => 7
        )

    [7] => Array
        (
            [id] => 24
            [name] => 坂本 純子
            [item_id] => sku-0003
            [price] => 9000
            [quantity] => 3
            [total] => 27000
            [order_id] => 8
        )

    [8] => Array
        (
            [id] => 26
            [name] => 加藤 和子
            [item_id] => sku-0002
            [price] => 2500
            [quantity] => 8
            [total] => 20000
            [order_id] => 9
        )

    [9] => Array
        (
            [id] => 30
            [name] => 山口 晴子
            [item_id] => sku-0003
            [price] => 9000
            [quantity] => 3
            [total] => 27000
            [order_id] => 10
        )

)