某一个部门下所有人员

非树状结构每个部门下的总人数

with T as(
  select _id,name from department_table where _id='8888888888'
  union all
  select D._id,D.name from department_table D,T
  where D.parent_id=T._id
)
select * from T

如果部门结构为树状结构

select distinct(a._id) as mem_id from police_table a, (
with T as(
    select _id,name from department_table  where _id='ff808081596487b7015aacdbd50c0fe2'
    union all
    select D._id,D.name from department_table D,T  
    where D.parent_id=T._id
)
select * from T ) bb 
where a.dep_id = bb._id 

select count(distinct(a._id) )as number from police_table a, imid_table imt on a.code = imt.account, (
with T as(
  select _id,name from department_table where _id='ff8080814e6c589a014e6c9764e60026'
  union all
  select D._id,D.name from department_table D,T
  where D.parent_id=T._id
)
select * from T ) bb
where a.dep_id = bb._id and a.delete_flag = '0' and imt.isOnline='Y'

代码如下

String sql = "select count(distinct(a._id) )as number from police_table a, imid_table imt on a.code = imt.account, (\n" +
  "with T as(\n" +
  " select _id,name from department_table where _id= " + "'" + id + "'" +
  " union all\n" +
  " select D._id,D.name from department_table D,T \n" +
  " where D.parent_id=T._id\n" +
  ")\n" +
  "select * from T ) bb \n" +
  "where a.dep_id = bb._id and a.delete_flag = '0' and a.atNo NOTNULL and a.atNo NOTNULL and a._id != " + "'"+ShareData.getUserInfo(mContext).getId()+"'";