質問編集履歴
1
データの取得条件を追記
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -2,12 +2,22 @@
|
|
|
2
2
|
|
|
3
3
|
SQLのデータ抽出についての質問です。
|
|
4
4
|
|
|
5
|
-
同テーブル内のレコードの内、異なるカラム
|
|
5
|
+
同テーブル内のレコードの内、同じカラムのフィールド値が一致するかつ異なるカラムのフィールドの値が一致するレコードを全て抽出したいです。
|
|
6
6
|
|
|
7
7
|
上記内容をテーブルから取得できるクエリをご教授いただけないでしょうか。
|
|
8
8
|
|
|
9
9
|
|
|
10
10
|
|
|
11
|
+
### データの取得条件
|
|
12
|
+
|
|
13
|
+
「CONCAT(test.name1, test.name2)が一致するレコード」
|
|
14
|
+
|
|
15
|
+
かつ
|
|
16
|
+
|
|
17
|
+
「CONCAT(test.h_phone1, test.h_phone2, test.h_phone3) とCONCAT(test.m_phone1, test.m_phone2, test.m_phone3)の組み合わせで一致するレコード」
|
|
18
|
+
|
|
19
|
+
|
|
20
|
+
|
|
11
21
|
### 環境
|
|
12
22
|
|
|
13
23
|
OS：CentOS7
|
|
@@ -62,49 +72,49 @@
|
|
|
62
72
|
|
|
63
73
|
SELECT
|
|
64
74
|
|
|
65
|
-
|
|
75
|
+
main_test.id,
|
|
66
|
-
|
|
76
|
+
|
|
67
|
-
CONCAT(
|
|
77
|
+
CONCAT(main_test.name1, main_test.name2) AS main_name,
|
|
68
|
-
|
|
78
|
+
|
|
69
|
-
CONCAT(
|
|
79
|
+
CONCAT(sub_test.name1, sub_test.name2) AS sub_name,
|
|
70
|
-
|
|
80
|
+
|
|
71
|
-
CONCAT(
|
|
81
|
+
CONCAT(
|
|
72
|
-
|
|
82
|
+
|
|
73
|
-
|
|
83
|
+
main_test.h_phone1,
|
|
74
|
-
|
|
84
|
+
|
|
75
|
-
|
|
85
|
+
main_test.h_phone2,
|
|
76
|
-
|
|
86
|
+
|
|
77
|
-
|
|
87
|
+
main_test.h_phone3
|
|
78
88
|
|
|
79
89
|
) AS main_h_phone,
|
|
80
90
|
|
|
81
91
|
CONCAT(
|
|
82
92
|
|
|
83
|
-
|
|
93
|
+
sub_test.m_phone1,
|
|
84
|
-
|
|
94
|
+
|
|
85
|
-
|
|
95
|
+
sub_test.m_phone2,
|
|
86
|
-
|
|
96
|
+
|
|
87
|
-
|
|
97
|
+
sub_test.m_phone3
|
|
88
98
|
|
|
89
99
|
) AS sub_m_phone,
|
|
90
100
|
|
|
91
101
|
CONCAT(
|
|
92
102
|
|
|
93
|
-
|
|
103
|
+
sub_test.h_phone1,
|
|
94
|
-
|
|
104
|
+
|
|
95
|
-
|
|
105
|
+
sub_test.h_phone2,
|
|
96
|
-
|
|
106
|
+
|
|
97
|
-
|
|
107
|
+
sub_test.h_phone3
|
|
98
108
|
|
|
99
109
|
) AS sub_h_phone,
|
|
100
110
|
|
|
101
111
|
CONCAT(
|
|
102
112
|
|
|
103
|
-
|
|
113
|
+
main_test.m_phone1,
|
|
104
|
-
|
|
114
|
+
|
|
105
|
-
|
|
115
|
+
main_test.m_phone2,
|
|
106
|
-
|
|
116
|
+
|
|
107
|
-
|
|
117
|
+
main_test.m_phone3
|
|
108
118
|
|
|
109
119
|
) AS main_m_phone
|
|
110
120
|
|
|
@@ -198,7 +208,7 @@
|
|
|
198
208
|
|
|
199
209
|
-- 「main_h_phone、sub_m_phone」もしくは「sub_h_phoneとmain_m_phone」の組み合わせ
|
|
200
210
|
|
|
201
|
-
CONCAT(
|
|
211
|
+
CONCAT(main_test.name1, main_test.name2) = CONCAT(sub_test.name1, sub_test.name2)
|
|
202
212
|
|
|
203
213
|
AND (
|
|
204
214
|
|
|
@@ -206,19 +216,19 @@
|
|
|
206
216
|
|
|
207
217
|
CONCAT(
|
|
208
218
|
|
|
209
|
-
|
|
219
|
+
main_test.h_phone1,
|
|
210
|
-
|
|
220
|
+
|
|
211
|
-
|
|
221
|
+
main_test.h_phone2,
|
|
212
|
-
|
|
222
|
+
|
|
213
|
-
|
|
223
|
+
main_test.h_phone3
|
|
214
224
|
|
|
215
225
|
) = CONCAT(
|
|
216
226
|
|
|
217
|
-
|
|
227
|
+
sub_test.m_phone1,
|
|
218
|
-
|
|
228
|
+
|
|
219
|
-
|
|
229
|
+
sub_test.m_phone2,
|
|
220
|
-
|
|
230
|
+
|
|
221
|
-
|
|
231
|
+
sub_test.m_phone3
|
|
222
232
|
|
|
223
233
|
)
|
|
224
234
|
|
|
@@ -228,19 +238,19 @@
|
|
|
228
238
|
|
|
229
239
|
CONCAT(
|
|
230
240
|
|
|
231
|
-
|
|
241
|
+
sub_test.h_phone1,
|
|
232
|
-
|
|
242
|
+
|
|
233
|
-
|
|
243
|
+
sub_test.h_phone2,
|
|
234
|
-
|
|
244
|
+
|
|
235
|
-
|
|
245
|
+
sub_test.h_phone3
|
|
236
246
|
|
|
237
247
|
) = CONCAT(
|
|
238
248
|
|
|
239
|
-
|
|
249
|
+
main_test.m_phone1,
|
|
240
|
-
|
|
250
|
+
|
|
241
|
-
|
|
251
|
+
main_test.m_phone2,
|
|
242
|
-
|
|
252
|
+
|
|
243
|
-
|
|
253
|
+
main_test.m_phone3
|
|
244
254
|
|
|
245
255
|
)
|
|
246
256
|
|