質問編集履歴
2
テーブル名を修正しました。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -26,7 +26,7 @@
|
|
|
26
26
|
|
|
27
27
|
```
|
|
28
28
|
|
|
29
|
-
●list
|
|
29
|
+
●list_table（2万件ほどのデータ）
|
|
30
30
|
|
|
31
31
|
|id|name|number|･･･|
|
|
32
32
|
|
|
@@ -48,7 +48,7 @@
|
|
|
48
48
|
|
|
49
49
|
```
|
|
50
50
|
|
|
51
|
-
CREATE TABLE `list
|
|
51
|
+
CREATE TABLE `list_table` (
|
|
52
52
|
|
|
53
53
|
`id` int(5) unsigned NOT NULL,
|
|
54
54
|
|
|
@@ -82,7 +82,7 @@
|
|
|
82
82
|
|
|
83
83
|
|
|
84
84
|
|
|
85
|
-
data_tableとlist
|
|
85
|
+
data_tableとlist_tableをlist_table JOIN data_table USING (name)で結合したものから、
|
|
86
86
|
|
|
87
87
|
nameにAを含み、numberが101で、idが大きいデータを取得したいです。
|
|
88
88
|
|
|
@@ -96,13 +96,13 @@
|
|
|
96
96
|
|
|
97
97
|
SELECT t1.*
|
|
98
98
|
|
|
99
|
-
FROM list
|
|
99
|
+
FROM list_table AS t1
|
|
100
100
|
|
|
101
101
|
JOIN (
|
|
102
102
|
|
|
103
103
|
SELECT name, MAX(id) AS id
|
|
104
104
|
|
|
105
|
-
FROM list
|
|
105
|
+
FROM list_table
|
|
106
106
|
|
|
107
107
|
WHERE name LIKE '%A%'
|
|
108
108
|
|
1
CREATE文を追加しました。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -10,7 +10,21 @@
|
|
|
10
10
|
|
|
11
11
|
|3|AB|･･･|
|
|
12
12
|
|
|
13
|
+
|
|
13
14
|
|
|
15
|
+
```CREATE文
|
|
16
|
+
|
|
17
|
+
CREATE TABLE `data_table` (
|
|
18
|
+
|
|
19
|
+
`id2` int(5) unsigned NOT NULL,
|
|
20
|
+
|
|
21
|
+
`name` varchar(100) NOT NULL DEFAULT '',
|
|
22
|
+
|
|
23
|
+
PRIMARY KEY (`id2`)
|
|
24
|
+
|
|
25
|
+
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
|
26
|
+
|
|
27
|
+
```
|
|
14
28
|
|
|
15
29
|
●list＿table（2万件ほどのデータ）
|
|
16
30
|
|
|
@@ -31,6 +45,24 @@
|
|
|
31
45
|
|6|AB|102|･･･|
|
|
32
46
|
|
|
33
47
|
|
|
48
|
+
|
|
49
|
+
```
|
|
50
|
+
|
|
51
|
+
CREATE TABLE `list＿table` (
|
|
52
|
+
|
|
53
|
+
`id` int(5) unsigned NOT NULL,
|
|
54
|
+
|
|
55
|
+
`name` varchar(100) NOT NULL DEFAULT '',
|
|
56
|
+
|
|
57
|
+
`number` int(3) NOT NULL DEFAULT '',
|
|
58
|
+
|
|
59
|
+
PRIMARY KEY (`id`),
|
|
60
|
+
|
|
61
|
+
KEY `name` (`name`,`id`)
|
|
62
|
+
|
|
63
|
+
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
|
64
|
+
|
|
65
|
+
```
|
|
34
66
|
|
|
35
67
|
id、id2はPRIMARYでAUTO_INCREMENTです。
|
|
36
68
|
|