来一个递归WITH FUCK AS
(
SELECT * FROM category WHERE Id=10000
UNION ALL
SELECT * FROM FUCK INNER JOIN category
ON FUCK.Id=category.pid
)SELECT * FROM FUCK
(
SELECT * FROM category WHERE Id=10000
UNION ALL
SELECT * FROM FUCK INNER JOIN category
ON FUCK.Id=category.pid
)SELECT * FROM FUCK
--貌似楼主并没有说要搜索二级类以及它子节点的产品。只是要二级类的产品。SQL如:select a.* from product a,category b
where a.category_id =b.id and
exists(select 1 from category where pid=0 and id=b.pid)
select * from product where category_id like 12%
这样是否可行?效率是不是比递归好很多呢?
select * from product where category_id like 12%
还有一种,首先把类别表的数据通过程序缓存一下,然后,当别人按照类别搜索的时候,从缓存中取出他所有的子类的id值,然后通过
select * from product where category_id in (缓存类别id)还有就是,从缓存中查询出子类别id后,select * from product where category_id=? or category_id=?。
或者直接递归查询需要的数据WITH FUCK AS
(
SELECT * FROM category WHERE Id=10000
UNION ALL
SELECT * FROM FUCK INNER JOIN category
ON FUCK.Id=category.pid
)SELECT * FROM FUCK
哪种效率好呢?或者有更好的办法?