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
|
DROP TABLE IF EXISTS items;
CREATE TABLE items (
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
score1 INT,
score2 INT,
score3 INT,
INDEX (score1, score2, score3)
) DEFAULT CHARSET=UTF8;
SHOW CREATE TABLE items;
Table Create Table
items CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`score1` int(11) DEFAULT NULL,
`score2` int(11) DEFAULT NULL,
`score3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `score1` (`score1`,`score2`,`score3`)
) ENGINE=Mroonga DEFAULT CHARSET=utf8
INSERT INTO items (score1, score2, score3) VALUES(1, 10, -100);
INSERT INTO items (score1, score2, score3) VALUES(1, 10, 0);
INSERT INTO items (score1, score2, score3) VALUES(2, 10, 100);
INSERT INTO items (score1, score2, score3) VALUES(2, 30, -100);
INSERT INTO items (score1, score2, score3) VALUES(2, 30, 0);
INSERT INTO items (score1, score2, score3) VALUES(2, 30, 100);
INSERT INTO items (score1, score2, score3) VALUES(2, 20, -100);
INSERT INTO items (score1, score2, score3) VALUES(2, 20, 0);
INSERT INTO items (score1, score2, score3) VALUES(2, 20, 100);
SELECT *
FROM items
WHERE score1 = 2
ORDER BY score2 DESC, score3 DESC;
id score1 score2 score3
6 2 30 100
5 2 30 0
4 2 30 -100
9 2 20 100
8 2 20 0
7 2 20 -100
3 2 10 100
DROP TABLE items;
|
