我先列举一下表结构：
inpatient为入院病人表（in_seq为患者序号，out_date为出院时间，case_in_seq病案号（该字段无论转科，都是不变的）），intransferdept为转科表（org_seq为转科前序号，now_seq为转科后序号），
infeesheet为住院费用表（in_seq为患者序号，amout为数量，price为价格,top_no为费用大类），且一个病人会有多条费用信息。
现在我要查询所有患者的明细，其它地方都正常，但是转科的病人无法处理，因为转科后有两个序号，如何将两个序号合并，而且费用也要合并。完整语句如下，只要给出思路就好了，我纠结了好久。
SELECT '出院科室'=department.dept_name,
'住院序号'=inpatient.in_seq,
'病案号'=inpatient.case_in_seq,
'患者姓名'=inpatient.pat_name,
'患者类型'=patienttype.typ_name,
'入院时间'=(convert(varchar(10),inpatient.in_date,120)),
'出院时间'=(convert(varchar(10),inpatient.out_date,120)),
'住院天数'=(select datediff(dd,inpatient.in_date,inpatient.out_date)),
'办理人员'=person.per_name,
'预交金'=isnull((select sum(inchargemoney.pre_money) from inchargemoney where inpatient.in_seq=inchargemoney.in_seq and inchargemoney.pre_type='1'),0),
'总费用'=isnull((select sum(inusemed.amount*inusemed.price) from inusemed where inusemed.in_seq=inpatient.in_seq and inusemed.issue_date is not null),0)+isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet where infeesheet.in_seq=inpatient.in_seq and infeesheet.audit_date is not null),0),
'补交现金'=isnull((select sum(insupplymoney.sup_money) from insupplymoney where insupplymoney.sup_type='1' and insupplymoney.in_seq=inpatient.in_seq),0),
'退现金'=isnull((select sum(inbackmoney.bck_money) from inbackmoney where inbackmoney.in_seq=inpatient.in_seq),0),
'医保支付'=isnull((select sum(inchargemoney.pre_money) from inchargemoney where inpatient.case_in_seq=inchargemoney.in_seq and inchargemoney.pre_type in ('7','8','14')),0)+isnull((select sum(insupplymoney.sup_money) from insupplymoney where insupplymoney.sup_type='7' and insupplymoney.in_seq=inpatient.in_seq),0),
'新农合统筹'=isnull((select sum(inchargemoney.pre_money) from inchargemoney where inpatient.in_seq=inchargemoney.in_seq and inchargemoney.pre_type not in ('1','4','7','8','14')),0)+isnull((select sum(insupplymoney.sup_money) from insupplymoney where insupplymoney.sup_type not in ('1','4','7','8') and insupplymoney.in_seq=inpatient.in_seq),0),
'西药费'=isnull((select sum(inusemed.amount*inusemed.price) from inusemed,storeindetail,medict where storeindetail.med_no=medict.med_no and inusemed.in_id=storeindetail.in_id and medict.top_no='01' and inusemed.in_seq=inpatient.in_seq and issue_date is not null),0),
'中成药费'=isnull((select sum(inusemed.amount*inusemed.price) from inusemed,storeindetail,medict where storeindetail.med_no=medict.med_no and inusemed.in_id=storeindetail.in_id and medict.top_no='02' and inusemed.in_seq=inpatient.in_seq and issue_date is not null),0),
'中草药费'=isnull((select sum(inusemed.amount*inusemed.price) from inusemed,storeindetail,medict where storeindetail.med_no=medict.med_no and inusemed.in_id=storeindetail.in_id and medict.top_no='03' and inusemed.in_seq=inpatient.in_seq and issue_date is not null),0),
'药房卫生材料'=isnull((select sum(inusemed.amount*inusemed.price) from inusemed,storeindetail,medict where storeindetail.med_no=medict.med_no and inusemed.in_id=storeindetail.in_id and medict.top_no='04' and inusemed.in_seq=inpatient.in_seq and issue_date is not null),0),
'床位费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='00' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'诊查费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='02' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'护理费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='03' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'治疗费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='04' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'化验费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='05' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'检查费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='06' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'放射费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='07' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'手术费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='10' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'心电图费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='13' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'B超费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='18' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'针推理疗费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='29' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'金域检验费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='33' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'外检'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='35' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'碎石费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='36' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'卫生材料'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='23' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'输氧费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='12' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'煎药费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='34' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'特殊材料费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='27' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'注射费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='31' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'采血费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='32' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'血费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='28' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'麻醉费'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no='09' and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'其他费用'=isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet,feedetail where infeesheet.fee_no=feedetail.fee_no and feedetail.top_no not in ('00','02','03','04','05','06','07','10','13','18','29','33','35','36','23','12','34','27','31','32','28','09') and infeesheet.in_seq=inpatient.in_seq and chk_date is not null),0),
'出生日期'=(convert(varchar(10),inpatient.birth_date,120)),
'出院床位'=bed.bed_name
FROM inpatient left OUTER JOIN bed on inpatient.bed_no=bed.bed_no ,department,person,patienttype
WHERE inpatient.out_treatdate is not null and inpatient.dept_no=department.dept_no and
inpatient.out_treatperson=person.per_no and inpatient.typ_no=patienttype.typ_no
and inpatient.in_seq in (
select case when sm>0 then seq else null end from (select 'seq'=in_seq,'sm'=isnull((select sum(inusemed.amount*inusemed.price) from inusemed where inusemed.in_seq=inpatient.in_seq and inusemed.issue_date is not null),0)+isnull((select sum(infeesheet.amount*infeesheet.price) from infeesheet where infeesheet.in_seq=inpatient.in_seq and infeesheet.audit_date is not null),0)
from inpatient where inpatient.print_date between '2014-07-20 00:00:00'and '2014-07-25 23:59:59' ) a )
and inpatient.out_type<>3 and inpatient.in_seq not in (select in_seq from inpatient_out_rollback)
------解决思路----------------------
infeesheet 关联 inpatient 得到 case_in_seq 字段，对 infeesheet 的统计通过这个 case_in_seq 而不是 in_seq 就能合并了。
inpatient 中同一个 case_in_seq 下 Min(in_date)和Max(out_date)就是入/出院时间。
inpatient 中同一个 case_in_seq 下最大的那个 in_seq 应该是最新的吧！用它来出“出院科室”、“出院床位”等。
建议把 case_in_seq 作为冗余字段带入 intransferdept 和 infeesheet，会方便的多。
否则转院不止一次，对 intransferdept 就要做递归了。
------解决思路----------------------
inchargemoney 和 infeesheet 一样关联 inpatient 可得到 case_in_seq 字段。不就可以统计了。
既然有这样的统计需求，那么把 case_in_seq 作为冗余字段带入各表是最简便的做法。
否则都要关联 inpatient 写起来麻烦、还影响性能。