测试数据: create table zy_actpatient(patient_id varchar(10),admiss_times int,inpatient_no varchar(20),name varchar(10)) insert into zy_actpatient select '1',2,'aaaaaaB1','xx' union all select '2',1,'aaaaaa','xx' union all select '3',1,'bbbbbbC1','zz' union all select '4',3,'bbbbbb','zz' union all select '5',2,'bbbbbbD2','zz' union all select '6',1,'ccccccF1','mm' create table zy_detail_charge(patient_id varchar(10),admiss_times int,ledger_sn int,charge_code varchar(10), charge_price int,charge_amount int)insert into zy_detail_charge select '1',2,1,'001',10,100 union all select '2',1,1,'002',10,200 union all select '3',1,1,'001',15,400 union all select '4',3,1,'004',5,400 union all select '5',2,1,'005',7,200 union all select '6',1,1,'008',8,200存储过程:create procedure sp_B as begin select a.patient_id as patient_id,left(a.inpatient_no,6) as inpatient_no into #t from zy_actpatient a where exists(select * from zy_actpatient where left(a.inpatient_no,6)=left(inpatient_no,6) and a.patient_id<>patient_id) declare @col1 varchar(100),@col2 varchar(100) update #t set @col1=case when @col2=inpatient_no then @col1+','+patient_id else patient_id end, @col2=inpatient_no, patient_id=@col1 select inpatient_no,max(patient_id) as patient_id into #tt from #t group by inpatient_no declare tb cursor local for select patient_id from #tt order by patient_id declare @pat_id varchar(50),@str1 varchar(50),@str2 varchar(50),@sql nvarchar(1000),@sql2 varchar(1000) open tb fetch tb into @pat_id while @@fetch_status=0 begin set @str1=''''+replace(@pat_id,',',''',''')+'''' declare @ch_count int set @sql='select @a=sum(charge_amount) from zy_detail_charge where patient_id in('+@str1+')' exec sp_executesql @sql,N'@a int output',@ch_count output update zy_detail_charge set charge_amount=@ch_count where patient_id=left(@pat_id,charindex(',',@pat_id)-1) set @str2=substring(@pat_id,charindex(',',@pat_id)+1,len(@pat_id)-charindex(',',@pat_id)) set @str2=''''+replace(@str2,',',''',''')+'''' set @sql2='delete from zy_detail_charge where patient_id in('+@str2+')' exec(@sql2) delete from zy_detail_charge where patient_id in(@str2) fetch tb into @pat_id end close tb deallocate tb select * from zy_detail_charge end执行结果: patient_id admiss_times ledger_sn charge_code charge_price charge_amount ---------- ------------ ----------- ----------- ------------ ------------- 1 2 1 001 10 300 3 1 1 001 15 1000 6 1 1 008 8 200(所影响的行数为 3 行)
楼上的兄弟辛苦了,谢谢。我是这样写的 CREATE procedure patient_total as create table #temp11 (inpatient_no varchar(12)) begin insert into #temp11 select substring(inpatient_no,1,6) as inpatient_no from zy_inactpatient group by substring(inpatient_no,1,6) having count(*)=2 end select max(b.name) bill_name, sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee from view_zy_detail_charge_b a, zy_bill_item b, zy_inactpatient c, #temp11 d where a.bill_item_code=b.code and a.charge_status in('3','4') and a.ledger_sn<>0 and c.inpatient_no=d.inpatient_no and c.inpatient_no=substring(c.inpatient_no,1,6) and a.patient_id=c.patient_id and a.admiss_times=c.admiss_times
group by b.name GO 要把zy_actpatient 表里面inpatient_no里面有这样的住院号如:569897,569897B1的病人费用都合并到569897里面去,具体合并就是select max(b.name) bill_name, sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee这个语句,这个表里面只有patient_id,要把中间表的住院号关联进去,楼上的兄弟看怎么写好呢。
我把你写的改了下,可还是不行,帮我再看看,谢谢 create procedure sp_B1 as begin select a.patient_id as patient_id,left(a.inpatient_no,6) as inpatient_no into #t from zy_actpatient a where exists(select * from zy_actpatient where left(a.inpatient_no,6)=left(inpatient_no,6) and a.patient_id<>patient_id) select inpatient_no,max(patient_id) as patient_id,min(patient_id) as patient_id1 into #tt from #t group by inpatient_no end declare pt cursor for select a.patient_id,a.patient_id1 from #tt a,zy_inactpatient b where substring(b.inpatient_no,1,6)=a.inpatient_no declare @p_id1 varchar(12),@p_id2 varchar(12) open pt fetch pt into @p_id1,@p_id2 while @@fetch_status=0 select max(b.name) bill_name, sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee from view_zy_detail_charge_b a, zy_bill_item b, zy_inactpatient c, #tt d where a.bill_item_code=b.code and a.charge_status in('3','4') and a.ledger_sn<>0 and substring(c.inpatient_no,1,6)=d.inpatient_no and a.patient_id in(@p_id1,@p_id2) group by b.name 表结构基本上就只关联到inpatient_no,patient_id,就是取inpatient_no的前六位,然后根据inaptient_no关联patient_id,用patient_id把费用合计起来。view_zy_detail_charge_b表里面主要有patient_id,charge_amount,charge_price字段。
你试试这样行不行 insert into zy_detail_charge select '1',2,1,'001',10,100 union all select '2',1,1,'002',10,200 union all select '3',1,1,'001',15,400 union all select '4',3,1,'004',5,400 union all select '5',2,1,'005',7,200 union all select '6',1,1,'008',8,200insert into zy_actpatient select '1',2,'aaaaaaB1','xx' union all select '2',1,'aaaaaa','xx' union all select '3',1,'bbbbbbC1','zz' union all select '4',3,'bbbbbb','zz' union all select '5',2,'bbbbbbD2','zz' union all select '6',1,'ccccccF1','mm'create procedure sp_B as begin select a.patient_id as patient_id,left(a.inpatient_no,6) as inpatient_no into #t from zy_actpatient a where exists(select * from zy_actpatient where left(a.inpatient_no,6)=left(inpatient_no,6) and a.patient_id<>patient_id) declare @col1 varchar(100),@col2 varchar(100) update #t set @col1=case when @col2=inpatient_no then @col1+','+patient_id else patient_id end, @col2=inpatient_no, patient_id=@col1 select inpatient_no,max(patient_id) as patient_id into #tt from #t group by inpatient_no declare tb cursor local for select inpatient_no,patient_id from #tt order by patient_id declare @pat_id varchar(50),@pat_name varchar(50),@str1 varchar(50),@str2 varchar(50),@sql nvarchar(1000),@sql2 varchar(1000) open tb fetch tb into @pat_name,@pat_id while @@fetch_status=0 begin set @str1=''''+replace(@pat_id,',',''',''')+'''' declare @ch_count int set @sql='select @a=sum(charge_amount) from zy_detail_charge where patient_id in('+@str1+')' exec sp_executesql @sql,N'@a int output',@ch_count output update zy_detail_charge set charge_amount=@ch_count where patient_id=(select patient_id from zy_actpatient where inpatient_no=@pat_name ) fetch tb into @pat_name,@pat_id end close tb deallocate tb end 执行这个存储过程后 exec sp_B 再查询表 select * from zy_detail_charge patient_id admiss_times ledger_sn charge_code charge_price charge_amount ---------- ------------ ----------- ----------- ------------ ------------- 1 2 1 001 10 100 2 1 1 002 10 300 3 1 1 001 15 400 4 3 1 004 5 1000 5 2 1 005 7 200 6 1 1 008 8 200(所影响的行数为 6 行) 也就是将patient_id=1,2的数据合并,将patient_id=3,4,5的记录合并(我只是合并的charge_amount字段的值)
看来这样的SQL很难实现,我改了下插入到ba_first_page6里面来,这样的插入如何来完成。 ba_first_page6的表结构如下: patient_id,admiss_times,inpatient_no,charge_item(与bill_item_code一样),charge_fee create procedure sp_B1 as begin select a.patient_id as patient_id,left(a.inpatient_no,6) as inpatient_no into #t from zy_actpatient a where exists(select * from zy_actpatient where left(a.inpatient_no,6)=left(inpatient_no,6) and a.patient_id<>patient_id) select inpatient_no,max(patient_id) as patient_id1,min(patient_id) as patient_id2 into #tt from #t group by inpatient_no end declare pt cursor for select a.inpatient_no,a.patient_id1,a.patient_id2 from #tt a,ba_first_page6 b where b.inpatient_no=a.inpatient_no declare @inpatient_no varchar(12),@p_id1 varchar,@p_id2 varchar(12) open pt fetch pt into @inpatient_no,@p_id1,@p_id2 while @@fetch_status=0
select @p_id2,1,@inpatient_no,max(b.name) bill_name, sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee into ba_first_page6 from view_zy_detail_charge_b a, zy_bill_item b
where a.bill_item_code=b.code and a.charge_status in('3','4') and a.ledger_sn<>0 and a.patient_id=@p_id1 group by b.name close pt deallocate pt 大家看看怎么解决这个插入语句select @p_id2,1,@inpatient_no,max(b.name) charge_item, sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee into ba_first_page6 from view_zy_detail_charge_b a, zy_bill_item b
where a.bill_item_code=b.code and a.charge_status in('3','4') and a.ledger_sn<>0 and a.patient_id=@p_id1 group by b.name ,谢谢了,急
费用合并就是把zy_detail_charge表的两条记录合并吧?
create table zy_actpatient(patient_id varchar(10),admiss_times int,inpatient_no varchar(20),name varchar(10))
insert into zy_actpatient
select '1',2,'aaaaaaB1','xx'
union all
select '2',1,'aaaaaa','xx'
union all
select '3',1,'bbbbbbC1','zz'
union all
select '4',3,'bbbbbb','zz'
union all
select '5',2,'bbbbbbD2','zz'
union all
select '6',1,'ccccccF1','mm'
create table zy_detail_charge(patient_id varchar(10),admiss_times int,ledger_sn int,charge_code varchar(10),
charge_price int,charge_amount int)insert into zy_detail_charge
select '1',2,1,'001',10,100
union all
select '2',1,1,'002',10,200
union all
select '3',1,1,'001',15,400
union all
select '4',3,1,'004',5,400
union all
select '5',2,1,'005',7,200
union all
select '6',1,1,'008',8,200存储过程:create procedure sp_B
as
begin
select a.patient_id as patient_id,left(a.inpatient_no,6) as inpatient_no into #t from zy_actpatient a where
exists(select * from zy_actpatient where left(a.inpatient_no,6)=left(inpatient_no,6) and a.patient_id<>patient_id)
declare @col1 varchar(100),@col2 varchar(100)
update #t set
@col1=case when @col2=inpatient_no then @col1+','+patient_id else patient_id end,
@col2=inpatient_no,
patient_id=@col1
select inpatient_no,max(patient_id) as patient_id into #tt from #t group by inpatient_no
declare tb cursor local
for
select patient_id from #tt order by patient_id
declare @pat_id varchar(50),@str1 varchar(50),@str2 varchar(50),@sql nvarchar(1000),@sql2 varchar(1000)
open tb
fetch tb into @pat_id
while @@fetch_status=0
begin
set @str1=''''+replace(@pat_id,',',''',''')+''''
declare @ch_count int
set @sql='select @a=sum(charge_amount) from zy_detail_charge where patient_id in('+@str1+')'
exec sp_executesql @sql,N'@a int output',@ch_count output
update zy_detail_charge set charge_amount=@ch_count where patient_id=left(@pat_id,charindex(',',@pat_id)-1)
set @str2=substring(@pat_id,charindex(',',@pat_id)+1,len(@pat_id)-charindex(',',@pat_id))
set @str2=''''+replace(@str2,',',''',''')+''''
set @sql2='delete from zy_detail_charge where patient_id in('+@str2+')'
exec(@sql2)
delete from zy_detail_charge where patient_id in(@str2)
fetch tb into @pat_id
end
close tb
deallocate tb
select * from zy_detail_charge
end执行结果:
patient_id admiss_times ledger_sn charge_code charge_price charge_amount
---------- ------------ ----------- ----------- ------------ -------------
1 2 1 001 10 300
3 1 1 001 15 1000
6 1 1 008 8 200(所影响的行数为 3 行)
CREATE procedure patient_total
as
create table #temp11
(inpatient_no varchar(12))
begin
insert into #temp11
select substring(inpatient_no,1,6) as inpatient_no from zy_inactpatient
group by substring(inpatient_no,1,6)
having count(*)=2
end
select max(b.name) bill_name,
sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee
from view_zy_detail_charge_b a,
zy_bill_item b,
zy_inactpatient c,
#temp11 d
where a.bill_item_code=b.code and
a.charge_status in('3','4') and
a.ledger_sn<>0 and
c.inpatient_no=d.inpatient_no and
c.inpatient_no=substring(c.inpatient_no,1,6) and
a.patient_id=c.patient_id and
a.admiss_times=c.admiss_times
group by
b.name
GO
要把zy_actpatient 表里面inpatient_no里面有这样的住院号如:569897,569897B1的病人费用都合并到569897里面去,具体合并就是select max(b.name) bill_name,
sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee这个语句,这个表里面只有patient_id,要把中间表的住院号关联进去,楼上的兄弟看怎么写好呢。
表zy_actpatient数据:
patient_id admiss_times inpatient_no name
---------- ------------ -------------------- ----------
1 2 aaaaaaB1 xx
2 1 aaaaaa xx
3 1 bbbbbbC1 zz
4 3 bbbbbb zz
5 2 bbbbbbD2 zz
6 1 ccccccF1 mm(所影响的行数为 6 行)
表zy_detail_charge数据:
patient_id admiss_times ledger_sn charge_code charge_price charge_amount
---------- ------------ ----------- ----------- ------------ -------------
1 2 1 001 10 100
2 1 1 002 10 200
3 1 1 001 15 400
4 3 1 004 5 400
5 2 1 005 7 200
6 1 1 008 8 200
然后根据规则得到这样一个表:
inpatient_no patient_id
------------ ----------
aaaaaa 1,2
bbbbbb 3,4,5(所影响的行数为 2 行)然后用游标的方式更新zy_detail_charge表:将patient_id的第一个逗号左边的记录更新,更新的方式就是费用的相加,然后将第一个逗号右边的记录删除掉
对上表来说,对inpatient_no=aaaaaa的这条记录,更新zy_detail_charge表中patient_id=1的记录,更新的方式就是1,2两条记录的和,然后删除2记录
create procedure sp_B1
as
begin
select a.patient_id as patient_id,left(a.inpatient_no,6) as inpatient_no into #t
from zy_actpatient a where
exists(select * from zy_actpatient where left(a.inpatient_no,6)=left(inpatient_no,6)
and a.patient_id<>patient_id)
select inpatient_no,max(patient_id) as patient_id,min(patient_id) as patient_id1 into #tt
from #t group by inpatient_no
end
declare pt cursor
for
select a.patient_id,a.patient_id1 from #tt a,zy_inactpatient b
where substring(b.inpatient_no,1,6)=a.inpatient_no
declare @p_id1 varchar(12),@p_id2 varchar(12)
open pt
fetch pt into @p_id1,@p_id2
while @@fetch_status=0
select max(b.name) bill_name,
sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee
from view_zy_detail_charge_b a,
zy_bill_item b,
zy_inactpatient c,
#tt d
where a.bill_item_code=b.code and
a.charge_status in('3','4') and
a.ledger_sn<>0 and
substring(c.inpatient_no,1,6)=d.inpatient_no and
a.patient_id in(@p_id1,@p_id2)
group by b.name
表结构基本上就只关联到inpatient_no,patient_id,就是取inpatient_no的前六位,然后根据inaptient_no关联patient_id,用patient_id把费用合计起来。view_zy_detail_charge_b表里面主要有patient_id,charge_amount,charge_price字段。
这两个是两个病人,就是住院号取1到6位住院号就相同,patient_id不同的。
inpatient_no patient_id
600321(母亲) 000009247100
600321B1(婴儿) 000009247200
623569 0000006598732
表view_zy_detail_charge_b主要数据
patient_id admiss_times charge_price charge_amount
000009247100 1 300.0 1.0
000009247100 1 4.2 1.0
000009247100 1 2.0 1.0
000009247200 1 71.5 2.0
要求把婴儿的费用合并到母亲身上。
但是zy_detail_charge表中没有住院号这个字段,你根据patient_id查询吗?
谢谢
insert into zy_detail_charge
select '1',2,1,'001',10,100
union all
select '2',1,1,'002',10,200
union all
select '3',1,1,'001',15,400
union all
select '4',3,1,'004',5,400
union all
select '5',2,1,'005',7,200
union all
select '6',1,1,'008',8,200insert into zy_actpatient
select '1',2,'aaaaaaB1','xx'
union all
select '2',1,'aaaaaa','xx'
union all
select '3',1,'bbbbbbC1','zz'
union all
select '4',3,'bbbbbb','zz'
union all
select '5',2,'bbbbbbD2','zz'
union all
select '6',1,'ccccccF1','mm'create procedure sp_B
as
begin
select a.patient_id as patient_id,left(a.inpatient_no,6) as inpatient_no into #t from zy_actpatient a where
exists(select * from zy_actpatient where left(a.inpatient_no,6)=left(inpatient_no,6) and a.patient_id<>patient_id)
declare @col1 varchar(100),@col2 varchar(100)
update #t set
@col1=case when @col2=inpatient_no then @col1+','+patient_id else patient_id end,
@col2=inpatient_no,
patient_id=@col1
select inpatient_no,max(patient_id) as patient_id into #tt from #t group by inpatient_no
declare tb cursor local
for
select inpatient_no,patient_id from #tt order by patient_id
declare @pat_id varchar(50),@pat_name varchar(50),@str1 varchar(50),@str2 varchar(50),@sql nvarchar(1000),@sql2 varchar(1000)
open tb
fetch tb into @pat_name,@pat_id
while @@fetch_status=0
begin
set @str1=''''+replace(@pat_id,',',''',''')+''''
declare @ch_count int
set @sql='select @a=sum(charge_amount) from zy_detail_charge where patient_id in('+@str1+')'
exec sp_executesql @sql,N'@a int output',@ch_count output
update zy_detail_charge set charge_amount=@ch_count where patient_id=(select patient_id from zy_actpatient where inpatient_no=@pat_name )
fetch tb into @pat_name,@pat_id
end
close tb
deallocate tb
end
执行这个存储过程后
exec sp_B
再查询表
select * from zy_detail_charge
patient_id admiss_times ledger_sn charge_code charge_price charge_amount
---------- ------------ ----------- ----------- ------------ -------------
1 2 1 001 10 100
2 1 1 002 10 300
3 1 1 001 15 400
4 3 1 004 5 1000
5 2 1 005 7 200
6 1 1 008 8 200(所影响的行数为 6 行)
也就是将patient_id=1,2的数据合并,将patient_id=3,4,5的记录合并(我只是合并的charge_amount字段的值)
表zy_actpatient数据:
patient_id admiss_times inpatient_no name
---------- ------------ -------------------- ----------
1 2 aaaaaaB1 xx
2 1 aaaaaa xx
3 1 bbbbbbC1 zz
4 3 bbbbbb zz
5 2 bbbbbbD2 zz
6 1 ccccccF1 mm(所影响的行数为 6 行)
表zy_detail_charge数据:
patient_id admiss_times ledger_sn charge_code charge_price charge_amount
---------- ------------ ----------- ----------- ------------ -------------
1 2 1 001 10 100
2 1 1 002 10 200
3 1 1 001 15 400
4 3 1 004 5 400
5 2 1 005 7 200
6 1 1 008 8 200
ba_first_page6的表结构如下:
patient_id,admiss_times,inpatient_no,charge_item(与bill_item_code一样),charge_fee
create procedure sp_B1
as
begin
select a.patient_id as patient_id,left(a.inpatient_no,6) as inpatient_no into #t
from zy_actpatient a where
exists(select * from zy_actpatient where left(a.inpatient_no,6)=left(inpatient_no,6)
and a.patient_id<>patient_id)
select inpatient_no,max(patient_id) as patient_id1,min(patient_id) as patient_id2 into #tt
from #t group by inpatient_no
end
declare pt cursor
for
select a.inpatient_no,a.patient_id1,a.patient_id2 from #tt a,ba_first_page6 b
where b.inpatient_no=a.inpatient_no
declare @inpatient_no varchar(12),@p_id1 varchar,@p_id2 varchar(12)
open pt
fetch pt into @inpatient_no,@p_id1,@p_id2
while @@fetch_status=0
select @p_id2,1,@inpatient_no,max(b.name) bill_name,
sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee
into ba_first_page6
from view_zy_detail_charge_b a,
zy_bill_item b
where a.bill_item_code=b.code and
a.charge_status in('3','4') and
a.ledger_sn<>0 and
a.patient_id=@p_id1
group by b.name
close pt
deallocate pt
大家看看怎么解决这个插入语句select @p_id2,1,@inpatient_no,max(b.name) charge_item,
sum(isnull(a.charge_price,0)*a.charge_amount) charge_fee
into ba_first_page6
from view_zy_detail_charge_b a,
zy_bill_item b
where a.bill_item_code=b.code and
a.charge_status in('3','4') and
a.ledger_sn<>0 and
a.patient_id=@p_id1
group by b.name
,谢谢了,急