mysql查询一个节点的根节点

java版的实际例子。类同你说的情况

private void findChildList(AssetType parent,List<AssetType>list){

String hql = "from AssetType a where a.parentAssetType.assetTypeId=? ORDER BY a.sort,a.assetTypeName asc"

List<AssetType>childList = this.assetTypeDao

.getEntityManager()

.createQuery(hql)

.setParameter(1, parent.getAssetTypeId())

.getResultList()

int size = childList.size()

if(size>0){

for (int i = 0i <sizei++) {

AssetType assetType = childList.get(i)

List<AssetType>childs = assetType.getChildAssetType()

if(childs.size()>0){

list.addAll(childs)

this.findChildList(assetType, list)//递归查询节点的子节点

}

}

}

}

SELECT * FROM NODE

WHERE PREV_NODE_ID NOT IN (SELECT DISTINCT NODE_ID FROM NODE)

AND PRNT_NODE_ID NOT IN (SELECT DISTINCT NODE_ID FROM NODE)

将 SELECT * 换成 DELETE 即可删除了

那就将语句改成下面这样

DELETE FROM NODE A

WHERE A.PREV_NODE_ID NOT IN (SELECT DISTINCT B.NODE_ID FROM NODE B WHERE B.ID = A.ID)

AND A.PRNT_NODE_ID NOT IN (SELECT DISTINCT B.NODE_ID FROM NODE B WHERE B.ID = A.ID)

select id,

name,

pid

from(select * from products

order by pid, id) products_sorted,

(select @pv := '1') initialisation

where find_in_set(pid, @pv)

and length(@pv := concat(@pv, ',', id))

---