ALTER proc AllRecord @qsrq datetime,@jsrq datetime,@fymc varchar(10)
as
select fpb_mx.fymc, sum(je),max(fpb_main.jzrq) as rq
fpb_main,fpb_mx
where fpb_main.fph=fpb_mx.fph
and fpb_main.zfbz=0
and fpb_main.jzrq>=@qsrq and fpb_main.jzrq<=@jsrq
and fpb_mx.fymc=@fymc
group by fpb_mx.fymc
as
select fpb_mx.fymc, sum(je),max(fpb_main.jzrq) as rq
fpb_main,fpb_mx
where fpb_main.fph=fpb_mx.fph
and fpb_main.zfbz=0
and fpb_main.jzrq>=@qsrq and fpb_main.jzrq<=@jsrq
and fpb_mx.fymc=@fymc
group by fpb_mx.fymc
id fph gzh fybh fymc je
459 127435152 00000107 05 化验费 87.00
460 127435152 00000107 09 心电图 30.00
461 127398755 00000092 06 放射费 60.00
462 127398756 00000096 06 放射费 60.00
463 127398757 00000008 06 放射费 5.00
465 127435154 00000108 05 化验费 87.00
466 127435154 00000108 09 心电图 30.00
467 127435154 00000108 14 CT费 200.00
468 127398758 00000063 06 放射费 60.00
469 127435155 0000085 49 B超 60.00
470 127435155 00000085 34 乳腺治疗 45.00
471 127345549 00000111 14 CT费 200.00
472 127398760 00000020 02 中成药 36.80
473 127398760 00000020 01 西药费 6.20
fpb_main 是另一个表名
from fpb_main,fpb_mx
where fpb_main.fph=fpb_mx.fph
and fpb_main.zfbz=0
and fpb_main.jzrq>=@qsrq and fpb_main.jzrq<=@jsrq
and fpb_mx.fymc=@fymc
group by fpb_mx.fymc
create proc all_je as declare @fymc varchar(20)DECLARE myCursor CURSOR FOR SELECT [fymc] FROM [fyszb]OPEN myCursorFETCH NEXT FROM myCursor
INTO @fymcWHILE @@FETCH_STATUS = 0BEGINselect max(fpb_main.jzrq) as rq,fpb_mx.fymc,sum (fpb_mx.je) as je
from fpb_main,fpb_mx
where fpb_main.fph=fpb_mx.fph
and fpb_main.zfbz=0
and fpb_main.jzrq>='2003-1-10' and fpb_main.jzrq<='2003-4-8'
and fpb_mx.fymc=@fymc
group by fpb_mx.fymc
FETCH NEXT FROM myCursor
into @fymc
END
close myCursor
DEALLOCATE myCursor
select fpb_mx.fymc, sum(je),max(fpb_main.jzrq) as rq
from fpb_main,fpb_mx
where fpb_main.fph=fpb_mx.fph
and fpb_main.zfbz=0
--and fpb_mx.fymc=@fymc (不要此条件)
and fpb_main.jzrq>='2003-1-10' and fpb_main.jzrq<='2003-4-8'group by fpb_mx.fymc