28/02/2020 - MYSQL
Siparişler, ürünler gibi satış bilgilerini sakladığınız bir veritabanınız olduğunu varsayın. Bu iki tablo arasında bire çok (one-to-many) ilişki vardır. Sorgu içinde ROLLUP ile bir siparişin tüm kalemlerini toplamı hesaplayarak bir fatura oluşturmak istiyorsunuz. Bu örnek bize bunun nasıl yapılabileceğini göstermektedir.
mysql> DESCRIBE orders;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| created_at | datetime | NO | | NULL | |
+------------+------------------+------+-----+---------+----------------+
mysql> DESCRIBE items;
+-----------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | | |
| qty | int(10) unsigned | NO | | NULL | |
| price | decimal(11,2) unsigned | NO | | NULL | |
| orders_id | int(10) unsigned | NO | MUL | NULL | |
+-----------+------------------------+------+-----+---------+----------------+
mysql> SELECT * FROM orders;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2010-01-01 00:00:00 |
| 2 | 2010-02-01 00:00:00 |
+----+---------------------+
mysql> SELECT * FROM items;
+----+----------+-----+----------+-----------+
| id | name | qty | price | orders_id |
+----+----------+-----+----------+-----------+
| 1 | bmw | 1 | 20000.00 | 1 |
| 2 | mercedes | 2 | 25000.00 | 1 |
| 3 | audi | 1 | 19000.00 | 1 |
| 4 | toyota | 3 | 15000.00 | 2 |
| 5 | mazda | 2 | 13000.00 | 2 |
+----+----------+-----+----------+-----------+
mysql> SELECT
-> orders.id AS OrderID,
-> items.name AS ItemName,
-> SUM(items.price) AS ItemPrice,
-> SUM(items.qty) AS ItemQty,
-> SUM(items.qty * items.price) AS OrderTotal
-> FROM orders
-> INNER JOIN items ON orders.id = items.orders_id
-> GROUP BY OrderID, ItemName WITH ROLLUP;
+---------+----------+-----------+---------+------------+
| OrderID | ItemName | ItemPrice | ItemQty | OrderTotal |
+---------+----------+-----------+---------+------------+
| 1 | audi | 19000.00 | 1 | 19000.00 |
| 1 | bmw | 20000.00 | 1 | 20000.00 |
| 1 | mercedes | 25000.00 | 2 | 50000.00 |
| 1 | NULL | 64000.00 | 4 | 89000.00 |
| 2 | mazda | 13000.00 | 2 | 26000.00 |
| 2 | toyota | 15000.00 | 3 | 45000.00 |
| 2 | NULL | 28000.00 | 5 | 71000.00 |
| NULL | NULL | 92000.00 | 9 | 160000.00 |
+---------+----------+-----------+---------+------------+