重複したデータを消すSQL 標準SQLのみ

data.csv

result.log

sqlite> select * from test;
no,name,a,b,c,d,e,s,t,id,evo
386,デオキシス(ノーマル),50,150,50,150,50,150,エスパー,386,
386,デオキシス(アタック),50,180,20,180,20,150,エスパー,387,
386,デオキシス(ディフェンス),50,70,160,70,160,90,エスパー,388,
386,デオキシス(スピード),50,95,90,95,90,180,エスパー,389,
387,ナエトル,55,68,64,45,55,31,くさ,390,
388,ハヤシガメ,75,89,85,55,65,36,くさ,391,390
389,ドダイトス,95,109,105,75,85,56,くさ/じめん,392,391
390,ヒコザル,44,58,44,58,44,61,ほのお,393,
391,モウカザル,64,78,52,78,52,81,ほのお/かくとう,394,393
392,ゴウカザル,76,104,71,104,71,108,ほのお/かくとう,395,394
393,ポッチャマ,53,51,53,61,56,40,みず,396,
394,ポッタイシ,64,66,68,81,76,50,みず,397,396
395,エンペルト,84,86,88,111,101,60,みず/はがね,398,397
396,ムックル,40,55,30,30,30,60,ノーマル/ひこう,399,
397,ムクバード,55,75,50,40,40,80,ノーマル/ひこう,400,399
398,ムクホーク,85,120,70,50,50,100,ノーマル/ひこう,401,400
399,ビッパ,59,45,40,35,40,31,ノーマル,402,
400,ビーダル,79,85,60,55,60,71,ノーマル/みず,403,402
401,コロボーシ,37,25,41,25,41,25,むし,404,
402,コロトック,77,85,51,55,51,65,むし,405,404
403,コリンク,45,65,34,40,34,45,でんき,406,
404,ルクシオ,60,85,49,60,49,60,でんき,407,406
405,レントラー,80,120,79,95,79,70,でんき,408,407
406,スボミー,40,30,35,50,70,55,くさ/どく,409,
407,ロズレイド,60,70,55,125,105,90,くさ/どく,410,315
408,ズガイドス,67,125,40,30,30,58,いわ,411,
409,ラムパルド,97,165,60,65,50,58,いわ,412,411
410,タテトプス,30,42,118,42,88,30,いわ/はがね,413,
411,トリデプス,60,52,168,47,138,30,いわ/はがね,414,413
412,ミノムッチ,40,29,45,29,45,36,むし,415,
413,ミノマダム(くさ),60,59,85,79,105,36,むし/くさ,416,415
413,ミノマダム(すな),60,79,105,59,85,36,むし/じめん,417,415
413,ミノマダム(ゴミ),60,69,95,69,95,36,むし/はがね,418,415
414,ガーメイル,70,94,50,94,50,66,むし/ひこう,419,415
sqlite> delete from test where id in (select test.id from test, (select * from t
est group by no having count(*)>1) as test2 where test.no=test2.no and test.id<>
test2.id);
sqlite> select * from test;
no,name,a,b,c,d,e,s,t,id,evo
386,デオキシス(スピード),50,95,90,95,90,180,エスパー,389,
387,ナエトル,55,68,64,45,55,31,くさ,390,
388,ハヤシガメ,75,89,85,55,65,36,くさ,391,390
389,ドダイトス,95,109,105,75,85,56,くさ/じめん,392,391
390,ヒコザル,44,58,44,58,44,61,ほのお,393,
391,モウカザル,64,78,52,78,52,81,ほのお/かくとう,394,393
392,ゴウカザル,76,104,71,104,71,108,ほのお/かくとう,395,394
393,ポッチャマ,53,51,53,61,56,40,みず,396,
394,ポッタイシ,64,66,68,81,76,50,みず,397,396
395,エンペルト,84,86,88,111,101,60,みず/はがね,398,397
396,ムックル,40,55,30,30,30,60,ノーマル/ひこう,399,
397,ムクバード,55,75,50,40,40,80,ノーマル/ひこう,400,399
398,ムクホーク,85,120,70,50,50,100,ノーマル/ひこう,401,400
399,ビッパ,59,45,40,35,40,31,ノーマル,402,
400,ビーダル,79,85,60,55,60,71,ノーマル/みず,403,402
401,コロボーシ,37,25,41,25,41,25,むし,404,
402,コロトック,77,85,51,55,51,65,むし,405,404
403,コリンク,45,65,34,40,34,45,でんき,406,
404,ルクシオ,60,85,49,60,49,60,でんき,407,406
405,レントラー,80,120,79,95,79,70,でんき,408,407
406,スボミー,40,30,35,50,70,55,くさ/どく,409,
407,ロズレイド,60,70,55,125,105,90,くさ/どく,410,315
408,ズガイドス,67,125,40,30,30,58,いわ,411,
409,ラムパルド,97,165,60,65,50,58,いわ,412,411
410,タテトプス,30,42,118,42,88,30,いわ/はがね,413,
411,トリデプス,60,52,168,47,138,30,いわ/はがね,414,413
412,ミノムッチ,40,29,45,29,45,36,むし,415,
413,ミノマダム(ゴミ),60,69,95,69,95,36,むし/はがね,418,415
414,ガーメイル,70,94,50,94,50,66,むし/ひこう,419,415

sql

sqlite> delete from test where id in (select test.id from test, (select * from t
est group by no having count(*)>1) as test2 where test.no=test2.no and test.id<>
test2.id);