求多个子表填写次数统计的SQL

Question

问题描述:
现要统计工作计划填写次数.工作计划的数据分三个表存储,主表(WORKPLAN)记录填写人,计划条目(WORKPLAN_ITEM)记录具体的计划,计划完成情况(WORKPLAN_ITEM_COMPLETE)记录计划完成后的条目.现在要一同统计出,每个人的计划填写次数,完成情况填写次数.即统计主表在两个子表中有对应数据的条数.下面是我写的错误的语句.select 
org.name as name,
count(distinct p0.id) as times,
count(distinct p1.id) as timesC
from
WORKPLAN as p0,
WORKPLAN as p1,
ORGMODEL_USERINFO as org,
WORKPLAN_ITEM as si,
WORKPLAN_ITEM_COMPLETE as sci
where 
p0.id = si.planid and
p1.id = sci.planid and
org.id = p0.creator
group by p0.creator, org.name

Answer 1

select a.id , b.cnt , c.cnt
from WORKPLAN a , 
(select id , count(*) as cnt from WORKPLAN_ITEM group by id) b,
(select id , count(*) as cnt from WORKPLAN_ITEM_COMPLETE group by id) c
where a.id = b.id and a.id = c.id

Answer 2

select 
org.name as name,
count(distinct si.planid) as times,
count(distinct sci.planid) as timesC
from
WORKPLAN as p0 inner join ORGMODEL_USERINFO as org on org.id = p0.creator
left join WORKPLAN_ITEM as si on p0.id = si.planid  
         left join WORKPLAN_ITEM_COMPLETE as sci on p0.id = sci.planidgroup by org.name

Answer 3

WORKPLAN(id, creator)
-------------------
1  1
2  2
3  1
-------------------
WORKPLAN_ITEM(id, planid, content) id refrence WORKPLAN(id)
-------------------
1  1  'ic11'
2  1  'ic21'
3  2  'ic32'
3  3  'ic32'
-------------------WORKPLAN_ITEM_COMPLETE(id, planid, content) id refrence WORKPLAN(id)
-------------------
1  1  'cc11'
2  1  'cc21'
-------------------ORGMODEL_USERINFO (id, name)
-------------------
1  'a'
2  'b'
-------------------输出结果应为
-------------------
a  2  1
b  1  0
-------------------先对楼上表示感谢.

Answer 4

谢谢 coolingpipe(冷箫轻笛)
同时也谢谢 dawugui(潇洒老乌龟)