MySQL における制限関数と合計関数の混在使用の問題の詳細な説明

序文

今日、注文データを同期した後、同僚は、合計注文金額とデータソースの合計金額に差があったため、LIMIT 関数と SUM() 関数を使用して現在のページの合計金額を計算し、特定の注文の合計金額を他の当事者と比較することを選択しました。しかし、計算された金額はページ化された注文の合計金額ではなく、すべての注文の合計金額であることがわかりました。

データベースのバージョンは MySQL 5.7 です。以下では、例を使用して発生した問題を確認します。

問題のレビュー

このレビューでは、非常に単純な注文テーブルを例として使用します。

データ準備

注文テーブルの作成ステートメントは以下のとおりです（ここでは面倒なので自動増分 ID を使用します。実際の開発では自動増分 ID を注文 ID として使用することは推奨されません）

テーブル「order」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '注文ID',
 `amount` 小数点(10,2) NOT NULL COMMENT '注文金額',
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

100の金額を挿入するSQLは次のようになります（10回実行）

`order`(`amount`) VALUES (100) に INSERT します。

したがって、合計金額は 10 * 100 = 1000 になります。

問題のあるSQL

limitを使用してページ内のデータを照会し、sum()関数を使用して現在のページの合計量を計算します。

選択 
  SUM(`金額`)
から
  「注文」
`id` で並べ替え
制限5;

前述のように、期待される結果は 5*100=500 ですが、実際の結果は 1000.00 です (小数点はデータ型によるものです)。

トラブルシューティング

実際、SELECT ステートメントの実行順序をある程度理解していれば、返される結果がすべての注文の合計金額である理由をすぐに判断できます。次に、問題のある SQL の実行シーケンスに基づいて問題を分析します。

1. FROM: 最初に FROM 句が実行され、クエリが注文テーブルであることが決定されます。
2. SELECT: SELECT 句は 2 番目に実行される句であり、この時点で SUM() 関数も実行されます。
3. ORDER BY: ORDER BY 句は 3 番目に実行される句であり、結果は 1 つだけ、つまり注文の合計金額になります。
4. LIMIT: LIMIT句は最後に実行され、この時点で結果セットには結果が1つだけあります（注文の合計金額）

補足コンテンツ

SELECT文の実行順序は次のとおりです。

1. <左テーブル> から
2. ON <結合条件>
3. <結合タイプ> JOIN <右テーブル>
4. WHERE <where_condition>
5. GROUP BY <group_by_list>
6. HAVING <having_condition>
7. 選択
8. DISTINCT <選択リスト>
9. ORDER BY <order_by_condition>
10. LIMIT <制限数>

解決

ページング データをカウントする必要がある場合 (SUM() 関数に加えて、一般的な COUNT()、AVG()、MAX()、および MIN() 関数にもこの問題があります)、サブクエリを使用して処理することを選択できます (PS: ここではメモリ計算は考慮されておらず、目的はデータベースを使用してこの問題を解決することです)。上記の問題の解決策は次のとおりです。

選択 
  SUM(o.金額)
から
  （選択 
    「金額」
  から
    「注文」
  `id` で並べ替え
  制限5) AS o;

操作の戻り値は 500.00 です。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。