请问这句sql查询语句怎么优化?
部门表中,假设本级部门的ID=211,则下级部门及本级部门的所有id通过如下函数取得(各id用英文的逗号分割):
GetAllChildID(211)
现在的问题是:
如果得到的下级部门的id很多(>60),如:211,293,295,296,297,298,299,300,301,302,304,719,499,502,504,728,576,773,776,491,257,732,286,306,308,309,312,313,315,316,317,318,319,320,287,492,493,494,495,496,497,498,500,501,503,734,733,505,506,507,508,509,510,515,516,517,518,511,514,288,289,290,291,292
那么,以下的查询语句将很慢(table表中有百万条数据)
select * from [table] where bm_id in (GetAllChildID(211))
有更好的方法吗?
网上查了下说用 EXISTS 代替 in ,可语句怎么写呢?效果有提升吗?