查询是否存在父Id和该表其他Id一致的数据:
SELECT * FROM Comments A WHERE EXISTS(SELECT * FROM Comments B WHERE A.`ParentId`=B.`Id`)
这一条可以查询出来到底还要多少条合法子数据。
非法子数据直接加NOT会把父Id=0也筛选出来:
SELECT * FROM Comments A WHERE NOT EXISTS(SELECT * FROM Comments B WHERE A.`ParentId`=B.`Id` )
所以应该再加一句AND ParentId!=0:
SELECT * FROM Comments A WHERE NOT EXISTS(SELECT * FROM Comments B WHERE A.`ParentId`=B.`Id`) AND ParentId!=0
但是最后删除的话,用这句其实是不行的:
DELETE A FROM Comments A WHERE NOT EXISTS(SELECT * FROM Comments B WHERE A.`ParentId`=B.`Id`) AND ParentId!=0
你会获得一个错误叫MySQL Error 1093 - Can't specify target table for update in FROM clause
按照
的说法
In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html
在MySQL你无法修改你SELECT出来的同一张表的内容
而INNER JOIN的用法
https://segmentfault.com/a/1190000017369618
SELECT * FROM Comments A INNER JOIN Comments B ON NOT A.`ParentId`=B.`Id`
↓
SELECT * FROM Comments A FULL OUTER JOIN Comments B ON NOT A.`ParentId`=B.`Id`
然后你发现MySQL中不支持 FULL OUTER JOIN。
至此我发现非常严重的问题,免费的MySQL不支持的语句比我想象的要多,因此多余的子评论只能选择手动删除了。
Today's comments have reached the limit. If you want to comment, please wait until tomorrow (UTC-Time).
There is 18h48m59s left until you can comment.