質問編集履歴
4
あ
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -21,24 +21,24 @@
|
|
|
21
21
|
charset = utf8mb4;
|
|
22
22
|
|
|
23
23
|
|
|
24
|
-
insert table_name (id, sei, mei) VALUE (1, '
|
|
24
|
+
insert table_name (id, sei, mei) VALUE (1, '田中', '二郎');
|
|
25
|
-
insert table_name (id, sei, mei) VALUE (2, '
|
|
25
|
+
insert table_name (id, sei, mei) VALUE (2, '田中', '二郎2');
|
|
26
|
-
insert table_name (id, sei, mei) VALUE (3, '
|
|
26
|
+
insert table_name (id, sei, mei) VALUE (3, '田中', '二郎3');
|
|
27
|
-
insert table_name (id, sei, mei) VALUE (4, '
|
|
27
|
+
insert table_name (id, sei, mei) VALUE (4, '田中', '二郎4');
|
|
28
|
-
insert table_name (id, sei, mei) VALUE (5, '
|
|
28
|
+
insert table_name (id, sei, mei) VALUE (5, '高橋', '太郎');
|
|
29
|
-
insert table_name (id, sei, mei) VALUE (6, '
|
|
29
|
+
insert table_name (id, sei, mei) VALUE (6, '高橋', '太郎2');
|
|
30
|
-
insert table_name (id, sei, mei) VALUE (7, '
|
|
30
|
+
insert table_name (id, sei, mei) VALUE (7, '高橋', '太郎3');
|
|
31
|
-
insert table_name (id, sei, mei) VALUE (8, '
|
|
31
|
+
insert table_name (id, sei, mei) VALUE (8, '高橋', '太郎4');
|
|
32
|
-
insert table_name (id, sei, mei) VALUE (9, '
|
|
32
|
+
insert table_name (id, sei, mei) VALUE (9, '斎藤2', '一郎9');
|
|
33
|
-
insert table_name (id, sei, mei) VALUE (10, '
|
|
33
|
+
insert table_name (id, sei, mei) VALUE (10, '斎藤2', '一郎');
|
|
34
|
-
insert table_name (id, sei, mei) VALUE (11, '
|
|
34
|
+
insert table_name (id, sei, mei) VALUE (11, '斎藤2', '一郎2');
|
|
35
|
-
insert table_name (id, sei, mei) VALUE (12, '
|
|
35
|
+
insert table_name (id, sei, mei) VALUE (12, '斎藤2', '一郎3');
|
|
36
|
+
insert table_name (id, sei, mei) VALUE (13, '佐藤2', '四郎');
|
|
37
|
+
insert table_name (id, sei, mei) VALUE (14, '佐藤2', '四郎2');
|
|
38
|
+
insert table_name (id, sei, mei) VALUE (15, '佐藤2', '四郎3');
|
|
36
|
-
insert table_name (id, sei, mei) VALUE (
|
|
39
|
+
insert table_name (id, sei, mei) VALUE (16, '佐藤2', '四郎4');
|
|
37
|
-
insert table_name (id, sei, mei) VALUE (
|
|
40
|
+
insert table_name (id, sei, mei) VALUE (17, '佐藤2', '四郎5');
|
|
38
|
-
insert table_name (id, sei, mei) VALUE (
|
|
41
|
+
insert table_name (id, sei, mei) VALUE (18, '佐藤2', '四郎6');
|
|
39
|
-
insert table_name (id, sei, mei) VALUE (16, '名前2', '苗字7');
|
|
40
|
-
insert table_name (id, sei, mei) VALUE (17, '名前2', '苗字8');
|
|
41
|
-
insert table_name (id, sei, mei) VALUE (18, '名前2', '苗字9');
|
|
42
42
|
|
|
43
43
|
|
|
44
44
|
|
|
@@ -65,13 +65,15 @@
|
|
|
65
65
|
|
|
66
66
|
|id|fullname|count|
|
|
67
67
|
|:--|:--:|--:|
|
|
68
|
-
|1|
|
|
68
|
+
|1|田中二郎|4|
|
|
69
|
-
|10|
|
|
69
|
+
|10|高橋2太郎|4|
|
|
70
|
+
|1|斎藤2一郎|4|
|
|
71
|
+
|10|佐藤2四郎|5|
|
|
70
72
|
|
|
71
73
|
・条件
|
|
72
74
|
```
|
|
73
75
|
sei : が完全一致
|
|
74
|
-
mei :
|
|
76
|
+
mei : 前方一致
|
|
75
77
|
```
|
|
76
78
|
|
|
77
79
|
上記の名前`名前苗字`が18レコードあることを確認できるようにしたいです。
|
3
a
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -63,7 +63,7 @@
|
|
|
63
63
|
|
|
64
64
|
### 期待値
|
|
65
65
|
|
|
66
|
-
|id|
|
|
66
|
+
|id|fullname|count|
|
|
67
67
|
|:--|:--:|--:|
|
|
68
68
|
|1|名前苗字|9|
|
|
69
69
|
|10|名前2苗字|9|
|
2
a
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -29,16 +29,16 @@
|
|
|
29
29
|
insert table_name (id, sei, mei) VALUE (6, '名前', '苗字6');
|
|
30
30
|
insert table_name (id, sei, mei) VALUE (7, '名前', '苗字7');
|
|
31
31
|
insert table_name (id, sei, mei) VALUE (8, '名前', '苗字8');
|
|
32
|
-
insert table_name (id, sei, mei) VALUE (9, '名前', '苗字9');
|
|
32
|
+
insert table_name (id, sei, mei) VALUE (9, '名前2', '苗字9');
|
|
33
|
-
insert table_name (id, sei, mei) VALUE (10, '名前', '苗字
|
|
33
|
+
insert table_name (id, sei, mei) VALUE (10, '名前2', '苗字');
|
|
34
|
-
insert table_name (id, sei, mei) VALUE (11, '名前', '苗字
|
|
34
|
+
insert table_name (id, sei, mei) VALUE (11, '名前2', '苗字2');
|
|
35
|
-
insert table_name (id, sei, mei) VALUE (12, '名前', '苗字
|
|
35
|
+
insert table_name (id, sei, mei) VALUE (12, '名前2', '苗字3');
|
|
36
|
-
insert table_name (id, sei, mei) VALUE (13, '名前', '苗字
|
|
36
|
+
insert table_name (id, sei, mei) VALUE (13, '名前2', '苗字4');
|
|
37
|
-
insert table_name (id, sei, mei) VALUE (14, '名前', '苗字
|
|
37
|
+
insert table_name (id, sei, mei) VALUE (14, '名前2', '苗字5');
|
|
38
|
-
insert table_name (id, sei, mei) VALUE (15, '名前', '苗字
|
|
38
|
+
insert table_name (id, sei, mei) VALUE (15, '名前2', '苗字6');
|
|
39
|
-
insert table_name (id, sei, mei) VALUE (16, '名前', '苗字
|
|
39
|
+
insert table_name (id, sei, mei) VALUE (16, '名前2', '苗字7');
|
|
40
|
-
insert table_name (id, sei, mei) VALUE (17, '名前', '苗字
|
|
40
|
+
insert table_name (id, sei, mei) VALUE (17, '名前2', '苗字8');
|
|
41
|
-
insert table_name (id, sei, mei) VALUE (18, '名前', '苗字
|
|
41
|
+
insert table_name (id, sei, mei) VALUE (18, '名前2', '苗字9');
|
|
42
42
|
|
|
43
43
|
|
|
44
44
|
|
|
@@ -54,15 +54,24 @@
|
|
|
54
54
|
|
|
55
55
|
```
|
|
56
56
|
SELECT
|
|
57
|
-
CONCAT(sei, mei) as fullname
|
|
57
|
+
CONCAT(sei, mei) as fullname,
|
|
58
|
+
count(fullname) as count
|
|
58
59
|
FROM table_name
|
|
59
60
|
GROUP BY fullname
|
|
60
61
|
HAVING COUNT(*) > 1;
|
|
61
62
|
```
|
|
62
63
|
|
|
63
64
|
### 期待値
|
|
65
|
+
|
|
66
|
+
|id||fullname|count|
|
|
67
|
+
|:--|:--:|--:|
|
|
68
|
+
|1|名前苗字|9|
|
|
69
|
+
|10|名前2苗字|9|
|
|
70
|
+
|
|
71
|
+
・条件
|
|
64
72
|
```
|
|
65
|
-
|
|
73
|
+
sei : が完全一致
|
|
74
|
+
mei : 部分一致
|
|
66
75
|
```
|
|
67
76
|
|
|
68
77
|
上記の名前`名前苗字`が18レコードあることを確認できるようにしたいです。
|
1
mysql
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -3,6 +3,10 @@
|
|
|
3
3
|
SQLでの名前のあいまい検索で重複を探したいです。
|
|
4
4
|
|
|
5
5
|
|
|
6
|
+
### バージョン
|
|
7
|
+
mysql
|
|
8
|
+
5.7.22
|
|
9
|
+
|
|
6
10
|
### 該当のソースコード
|
|
7
11
|
|
|
8
12
|
```ここに言語名を入力
|