MariaDB講座 / 複数表JOINで合計を重複させない

複数表をJOINして集計する前に、結合後の一行が何を表すかを確認します。注文に明細を結ぶと注文の値は明細数だけ繰り返されるため、送料などは注文単位で集計する必要があります。

顧客・注文・明細・商品の事実を分ける

四表を結ぶ画面で最終一行が明細なら、顧客名と注文送料は繰り返し、quantity×unit_priceは明細ごとの値です。現在の商品価格は購入時単価の代わりにはなりません。履歴を残す関連設計が金額計算の前提です。

注文数だけでなく、一注文に一度の送料・割引も増える可能性があります。残す母集団はLEFT JOIN、指標間の照合は売上レポートと合わせて検討します。

送料と過去単価のあるデータを作る

クライアントの接続手順でcourse_userとしてshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;を確認します。以下には必要な四表の作成とデータがすべてあり、以前の記事の表を必要としません。UTF-8の.sqlファイルで実行し、同名の表があれば定義と内容を調べてから判断します。

USE shopcourse;
CREATE TABLE c28_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c28_order_customer FOREIGN KEY (customer_id)
  REFERENCES c28_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c28_item_order FOREIGN KEY (order_id)
  REFERENCES c28_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c28_item_product FOREIGN KEY (product_id)
  REFERENCES c28_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c28_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c28_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c28_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c28_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

顧客名・都市・商品名は原文の韓国語サンプルです。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。文字列を維持して実行し、説明とは安定したIDで照合できます。

paidの注文101・102・103は明細2・1・2行で計5行、各送料は1000です。この小さな件数を追い、何回足されるか観察します。

四表を関連キーで結ぶ

商品名は現在の商品表、金額は明細の単価から計算します。

SELECT o.order_id,c.customer_name,p.product_name,i.quantity,i.unit_price,
 i.quantity*i.unit_price AS line_total
FROM c28_orders o JOIN c28_customers c ON c.customer_id=o.customer_id
JOIN c28_items i ON i.order_id=o.order_id
JOIN c28_products p ON p.product_id=i.product_id
WHERE o.status='paid' ORDER BY o.order_id,i.product_id;

101の品物は7000+4500=11500、102は8000、103は3500+16000=19500、合計39000です。現在価格が変わってもこの金額は購入時単価から再現できます。

注文単位の値が増えることを確かめる

結合した明細で集計します。

SELECT COUNT(*) AS joined_rows,COUNT(DISTINCT o.order_id) AS orders_count,
 SUM(o.shipping_fee) AS duplicated_shipping,SUM(i.quantity*i.unit_price) AS sales
FROM c28_orders o JOIN c28_items i ON i.order_id=o.order_id
WHERE o.status='paid';

joined_rows=5、orders_count=3、duplicated_shipping=5000、sales=39000です。正しい送料は三注文で3000なので、二回余計に足しています。COUNT(DISTINCT order_id)で注文数だけを直しても、送料は直りません。

SUM(DISTINCT shipping_fee)では同じ1000という値を全注文から一つにして、1000になります。値の重複除去と「一注文を一度数える」は別です。DISTINCTを膨らんだ合計の万能な修正にしません。

明細を注文ごとに集計してから結ぶ

派生表を一注文一行にします。

SELECT COUNT(*) AS orders_count,SUM(o.shipping_fee) AS shipping_total,
 SUM(d.goods_total) AS goods_total
FROM c28_orders o
JOIN (SELECT order_id,SUM(quantity*unit_price) AS goods_total
 FROM c28_items GROUP BY order_id) d ON d.order_id=o.order_id
WHERE o.status='paid';

orders_count=3、shipping_total=3000、goods_total=39000になります。明細が増えても、送料を明細数だけ繰り返さずに済みます。この例は明細のあるpaid注文が対象です。明細なしも必要ならLEFT JOINと未集計値の方針を選びます。

結合するたび多重度を確認する

一表ずつ追加してキーと件数を表示します。異常な増加なら非一意キーや条件の欠落を疑います。二つの明細集合を同じ親へそのまま結ぶと件数の積になり得るため、それぞれ必要な単位へ先にまとめます。一つの注文を手計算で照合してから、大きな報告へ使います。

再実習はDB名、実際の定義と保存行を確認し、必要なデータを保存してから行います。この記事の削除してよい四表だけを、参照する子から親の順に削除します。DROP TABLEは定義とデータを消し、通常のROLLBACKでは戻せません。

DROP TABLE shopcourse.c28_items;
DROP TABLE shopcourse.c28_orders;
DROP TABLE shopcourse.c28_products;
DROP TABLE shopcourse.c28_customers;

関連する公式リファレンス

• join syntax（公式ドキュメント）