Copyright notice: This article is an original article and follows
CC 4.0 BY-NC-ND
Copyright agreement. If you want to repost this article, please attach the original source link and this statement.
Source link:
https://www.v2know.com/MainPage/PreView/881
查询是否存在父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不支持的语句比我想象的要多，因此多余的子评论只能选择手动删除了。