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 120 121 122 123
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `book_title`
--------------
--------------
CREATE TABLE `book_title`
(
`id` integer unsigned NOT NULL AUTO_INCREMENT,
`title` char(20) NOT NULL,
`locale` char(02) NOT NULL,
`book_fk` integer unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO book_title (`title`, `locale`, `book_fk`) VALUES
('Titre 1', 'fr', 1),
('Title 1', 'en', 1),
('Titre 2', 'fr', 2),
('Titolo 3', 'it', 3),
('Title 4', 'en', 4),
('Titolo 4', 'it', 4)
--------------
--------------
select * from book_title
--------------
+----+----------+--------+---------+
| id | title | locale | book_fk |
+----+----------+--------+---------+
| 1 | Titre 1 | fr | 1 |
| 2 | Title 1 | en | 1 |
| 3 | Titre 2 | fr | 2 |
| 4 | Titolo 3 | it | 3 |
| 5 | Title 4 | en | 4 |
| 6 | Titolo 4 | it | 4 |
+----+----------+--------+---------+
--------------
DROP TABLE IF EXISTS `priority`
--------------
--------------
CREATE TABLE `priority`
(
`locale` char(02) NOT NULL,
`sort` tinyint NOT NULL,
PRIMARY KEY (`locale`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `priority` (`locale`, `sort`) VALUES
('fr', 1),
('en', 2),
('it', 3)
--------------
--------------
select * from priority
--------------
+--------+------+
| locale | sort |
+--------+------+
| en | 2 |
| fr | 1 |
| it | 3 |
+--------+------+
--------------
select book_fk, title
from book_title as tb1
where locale = (
select tb2.locale
from book_title as tb2
inner join priority as tb3
on tb3.locale = tb2.locale
where tb2.book_fk = tb1.book_fk
order by tb3.sort limit 1)
order by book_fk
--------------
+---------+----------+
| book_fk | title |
+---------+----------+
| 1 | Titre 1 |
| 2 | Titre 2 |
| 3 | Titolo 3 |
| 4 | Title 4 |
+---------+----------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager