Context Navigation

Back to Ticket #318

Ticket #318: 清除冗余数据.sql

File 清除冗余数据.sql, 21.0 KB (added by libingyue, 11 years ago)

Line
1	-- --------------ä»åºæäœïŒç»è®¡bbs7_user_topicãbbs7_user_postãbbs7_pickè¡šçèæ°æ®,(å¯ä»¥äžçº¿åå)----------------------------
2	-- 1.ä¿åbbs7_user_topicè¡šåšç©çäžçèæ°æ®
3	CREATE TABLE user_topic_physic (
4	tid INT(10) NOT NULL,
5	uid INT(10) NOT NULL,
6	KEY idx_user_topic_physic_tid(tid)
7	);
8
9	CREATE TABLE user_topic_all (
10	tid INT(10) NOT NULL,
11	uid INT(10) NOT NULL,
12	KEY idx_user_topic_all_tid(tid)
13	);
14	INSERT INTO user_topic_all SELECT tid,uid FROM (
15	SELECT tid,uid FROM bbs7_user_topic
16	UNION ALL
17	SELECT tid,uid FROM bbs7_user_topic_1
18	UNION ALL
19	SELECT tid,uid FROM bbs7_user_topic_2
20	UNION ALL
21	SELECT tid,uid FROM bbs7_user_topic_3
22	UNION ALL
23	SELECT tid,uid FROM bbs7_user_topic_4
24	UNION ALL
25	SELECT tid,uid FROM bbs7_user_topic_5
26	UNION ALL
27	SELECT tid,uid FROM bbs7_user_topic_6
28	UNION ALL
29	SELECT tid,uid FROM bbs7_user_topic_7
30	UNION ALL
31	SELECT tid,uid FROM bbs7_user_topic_8
32	UNION ALL
33	SELECT tid,uid FROM bbs7_user_topic_9
34	) r;
35	INSERT INTO user_topic_physic SELECT tid,uid FROM user_topic_all a WHERE NOT EXISTS (SELECT 1 FROM tid_uid_pick b WHERE a.tid = b.tid);
36	COMMIT;
37	-- 2.ä¿åbbs7_user_postè¡šåšç©çäžçèæ°æ®,
38	CREATE TABLE user_post_physic (
39	pid INT(10) NOT NULL,
40	uid INT(10) NOT NULL,
41	tid INT(10) NOT NULL,
42	key idx_user_post_physicz_pid(pid),
43	KEY idx_user_post_physicz_put(pid,uid,tid)
44	);
45
46	CREATE TABLE user_post_all (
47	pid INT(10) NOT NULL,
48	uid INT(10) NOT NULL,
49	tid INT(10) NOT NULL,
50	KEY idx_user_post_all_pid(pid)
51	);
52	INSERT INTO user_post_all SELECT pid, uid, tid FROM (
53	SELECT pid, uid, tid FROM bbs7_user_post
54	UNION ALL
55	SELECT pid, uid, tid FROM bbs7_user_post_1
56	UNION ALL
57	SELECT pid, uid, tid FROM bbs7_user_post_2
58	UNION ALL
59	SELECT pid, uid, tid FROM bbs7_user_post_3
60	UNION ALL
61	SELECT pid, uid, tid FROM bbs7_user_post_4
62	UNION ALL
63	SELECT pid, uid, tid FROM bbs7_user_post_5
64	UNION ALL
65	SELECT pid, uid, tid FROM bbs7_user_post_6
66	UNION ALL
67	SELECT pid, uid, tid FROM bbs7_user_post_7
68	UNION ALL
69	SELECT pid, uid, tid FROM bbs7_user_post_8
70	UNION ALL
71	SELECT pid, uid, tid FROM bbs7_user_post_9
72	UNION ALL
73	SELECT pid, uid, tid FROM bbs7_user_post_10
74	UNION ALL
75	SELECT pid, uid, tid FROM bbs7_user_post_11
76	UNION ALL
77	SELECT pid, uid, tid FROM bbs7_user_post_12
78	UNION ALL
79	SELECT pid, uid, tid FROM bbs7_user_post_13
80	UNION ALL
81	SELECT pid, uid, tid FROM bbs7_user_post_14
82	UNION ALL
83	SELECT pid, uid, tid FROM bbs7_user_post_15
84	UNION ALL
85	SELECT pid, uid, tid FROM bbs7_user_post_16
86	UNION ALL
87	SELECT pid, uid, tid FROM bbs7_user_post_17
88	UNION ALL
89	SELECT pid, uid, tid FROM bbs7_user_post_18
90	UNION ALL
91	SELECT pid, uid, tid FROM bbs7_user_post_19
92	UNION ALL
93	SELECT pid, uid, tid FROM bbs7_user_post_20
94	UNION ALL
95	SELECT pid, uid, tid FROM bbs7_user_post_21
96	UNION ALL
97	SELECT pid, uid, tid FROM bbs7_user_post_22
98	UNION ALL
99	SELECT pid, uid, tid FROM bbs7_user_post_23
100	UNION ALL
101	SELECT pid, uid, tid FROM bbs7_user_post_24
102	UNION ALL
103	SELECT pid, uid, tid FROM bbs7_user_post_25
104	UNION ALL
105	SELECT pid, uid, tid FROM bbs7_user_post_26
106	UNION ALL
107	SELECT pid, uid, tid FROM bbs7_user_post_27
108	UNION ALL
109	SELECT pid, uid, tid FROM bbs7_user_post_28
110	UNION ALL
111	SELECT pid, uid, tid FROM bbs7_user_post_29
112	UNION ALL
113	SELECT pid, uid, tid FROM bbs7_user_post_30
114	UNION ALL
115	SELECT pid, uid, tid FROM bbs7_user_post_31
116	UNION ALL
117	SELECT pid, uid, tid FROM bbs7_user_post_32
118	UNION ALL
119	SELECT pid, uid, tid FROM bbs7_user_post_33
120	UNION ALL
121	SELECT pid, uid, tid FROM bbs7_user_post_34
122	UNION ALL
123	SELECT pid, uid, tid FROM bbs7_user_post_35
124	UNION ALL
125	SELECT pid, uid, tid FROM bbs7_user_post_36
126	UNION ALL
127	SELECT pid, uid, tid FROM bbs7_user_post_37
128	UNION ALL
129	SELECT pid, uid, tid FROM bbs7_user_post_38
130	UNION ALL
131	SELECT pid, uid, tid FROM bbs7_user_post_39
132	UNION ALL
133	SELECT pid, uid, tid FROM bbs7_user_post_40
134	UNION ALL
135	SELECT pid, uid, tid FROM bbs7_user_post_41
136	UNION ALL
137	SELECT pid, uid, tid FROM bbs7_user_post_42
138	UNION ALL
139	SELECT pid, uid, tid FROM bbs7_user_post_43
140	UNION ALL
141	SELECT pid, uid, tid FROM bbs7_user_post_44
142	UNION ALL
143	SELECT pid, uid, tid FROM bbs7_user_post_45
144	UNION ALL
145	SELECT pid, uid, tid FROM bbs7_user_post_46
146	UNION ALL
147	SELECT pid, uid, tid FROM bbs7_user_post_47
148	UNION ALL
149	SELECT pid, uid, tid FROM bbs7_user_post_48
150	UNION ALL
151	SELECT pid, uid, tid FROM bbs7_user_post_49
152	UNION ALL
153	SELECT pid, uid, tid FROM bbs7_user_post_50
154	UNION ALL
155	SELECT pid, uid, tid FROM bbs7_user_post_51
156	UNION ALL
157	SELECT pid, uid, tid FROM bbs7_user_post_52
158	UNION ALL
159	SELECT pid, uid, tid FROM bbs7_user_post_53
160	UNION ALL
161	SELECT pid, uid, tid FROM bbs7_user_post_54
162	UNION ALL
163	SELECT pid, uid, tid FROM bbs7_user_post_55
164	UNION ALL
165	SELECT pid, uid, tid FROM bbs7_user_post_56
166	UNION ALL
167	SELECT pid, uid, tid FROM bbs7_user_post_57
168	UNION ALL
169	SELECT pid, uid, tid FROM bbs7_user_post_58
170	UNION ALL
171	SELECT pid, uid, tid FROM bbs7_user_post_59
172	UNION ALL
173	SELECT pid, uid, tid FROM bbs7_user_post_60
174	UNION ALL
175	SELECT pid, uid, tid FROM bbs7_user_post_61
176	UNION ALL
177	SELECT pid, uid, tid FROM bbs7_user_post_62
178	UNION ALL
179	SELECT pid, uid, tid FROM bbs7_user_post_63
180	UNION ALL
181	SELECT pid, uid, tid FROM bbs7_user_post_64
182	UNION ALL
183	SELECT pid, uid, tid FROM bbs7_user_post_65
184	UNION ALL
185	SELECT pid, uid, tid FROM bbs7_user_post_66
186	UNION ALL
187	SELECT pid, uid, tid FROM bbs7_user_post_67
188	UNION ALL
189	SELECT pid, uid, tid FROM bbs7_user_post_68
190	UNION ALL
191	SELECT pid, uid, tid FROM bbs7_user_post_69
192	UNION ALL
193	SELECT pid, uid, tid FROM bbs7_user_post_70
194	UNION ALL
195	SELECT pid, uid, tid FROM bbs7_user_post_71
196	UNION ALL
197	SELECT pid, uid, tid FROM bbs7_user_post_72
198	UNION ALL
199	SELECT pid, uid, tid FROM bbs7_user_post_73
200	UNION ALL
201	SELECT pid, uid, tid FROM bbs7_user_post_74
202	UNION ALL
203	SELECT pid, uid, tid FROM bbs7_user_post_75
204	UNION ALL
205	SELECT pid, uid, tid FROM bbs7_user_post_76
206	UNION ALL
207	SELECT pid, uid, tid FROM bbs7_user_post_77
208	UNION ALL
209	SELECT pid, uid, tid FROM bbs7_user_post_78
210	UNION ALL
211	SELECT pid, uid, tid FROM bbs7_user_post_79
212	UNION ALL
213	SELECT pid, uid, tid FROM bbs7_user_post_80
214	UNION ALL
215	SELECT pid, uid, tid FROM bbs7_user_post_81
216	UNION ALL
217	SELECT pid, uid, tid FROM bbs7_user_post_82
218	UNION ALL
219	SELECT pid, uid, tid FROM bbs7_user_post_83
220	UNION ALL
221	SELECT pid, uid, tid FROM bbs7_user_post_84
222	UNION ALL
223	SELECT pid, uid, tid FROM bbs7_user_post_85
224	UNION ALL
225	SELECT pid, uid, tid FROM bbs7_user_post_86
226	UNION ALL
227	SELECT pid, uid, tid FROM bbs7_user_post_87
228	UNION ALL
229	SELECT pid, uid, tid FROM bbs7_user_post_88
230	UNION ALL
231	SELECT pid, uid, tid FROM bbs7_user_post_89
232	UNION ALL
233	SELECT pid, uid, tid FROM bbs7_user_post_90
234	UNION ALL
235	SELECT pid, uid, tid FROM bbs7_user_post_91
236	UNION ALL
237	SELECT pid, uid, tid FROM bbs7_user_post_92
238	UNION ALL
239	SELECT pid, uid, tid FROM bbs7_user_post_93
240	UNION ALL
241	SELECT pid, uid, tid FROM bbs7_user_post_94
242	UNION ALL
243	SELECT pid, uid, tid FROM bbs7_user_post_95
244	UNION ALL
245	SELECT pid, uid, tid FROM bbs7_user_post_96
246	UNION ALL
247	SELECT pid, uid, tid FROM bbs7_user_post_97
248	UNION ALL
249	SELECT pid, uid, tid FROM bbs7_user_post_98
250	UNION ALL
251	SELECT pid, uid, tid FROM bbs7_user_post_99
252
253	) u;
254	COMMIT;
255	-- ç©çäžåé»èŸäžéœéå é€
256	INSERT INTO user_post_physic SELECT pid, uid, tid FROM user_post_all a WHERE NOT EXISTS (SELECT 1 FROM uid_pid_status_seq0 b WHERE a.pid = b.pid);
257	COMMIT;
258	-- 3.ä¿åbbs7_pickè¡šåšç©ççèæ°æ®
259	CREATE TABLE pick_physic (
260	tid INT(10) NOT NULL,
261	KEY idx_pick_physic_tid(tid)
262	);
263
264	-- ç©çäž
265	INSERT INTO pick_physic SELECT tid FROM bbs7_pick a WHERE NOT EXISTS (SELECT 1 FROM tid_uid_pick b WHERE a.tid = b.tid);
266	COMMIT;
267
268
269	-- æ³šæïŒèŠåæ¥è¿äžäžªè¡šå°äž»åºäžïŒuser_topic_physicãuser_post_physicãpick_physic
270
271	----------------------------------- 4.æ£æ¥åè¡šåäœæ°æ®å¹¶å é€,åšäž»åºäžæäœ-----------------------------------------------------------
272	-- å é€bbs7_user_topicè¡šçåäœæ°æ®
273	BEGIN;
274	DELETE a FROM bbs7_user_topic a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
275	DELETE a FROM bbs7_user_topic_1 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
276	DELETE a FROM bbs7_user_topic_2 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
277	DELETE a FROM bbs7_user_topic_3 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
278	DELETE a FROM bbs7_user_topic_4 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
279	DELETE a FROM bbs7_user_topic_5 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
280	DELETE a FROM bbs7_user_topic_6 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
281	DELETE a FROM bbs7_user_topic_7 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
282	DELETE a FROM bbs7_user_topic_8 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
283	DELETE a FROM bbs7_user_topic_9 a WHERE EXISTS (SELECT 1 FROM user_topic_physic b WHERE a.tid = b.tid);
284	COMMIT;
285	-- å é€bbs7_user_postè¡šçåäœæ°æ®
286	BEGIN;
287	DELETE a FROM bbs7_user_post a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
288	DELETE a FROM bbs7_user_post_1 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
289	DELETE a FROM bbs7_user_post_2 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
290	DELETE a FROM bbs7_user_post_3 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
291	DELETE a FROM bbs7_user_post_4 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
292	DELETE a FROM bbs7_user_post_5 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
293	DELETE a FROM bbs7_user_post_6 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
294	DELETE a FROM bbs7_user_post_7 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
295	DELETE a FROM bbs7_user_post_8 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
296	DELETE a FROM bbs7_user_post_9 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
297	DELETE a FROM bbs7_user_post_10 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
298	DELETE a FROM bbs7_user_post_11 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
299	DELETE a FROM bbs7_user_post_12 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
300	DELETE a FROM bbs7_user_post_13 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
301	DELETE a FROM bbs7_user_post_14 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
302	DELETE a FROM bbs7_user_post_15 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
303	DELETE a FROM bbs7_user_post_16 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
304	DELETE a FROM bbs7_user_post_17 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
305	DELETE a FROM bbs7_user_post_18 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
306	DELETE a FROM bbs7_user_post_19 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
307	DELETE a FROM bbs7_user_post_20 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
308	DELETE a FROM bbs7_user_post_21 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
309	DELETE a FROM bbs7_user_post_22 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
310	DELETE a FROM bbs7_user_post_23 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
311	DELETE a FROM bbs7_user_post_24 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
312	DELETE a FROM bbs7_user_post_25 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
313	DELETE a FROM bbs7_user_post_26 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
314	DELETE a FROM bbs7_user_post_27 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
315	DELETE a FROM bbs7_user_post_28 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
316	DELETE a FROM bbs7_user_post_29 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
317	DELETE a FROM bbs7_user_post_30 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
318	DELETE a FROM bbs7_user_post_31 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
319	DELETE a FROM bbs7_user_post_32 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
320	DELETE a FROM bbs7_user_post_33 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
321	DELETE a FROM bbs7_user_post_34 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
322	DELETE a FROM bbs7_user_post_35 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
323	DELETE a FROM bbs7_user_post_36 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
324	DELETE a FROM bbs7_user_post_37 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
325	DELETE a FROM bbs7_user_post_38 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
326	DELETE a FROM bbs7_user_post_39 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
327	DELETE a FROM bbs7_user_post_40 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
328	DELETE a FROM bbs7_user_post_41 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
329	DELETE a FROM bbs7_user_post_42 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
330	DELETE a FROM bbs7_user_post_43 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
331	DELETE a FROM bbs7_user_post_44 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
332	DELETE a FROM bbs7_user_post_45 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
333	DELETE a FROM bbs7_user_post_46 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
334	DELETE a FROM bbs7_user_post_47 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
335	DELETE a FROM bbs7_user_post_48 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
336	DELETE a FROM bbs7_user_post_49 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
337	DELETE a FROM bbs7_user_post_50 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
338	DELETE a FROM bbs7_user_post_51 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
339	DELETE a FROM bbs7_user_post_52 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
340	DELETE a FROM bbs7_user_post_53 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
341	DELETE a FROM bbs7_user_post_54 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
342	DELETE a FROM bbs7_user_post_55 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
343	DELETE a FROM bbs7_user_post_56 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
344	DELETE a FROM bbs7_user_post_57 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
345	DELETE a FROM bbs7_user_post_58 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
346	DELETE a FROM bbs7_user_post_59 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
347	DELETE a FROM bbs7_user_post_60 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
348	DELETE a FROM bbs7_user_post_61 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
349	DELETE a FROM bbs7_user_post_62 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
350	DELETE a FROM bbs7_user_post_63 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
351	DELETE a FROM bbs7_user_post_64 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
352	DELETE a FROM bbs7_user_post_65 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
353	DELETE a FROM bbs7_user_post_66 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
354	DELETE a FROM bbs7_user_post_67 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
355	DELETE a FROM bbs7_user_post_68 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
356	DELETE a FROM bbs7_user_post_69 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
357	DELETE a FROM bbs7_user_post_70 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
358	DELETE a FROM bbs7_user_post_71 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
359	DELETE a FROM bbs7_user_post_72 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
360	DELETE a FROM bbs7_user_post_73 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
361	DELETE a FROM bbs7_user_post_74 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
362	DELETE a FROM bbs7_user_post_75 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
363	DELETE a FROM bbs7_user_post_76 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
364	DELETE a FROM bbs7_user_post_77 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
365	DELETE a FROM bbs7_user_post_78 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
366	DELETE a FROM bbs7_user_post_79 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
367	DELETE a FROM bbs7_user_post_80 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
368	DELETE a FROM bbs7_user_post_81 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
369	DELETE a FROM bbs7_user_post_82 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
370	DELETE a FROM bbs7_user_post_83 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
371	DELETE a FROM bbs7_user_post_84 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
372	DELETE a FROM bbs7_user_post_85 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
373	DELETE a FROM bbs7_user_post_86 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
374	DELETE a FROM bbs7_user_post_87 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
375	DELETE a FROM bbs7_user_post_88 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
376	DELETE a FROM bbs7_user_post_89 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
377	DELETE a FROM bbs7_user_post_90 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
378	DELETE a FROM bbs7_user_post_91 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
379	DELETE a FROM bbs7_user_post_92 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
380	DELETE a FROM bbs7_user_post_93 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
381	DELETE a FROM bbs7_user_post_94 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
382	DELETE a FROM bbs7_user_post_95 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
383	DELETE a FROM bbs7_user_post_96 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
384	DELETE a FROM bbs7_user_post_97 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
385	DELETE a FROM bbs7_user_post_98 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
386	DELETE a FROM bbs7_user_post_99 a WHERE EXISTS (SELECT 1 FROM user_post_physic b WHERE a.pid = b.pid);
387	COMMIT;
388	-- å é€bbs7_pickè¡šçåäœæ°æ®
389	DELETE a FROM bbs7_pick a WHERE EXISTS (SELECT 1 FROM pick_physic b WHERE a.tid = b.tid);
390	COMMIT;
391	-- --------------------å é€äžŽæ¶è¡š(åå«å )----------------------------------------------------------- --
392	DROP TABLE tid_uid_pick;
393	DROP TABLE tid_uid_pick_pickgt0;
394	DROP TABLE uid_pid_status_floor;
395	DROP TABLE uid_pid_status_seq0;
396	DROP TABLE uid_pid_status_fgt1seq0;
397	DROP TABLE uid_topiccount;
398	DROP TABLE uid_postcount;
399	DROP TABLE uid_pickcoount;
400	DROP TABLE user_count;
401	DROP TABLE user_topic_physic;
402	DROP TABLE user_post_physic;
403	DROP TABLE pick_physic;
404	DROP TABLE user_topic_all;
405	DROP TABLE user_post_all;
406
407	COMMIT;
408
409
410	-- ---------------------------------å é€å¶ä»åäœæ°æ®------------------------------ --
411
412	-- å é€çšæ·æ¶èåžåçåäœæ°æ® åæ¥åºæ¥åå é€
413	SELECT * FROM bbs7_favorite a WHERE EXISTS (SELECT 1 FROM bbs7_topic_1 b WHERE a.tid = b.tid);
414	-- å é€çšæ·æ¶èæ¿åçåäœæ°æ®
415	SELECT * FROM bbs7_favorite a WHERE EXISTS
416	(SELECT 1 FROM (SELECT fid FROM bbs7_forum WHERE deleted = 1) d WHERE a.fid = d.fid)
417
418
419	-- å é€çœ®é¡¶åäœæ°æ®
420	SELECT * FROM bbs7_top a WHERE EXISTS (SELECT 1 FROM bbs7_topic_1 b WHERE a.tid = b.tid);
421
422	-- å é€é«äº®åäœæ°æ®
423	SELECT * FROM bbs7_topic_highlight a WHERE EXISTS (SELECT 1 FROM bbs7_topic_1 b WHERE a.tid = b.tid);
424
425
426
427
428
429
430
431
432
433
434
435

Download in other formats:

Original Format