可以建立一临时表输出数据
例:
CREATE PROCEDURE DateReportOfOutHosAccount(@number char(10),@stime datetime,@etime datetime) AS set nocount on declare @Bed varchar(4096),@Heat varchar(4096),@WestMedi varchar(4096),@ChWMedi varchar(4096),
@ChinMedi varchar(4096),@Check varchar(4096),@Cure varchar(4096),@Xray varchar(4096),
@Surgery varchar(4096),@Test varchar(4096),@Blood varchar(4096),@O2 varchar(4096),
@Delivery varchar(4096),@CT varchar(4096),@MRI varchar(4096),@Nurse varchar(4096),
@Diagnose varchar(4096),@Other varchar(4096),@Dep varchar(4096);
set @Bed=''; set @Heat=''; set @WestMedi=''; set @ChWMedi=''; set @ChinMedi=''; set @Check='';
set @Cure=''; set @Xray=''; set @Surgery=''; set @Test=''; set @Blood=''; set @O2='';
set @Delivery=''; set @CT=''; set @MRI=''; set @Nurse=''; set @Diagnose=''; set @Other=''; set @Dep='';
CREATE TABLE #ToShowDep_Temp (
[DepName] [varchar] (20) NOT NULL ,
[DepID] [smallint] NOT NULL
)
--为了屏障
CREATE TABLE #ToShowAll_Temp (
[PaDep] [smallint] NOT NULL ,
[ToTalMoney] [money] NOT Null,
[sortID] [tinyint] NOT NULL
) CREATE TABLE #TempInoutID (
[BalanceID] [int] not null
)
insert into #TempInoutID (BalanceID)
SELECT distinct b.balanceid
FROM InhosOperAccount a ,InHosFeesBalanceAccount b
where a.ID = b.AccountID and a.AccountTime>=@stime and a.AccountTime<@etime insert #ToShowAll_Temp(PaDep,ToTalMoney,sortID)
SELECT a.PaDep,sum(a.ToTalMoney*payradio),a.sortID
FROM InHosFeesSortBalanceInfo a, #TempInoutID b
WHERE (a.BalanceID=b.BalanceID) --and a.padep in(22,24,4,37)
group by a.sortID, a.PaDep
order by a.PaDep------------------------------------------------------------------------------------- insert #ToShowDep_Temp(DepID,DepName)
SELECT distinct D.depid,D.depName
--SELECT D.depid, D.depname
FROM #ToShowAll_Temp T, Dep_table D
WHERE T.PaDep=D.DepID --and D.depid in(22,24,4,37)
ORDER BY D.depid SELECT @Dep=@Dep+convert(varchar(6),depid)+',"'+depName+'",' FROM #ToShowDep_Temp select @Bed=@Bed+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=1
select @Heat=@Heat+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=17
select @WestMedi=@WestMedi+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=2
select @ChWMedi=@ChWMedi+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=3
select @ChinMedi=@ChinMedi+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=4
select @Check=@Check+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=5
select @Cure=@Cure+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=6
select @Xray=@Xray+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=7
select @Surgery=@Surgery+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=8
select @Test=@Test+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=9
select @Blood=@Blood+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=10
select @O2=@O2+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=11
select @Delivery=@Delivery+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=19
select @CT=@CT+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=13
select @MRI=@MRI+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=14
select @Nurse=@Nurse+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=15
select @Diagnose=@Diagnose+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=16
select @Other=@Other+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=18set nocount off select m0=@Bed,m1=@Heat,m2=@WestMedi,m3=@ChWMedi,m4=@ChinMedi,m5=@Check,m6=@Cure,m7=@Xray,m8=@Surgery,
m9=@Test,m10=@Blood,m11=@O2,m12=@Delivery,m13=@CT,m14=@MRI,m15=@Nurse,m16=@Diagnose,m17=@Other,m18=@DepGO这是以前写过的一医疗管理系统中患者费用统计过程,可参考其中的临时表用法!
例:
CREATE PROCEDURE DateReportOfOutHosAccount(@number char(10),@stime datetime,@etime datetime) AS set nocount on declare @Bed varchar(4096),@Heat varchar(4096),@WestMedi varchar(4096),@ChWMedi varchar(4096),
@ChinMedi varchar(4096),@Check varchar(4096),@Cure varchar(4096),@Xray varchar(4096),
@Surgery varchar(4096),@Test varchar(4096),@Blood varchar(4096),@O2 varchar(4096),
@Delivery varchar(4096),@CT varchar(4096),@MRI varchar(4096),@Nurse varchar(4096),
@Diagnose varchar(4096),@Other varchar(4096),@Dep varchar(4096);
set @Bed=''; set @Heat=''; set @WestMedi=''; set @ChWMedi=''; set @ChinMedi=''; set @Check='';
set @Cure=''; set @Xray=''; set @Surgery=''; set @Test=''; set @Blood=''; set @O2='';
set @Delivery=''; set @CT=''; set @MRI=''; set @Nurse=''; set @Diagnose=''; set @Other=''; set @Dep='';
CREATE TABLE #ToShowDep_Temp (
[DepName] [varchar] (20) NOT NULL ,
[DepID] [smallint] NOT NULL
)
--为了屏障
CREATE TABLE #ToShowAll_Temp (
[PaDep] [smallint] NOT NULL ,
[ToTalMoney] [money] NOT Null,
[sortID] [tinyint] NOT NULL
) CREATE TABLE #TempInoutID (
[BalanceID] [int] not null
)
insert into #TempInoutID (BalanceID)
SELECT distinct b.balanceid
FROM InhosOperAccount a ,InHosFeesBalanceAccount b
where a.ID = b.AccountID and a.AccountTime>=@stime and a.AccountTime<@etime insert #ToShowAll_Temp(PaDep,ToTalMoney,sortID)
SELECT a.PaDep,sum(a.ToTalMoney*payradio),a.sortID
FROM InHosFeesSortBalanceInfo a, #TempInoutID b
WHERE (a.BalanceID=b.BalanceID) --and a.padep in(22,24,4,37)
group by a.sortID, a.PaDep
order by a.PaDep------------------------------------------------------------------------------------- insert #ToShowDep_Temp(DepID,DepName)
SELECT distinct D.depid,D.depName
--SELECT D.depid, D.depname
FROM #ToShowAll_Temp T, Dep_table D
WHERE T.PaDep=D.DepID --and D.depid in(22,24,4,37)
ORDER BY D.depid SELECT @Dep=@Dep+convert(varchar(6),depid)+',"'+depName+'",' FROM #ToShowDep_Temp select @Bed=@Bed+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=1
select @Heat=@Heat+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=17
select @WestMedi=@WestMedi+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=2
select @ChWMedi=@ChWMedi+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=3
select @ChinMedi=@ChinMedi+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=4
select @Check=@Check+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=5
select @Cure=@Cure+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=6
select @Xray=@Xray+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=7
select @Surgery=@Surgery+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=8
select @Test=@Test+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=9
select @Blood=@Blood+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=10
select @O2=@O2+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=11
select @Delivery=@Delivery+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=19
select @CT=@CT+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=13
select @MRI=@MRI+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=14
select @Nurse=@Nurse+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=15
select @Diagnose=@Diagnose+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=16
select @Other=@Other+convert(varchar(6),PaDep)+','+convert(varchar(20),ToTalMoney)+','
from #ToShowAll_Temp where sortID=18set nocount off select m0=@Bed,m1=@Heat,m2=@WestMedi,m3=@ChWMedi,m4=@ChinMedi,m5=@Check,m6=@Cure,m7=@Xray,m8=@Surgery,
m9=@Test,m10=@Blood,m11=@O2,m12=@Delivery,m13=@CT,m14=@MRI,m15=@Nurse,m16=@Diagnose,m17=@Other,m18=@DepGO这是以前写过的一医疗管理系统中患者费用统计过程,可参考其中的临时表用法!
解决方案 »
- 江湖救急,数据库服务器配置问题??? Web访问不到。。。
- 重要:int类型与varchar类型的隐式转化
- 怎样让copy path1 path2命令中的path1可以动态生成?
- 查询条件中一个是字符型,一个是int型,老是报错
- 想问一下,怎么查看SQL表里面的函数?
- 怎样建一个用户只能对某数据库的某个视图有访问权限?
- 期待高手!如果从一个表出发,可以通过两条路径,联系到另一个表,请问是否潜在地违反了某一个范式???如何解决!
- 问题求教?
- 如何将光标返回的记录进行更新
- Sybase存储过程运行太慢,怎么写可以编译的存储过程,扩展存储过程?
- 怎么在SELECT加入一列自增的数据!!!
- 怎样把现成数据表的某些字段的数据“定期自动”地复制到一个已经存在的新表当中(SQL语句)
select 1 from 结存库 where convert(monthfieldname='2003.01' and ckh='01'
)
select 1 from 结存库 where convert(monthfieldname='2003.01' and ckh='01'
else
select ckh,sum(sl) sl from
(
select ckh,sl from 月末结存库 where monthfieldname='2002.12' and ckh='01'
union all
select ckh,-1*sl from 领料库 where monthfieldname='2003.01' and ckh='01'
union all
select ckh,sl from 进货库 where monthfieldname='2003.01' and ckh='01'
union all
select ckh,sl from 进货库 where monthfieldname='2003.01' and ckh='01'
) a group by ckh