1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
| --------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
--------------
CREATE TABLE `tabtwo`
( `id` integer unsigned NOT NULL,
`rang` tinyint unsigned NOT NULL,
`prix` decimal(15,2) NOT NULL,
`volume` decimal(15,0) NULL,
primary key (`id`,`rang`)
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
--------------
INSERT INTO `tabtwo` (`id`,`rang`,`prix`,`volume`) VALUES
(1, 1, 5, 10.0),(1, 2, 4, 20.0),(1, 3, 3, NULL),
(2, 1, 7, 12.0),(2, 2, 6, 24.0),(2, 3, 5, NULL),
(3, 1, 6, 7.0),(3, 2, 5, 14.0),(3, 3, 4, NULL)
--------------
--------------
select * from `tabtwo`
--------------
+----+------+------+--------+
| id | rang | prix | volume |
+----+------+------+--------+
| 1 | 1 | 5.00 | 10 |
| 1 | 2 | 4.00 | 20 |
| 1 | 3 | 3.00 | NULL |
| 2 | 1 | 7.00 | 12 |
| 2 | 2 | 6.00 | 24 |
| 2 | 3 | 5.00 | NULL |
| 3 | 1 | 6.00 | 7 |
| 3 | 2 | 5.00 | 14 |
| 3 | 3 | 4.00 | NULL |
+----+------+------+--------+
--------------
DROP TABLE IF EXISTS `tabone`
--------------
--------------
CREATE TABLE `tabone`
( `id` integer unsigned NOT NULL auto_increment primary key,
`nom` varchar(255) NOT NULL,
`commande` decimal(15,0) NOT NULL,
`clef` integer unsigned not null,
CONSTRAINT `FK_01` FOREIGN KEY (`clef`) REFERENCES `tabtwo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
--------------
INSERT INTO `tabone` (`nom`,`commande`,`clef`) VALUES
('Huile Un', 7, 1),('Huile Deux', 22, 2),('Huile Trois', 20, 3)
--------------
--------------
select * from `tabone`
--------------
+----+-------------+----------+------+
| id | nom | commande | clef |
+----+-------------+----------+------+
| 1 | Huile Un | 7 | 1 |
| 2 | Huile Deux | 22 | 2 |
| 3 | Huile Trois | 20 | 3 |
+----+-------------+----------+------+
--------------
select t1.nom,
t1.commande,
t2.prix as prix_1,
t2.volume as volume_1,
t3.prix as prix_2,
t3.volume as volume_2,
t4.prix as prix_3,
CASE WHEN t1.commande <= t2.volume THEN t2.prix * t1.commande
WHEN t1.commande <= t3.volume THEN t2.prix * t2.volume + t3.prix * (t1.commande - t2.volume)
ELSE t2.prix * t2.volume + t3.prix * (t3.volume - t2.volume) + t4.prix * (t1.commande - t3.volume) END as result
from `tabone` as t1
inner join `tabtwo` as t2
on t2.id = t1.clef and t2.rang = 1
inner join `tabtwo` as t3
on t3.id = t1.clef and t3.rang = 2
inner join `tabtwo` as t4
on t4.id = t1.clef and t4.rang = 3
--------------
+-------------+----------+--------+----------+--------+----------+--------+--------+
| nom | commande | prix_1 | volume_1 | prix_2 | volume_2 | prix_3 | result |
+-------------+----------+--------+----------+--------+----------+--------+--------+
| Huile Un | 7 | 5.00 | 10 | 4.00 | 20 | 3.00 | 35.00 |
| Huile Deux | 22 | 7.00 | 12 | 6.00 | 24 | 5.00 | 144.00 |
| Huile Trois | 20 | 6.00 | 7 | 5.00 | 14 | 4.00 | 101.00 |
+-------------+----------+--------+----------+--------+----------+--------+--------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager