一个table,就两个field, ManagerID 和 direct Reporter ID 要求是得出每个manager下面的有多少direct和indirect reporters
z*u
2 楼
试着写了下self join: select count(distinct dr.drid) as indr_cnt, count(distinct m.drid) as dr_cnt , m.managerid from table as dr join table as m on dr.managerid=m.drid group by m.managerid
q*x
3 楼
direct: select ManagerId, count(ReporterId) from table group by 1 indirect: i wonder if sql can do this.
【在 t*****e 的大作中提到】 : 一个table,就两个field, ManagerID 和 direct Reporter ID : 要求是得出每个manager下面的有多少direct和indirect reporters
q*x
4 楼
can this query count n-level indirect reports?
cnt
【在 z*******u 的大作中提到】 : 试着写了下self join: : select count(distinct dr.drid) as indr_cnt, count(distinct m.drid) as dr_cnt : , m.managerid : from table as dr : join table as m : on dr.managerid=m.drid : group by m.managerid
e*s
5 楼
这个应该得用recursion
【在 q****x 的大作中提到】 : can this query count n-level indirect reports? : : cnt
h*d
6 楼
single query肯定不行,可以搞temp table什么的一层一层递归?
【在 q****x 的大作中提到】 : can this query count n-level indirect reports? : : cnt
l*8
7 楼
一个公司的管理层次也就几层到十几层吧? 用个比较土的sql也应该能写出来。
【在 e*******s 的大作中提到】 : 这个应该得用recursion
e*s
8 楼
问题是不知道有多少层啊, 算是unbounded computation, 简单的SQL估计不行. 试着写了一个 假设已有那个表是Direct(mID, dID), mID是manager id, dID是相应的direct report ID. 那第一问可以用楼上矿工写的 select mID, count(dID) from Direct group by mID; 第二问, 求每个manager的所有report (indirect + direct),可以: with recursive Indirect(manager, reporter) as (select mID as manager, dID as reporter from Direct union select Indirect.manager, Direct.dID as reporter from Indirect, Direct where Indirect.reporter = Direct.mID) 然后就和第一问一样了: select manager, count(reporter) from Indirect group by manager;