MariaDB-Kurs / Mehrere Tabellen ohne doppelte Anzahlen und Summen verbinden
Bestimmen Sie vor der Aggregation mehrerer verknüpfter Tabellen, was jede Ergebniszeile darstellt. Die Verknüpfung von Bestellungen mit ihren Positionen wiederholt Bestellattribute für jede Position. Einmal pro Bestellung berechnete Beträge wie Versandkosten müssen deshalb auf Bestellungsebene summiert oder nach einer Zusammenfassung der Positionen verbunden werden.
Kunden-, Bestellungs-, Positions- und Produktdaten unterscheiden
Eine Ausgabe mit Kundenname, Bestelldatum, Produktname und Menge verbindet vier Tabellen. Jede fertige Zeile steht für eine Bestellposition, nicht für die gesamte Bestellung. Wer diesen Unterschied übersieht, kann Bestellungen sowie nur einmal je Bestellung anfallende Gebühren oder Rabatte mehrfach zählen.
| Wert | Ursprüngliche Ebene | Nach Verknüpfung der Positionen |
|---|---|---|
| customer_name | Kunde | Wiederholt sich je passender Position |
| shipping_fee | Bestellung | Wiederholt sich je Bestellposition |
| quantity × unit_price | Position | Kann als Positionsbetrag summiert werden |
| Aktueller Produktpreis | Produkt | Für den Kaufbetrag muss der historische Preis erhalten bleiben |
Die Anleitung zu Bestellungs- und Produktbeziehungen erklärt die Bedeutung der Tabellen. Die Anleitung zu LEFT JOIN hilft bei der Auswahl der zu erhaltenden Ausgangsmenge. Die offizielle JOIN-Referenz dokumentiert die Syntax.
Historische Preise und Versandkosten vorbereiten
Verbinden Sie sich als course_user mit dem Lernserver und wählen Sie shopcourse. Die Anleitung zu Client und UTF-8-SQL-Dateien erklärt das Speichern und Ausführen mit SOURCE C:/mariadb-lab/lesson.sql;. Ersetzen Sie den Pfad durch die tatsächliche Datei und führen Sie SQL im MariaDB-Client aus.
Prüfen Sie vor dem Erstellen von c28_customers, c28_products, c28_orders und c28_items mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung. Untersuchen Sie vorhandene Definitionen und Daten vor einem Neustart. Verwenden Sie nur entbehrliche Übungstabellen und erhalten Sie Anwendungstabellen.
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);
Das SQL erhält die koreanischen Beispieldaten für Namen und Städte; die Erläuterungen verwenden eindeutige Kennungen. Die Produkte 1 bis 4 sind Notizbuch, Kugelschreiber, Becher und Lampe. Die Kunden 1 und 3 haben die Stadt Seoul, Kunde 2 Busan und Kunde 4 Daejeon. Behalten Sie die UTF-8-Zeichenfolgen beim Nachvollziehen der Ergebnisse bei.
Die bezahlten Bestellungen 101, 102 und 103 haben zwei, eine und zwei Positionen: insgesamt fünf bezahlte Positionen. Jede Bestellung hat Versandkosten von 1000. Die kleinen Mengen machen nachvollziehbar, wie oft jeder Betrag erscheint.
Vier Tabellen über die Beziehungsschlüssel verbinden
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;
Bestellung 101 hat einen Warenwert von 7000 + 4500 = 11500, Bestellung 102 von 8000 und Bestellung 103 von 3500 + 16000 = 19500. Zusammen ergeben die bezahlten Bestellungen einen Warenwert von 39000. Die Namen stammen aus products, die Berechnung verwendet aber unit_price der Position. Eine spätere Änderung des Katalogpreises schreibt dadurch den historischen Kaufbetrag nicht um.
Mehrfach gezählte Versandkosten erkennen
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';
Das Ergebnis lautet joined_rows=5, orders_count=3, duplicated_shipping=5000.00 und sales=39000.00. Die richtigen Versandkosten der drei Bestellungen betragen 3000.00. Die Verknüpfung mit Positionen hat zwei zusätzliche Kopien erzeugt. COUNT(DISTINCT order_id) korrigiert die Bestellungsanzahl, aber nicht gleichzeitig die anderen Aggregationen.
SUM(DISTINCT shipping_fee) löst das Problem ebenfalls nicht. Alle drei Bestellungen haben denselben Betrag 1000; der Ausdruck würde ihn nur einmal berücksichtigen und 1000 zurückgeben. Gleiche Werte zu entfernen, ist etwas anderes, als jede Bestellung einmal zu summieren. DISTINCT ist keine allgemeine Korrektur für überhöhte Summen.
Positionen vor der Verknüpfung pro Bestellung zusammenfassen
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';
| Prüfung | Verknüpfung jeder Position | Verknüpfung der Bestellungszusammenfassung |
|---|---|---|
| Ergebniszeilen vor der Endaggregation | 5 Positionen | 3 Bestellungen |
| Summierte Versandkosten | 5000.00, mehrfach gezählt | 3000.00 |
| Warenwert | 39000.00 | 39000.00 |
Die abgeleitete Tabelle erzeugt mit GROUP BY einen goods_total pro order_id. Wird sie mit orders verbunden, gibt es wieder eine Zeile pro Bestellung: orders_count=3, shipping_total=3000.00 und goods_total=39000.00. Zusätzliche Positionen erzeugen keine weiteren Kopien der Versandkosten. Die Anleitung zur Gruppierung erklärt den Wechsel der Ergebnisebene.
Diese Abfrage berücksichtigt bezahlte Bestellungen mit Positionen. Müssen auch Bestellungen ohne Positionen erscheinen, wählen Sie LEFT JOIN und eine passende Behandlung fehlender Beträge. Das ist eine Entscheidung über die Ausgangsmenge des Berichts und nicht nur ein Austausch eines SQL-Schlüsselworts.
Die Anzahl der Kombinationen nach jeder Verknüpfung prüfen
Ergänzen Sie Tabellen einzeln und zeigen Sie Schlüssel und Anzahlen an. Unerwartetes Wachstum kann auf nicht eindeutige Schlüssel oder eine fehlende Bedingung hinweisen. Zwei verschiedene Mengen von Detailzeilen zur gleichen Bestellung können alle Kombinationen ihrer Zeilen erzeugen. Fassen Sie jede Detailmenge auf der benötigten Ebene zusammen, bevor Sie ihre Messgrößen verbinden.
Rechnen Sie zunächst eine kleine Bestellung von Hand nach und vergleichen Sie Anzahl und Beträge. Ein richtiger Warenwert beweist keine richtigen Versandkosten. Die Anleitung zu Aggregationen erklärt die Prüfung von Einheiten und Nennern.
Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, SHOW CREATE TABLE und gespeicherte Zeilen aller vier Tabellen. Sichern Sie benötigte Inhalte und entfernen Sie nur entbehrliche c28_-Objekte, zuerst die abhängigen und danach die referenzierten Tabellen. DROP TABLE entfernt Struktur und Daten und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Trennen Sie die Bereinigung von Transaktionsversuchen.
DROP TABLE shopcourse.c28_items;
DROP TABLE shopcourse.c28_orders;
DROP TABLE shopcourse.c28_products;
DROP TABLE shopcourse.c28_customers;




