mysql 树查询语句

求下级所有节点

SELECT
	b.*
FROM
	tree AS a,
	tree AS b
WHERE
	a.id= b.pid
AND(a.id= ? OR a.pi= ?)

求所有子节点

SELECT
	*
FROM
	tree t
WHERE
	NOT EXISTS(
		SELECT
			1
		FROM
			tree  t1,
			tree t2
		WHERE
			t1.id= t2.pid
		AND t1.id= t.id
	)

求所有根节点

SELECT
	*
FROM
	tree t
WHERE
	NOT EXISTS(
		SELECT
			1
		FROM
			tree t1,
			tree t2
		WHERE
			t1.id= t2.pid
		AND t1.id= t.pid
	)

评论

1 楼 zgw06629 2013-11-14  

第一种只能查到第三层，哪是所有子节点