跪求一条查询语句,我实在写不出来呀!!!!!

Question

select 科室名称 = (select SectionOffice from SectionOffice where SectionOfficeID =  tmp.SectionOfficeID),
人次 = isnull(count(*),0),
挂号费 = isnull(sum(tmp.Register),0),
出诊费 = isnull(sum(tmp.Visit),0),
物理检查费 = isnull(sum(tmp.CheckIncome),0)
from (select ectionOfficeID,ServicePriceID,Register,Visit,CheckIncome,VisitTime 
      from Checkup  
      where ServicePriceID in (select ServicePriceID from ServicePrice where  ServiceTypeID = (select ServiceTypeID from ServiceType where ServiceType = '物理检查'))) tmp where tmp.VisitTime between '2005-10-1' and '2005-11-30'
逻辑根本就不对呀.我应该怎莫关联着五个表呀,主次关系是什磨? 能给个思路也行,把关键地方的逻辑指点一下呀.小弟不是懒呀,实在是头大了.

Answer 1

select 
科室ID = b.SectionOfficeID,
科室名称 = b.SectionOffice,
人次   = count(incomeID),
挂号费 = isnull(sum(Register),0),
出诊费 = isnull(sum(Visit),0),
        药费 = isnull(sum(Medicine),0),
        物理检查费 = (select isnull(sum(CheckIncome),0) from Checkup where SectionOfficeID = a.SectionOfficeID and ServicePriceID in (select ServicePriceID 
from ServicePrice 
where ServiceTypeID = (select ServiceTypeID from ServiceType where ServiceType = '物理检查'))
and (VisitTime between '2005-10-1' and '2005-11-30') )
from 
        ServiceIncome a,SectionOffice b
where 
a.SectionOfficeID in (select SectionOfficeID from SectionOffice)
and     
        a.SectionOfficeID = b.SectionOfficeID
and
        a.VisitTime between '2005-10-1' and '2005-11-30'
group by b.SectionOfficeID,b.SectionOffice
帮忙看一下逻辑对不对。