質問: I have `table` named `Student`. It have primary key `student_id` which is `auto increment` and `not null`. Now `Student` contains around 5000 records. After passed out from university, we need to delete specific student record from `Student` and related child table as well. It is working good. I need to get those numbers who are not in `student_id` range. For ex. I have 5000 records. So assume range is 1-5000. In `Student` table, there may be data with primary key : `student_id` : 1 2 4 5 6 7 9 12 15 etc... So I want deleted ids, like : 3, 8, 10, 11, 13, 14, etc... And need this result in some text file. Finally I have 2 questions : * Need query to get those deleted ids * Can I export result in some file after fired query ?
回答: ``` SELECT n FROM ( SELECT 1000*n1+100*n2+10*n3+n4+1 n FROM ( SELECT 0 n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) n1, ( SELECT 0 n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) n2, ( SELECT 0 n3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) n3, ( SELECT 0 n4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) n4 HAVING n < ( SELECT MAX(student_id) FROM Student ) ) numbers LEFT JOIN Student ON student_id = n WHERE student_id IS NULL ``` fiddle