回答編集履歴

1

sample

2017/10/12 08:19

投稿

yambejp
yambejp

スコア114583

test CHANGED
@@ -13,3 +13,105 @@
13
13
  ```
14
14
 
15
15
  とするだけでよいかと思います
16
+
17
+
18
+
19
+ # sample
20
+
21
+
22
+
23
+ ```SQL
24
+
25
+ create table nayose_otameshi(id int primary key auto_increment,
26
+
27
+ first_name_kana varchar(10),
28
+
29
+ last_name_kana varchar(10),
30
+
31
+ phone_number varchar(10),
32
+
33
+ city varchar(10));
34
+
35
+
36
+
37
+ insert into nayose_otameshi(first_name_kana,last_name_kana,phone_number,city) values
38
+
39
+ ('f1','l1','p1','c1'),
40
+
41
+ ('f2','l2','p2','c2'),
42
+
43
+ ('f2','l2','p2','c2'),
44
+
45
+ ('f3','l3','p3','c3'),
46
+
47
+ ('f1','l4','p4','c4'),
48
+
49
+ ('f1','l1','p1','c1'),
50
+
51
+ ('f3','l3','p3','c3'),
52
+
53
+ ('f2','l2','p2','c2');
54
+
55
+ ```
56
+
57
+ 上記のように別途主キーが設定されているのであれば2番め以降に出て来るレコードはこう
58
+
59
+ ```SQL
60
+
61
+ select id from nayose_otameshi as t1
62
+
63
+ where exists(select 1 from nayose_otameshi as t2
64
+
65
+ where 1
66
+
67
+ and t1.first_name_kana=t2.first_name_kana
68
+
69
+ and t1.last_name_kana=t2.last_name_kana
70
+
71
+ and t1.phone_number=t2.phone_number
72
+
73
+ and t1.city=t2.city
74
+
75
+ and t1.id>t2.id
76
+
77
+ )
78
+
79
+ ```
80
+
81
+ id=3,6,7,8が抽出されるのでこれを利用して削除
82
+
83
+ ```SQL
84
+
85
+ delete from nayose_otameshi
86
+
87
+ where id in(
88
+
89
+ select id from(
90
+
91
+ select id from nayose_otameshi as t1
92
+
93
+ where exists(select 1 from nayose_otameshi as t2
94
+
95
+ where 1
96
+
97
+ and t1.first_name_kana=t2.first_name_kana
98
+
99
+ and t1.last_name_kana=t2.last_name_kana
100
+
101
+ and t1.phone_number=t2.phone_number
102
+
103
+ and t1.city=t2.city
104
+
105
+ and t1.id>t2.id
106
+
107
+ )
108
+
109
+ )as temp
110
+
111
+ )
112
+
113
+ ```
114
+
115
+ deleteに自己結合したデータはダイレクトにつかえないので
116
+
117
+ 一度ダミーのサブクエリでselectを抽出しないといけません