我有一表是这样:
select 费用,总金额,公费金额,自费金额 from table
我想实现如下效果!!表头:
费用,总金额,公费金额,自费金额,费用,总金额,公费金额,自费金额,费用,总金额,公费金额,自费金额
表头下面是明细:
aa 200 100 100 bb 200 100 100 cc 200 100 100
aa 200 100 100 bb 200 100 100 cc 200 100 100
aa 200 100 100 bb 200 100 100 cc 200 100 100
合计:600 300 300 合计:600 300 300 合计:600 300 300而且表头部分可以是分两列,或是三列如何实现????
select 费用,总金额,公费金额,自费金额 from table
我想实现如下效果!!表头:
费用,总金额,公费金额,自费金额,费用,总金额,公费金额,自费金额,费用,总金额,公费金额,自费金额
表头下面是明细:
aa 200 100 100 bb 200 100 100 cc 200 100 100
aa 200 100 100 bb 200 100 100 cc 200 100 100
aa 200 100 100 bb 200 100 100 cc 200 100 100
合计:600 300 300 合计:600 300 300 合计:600 300 300而且表头部分可以是分两列,或是三列如何实现????
解决方案 »
- mysql误删
- 如何查询指定时间段内的数据
- 一个感觉比较复杂的SQL,高分求救!!
- DISTINCT关键字被用作返回唯一的值 的问题
- 一个管理系统问题?急
- 关于时间分段显示的问题
- 我要把对方的Oracle数据库中的一些记录导出成文件然后再导入到我的数据库,因该什么格式比较好?
- 请问在MS SQL SERVER 7 中Query Analyzer怎样实现这样的功能:先对变量strsql赋值,
- 如何使用VB编程调用SQL SERVER中的系统存储过程?
- RID锁发生了,但是没搞懂什么原因.
- 我想在本机上设置sql邮件服务,可以绕开exchange server么?
- 怎样用sql语句设置某数据库的登陆帐号及密码?
id ID号
name 费用
我还有一个表:
ID --费用ID
sumfee --总金额
gfsumfee --公费金额
zfsumfe -- 自费金额
两个表用ID号关联
select T1.name,T.sumfee,T1.gfsumfee,T1.zfsumfe,T2.name,T2.sumfee,T2.gfsumfee,T2.zfsumfe
from
(select A.id,A.name,B.sumfee,B.gfsumfee,B.zfsumfe
from A inner join B on A.id=B.id and A.id%2=1) T1
left join
(select A.id,A.name,B.sumfee,B.gfsumfee,B.zfsumfe
from A inner join B on A.id=B.id and A.id%2=0) T2
on T1.id+1=T2.id
from
(select A.id,A.name,B.sumfee,B.gfsumfee,B.zfsumfe
from A inner join B on A.id=B.id and A.id%2=1) T1
left join
(select A.id,A.name,B.sumfee,B.gfsumfee,B.zfsumfe
from A inner join B on A.id=B.id and A.id%2=0) T2
on T1.id+1=T2.id
from
(select XXX from XX on A.id=B.id and (A.id+2)%3=0) T1
left join
(XX..............................and (A.id+1)%3=0) T2
on T1.id+1=T2.id
left join
(XX.............................and A.id%3=0) T3
on T1.id+2=T3.id
union all
select '合計',sum(T1.sumfee),sum(T1.gfsumfee),sum(T1.zfsumfe),'合計',sum(T2.sumfee),sum(T2.gfsumfee),sum(T2.zfsumfe)
from
(原來語句) Dorder by case when T1.name='合計' then 1 else 0 end
如:
它的ID是99,101,103就不对了
id code name
99 0001 挂号费
100 0002 中药费
101 0003 西药费
102 0004 注射费
103 0005 材料费
104 0006 处置费
105 0007 手术费
106 0008 化验费
107 0009 B超费
108 0010 乳透费
109 0011 X光费
110 0012 MP费
111 0013 水疗费第二个表的数据是这样
Id sumfee gfsumfee zfsumfee
99 200 100 100
101 200 100 100
104 200 100 100
我想要的结果是
挂号费 总金额,公费金额,自费金额 西药费 总金额,公费金额,自费金额
处置费 总金额,公费金额,自费金额 ... ... .... ...
.... .... ... ...
from
(select A.name,B.sumfee,B.gfsumfee,B.zfsumfe,B.tempID
from A inner join #B B on A.id=B.id and B.tempID%2=1) T1
left join
(select A.name,B.sumfee,B.gfsumfee,B.zfsumfe,B.tempID
from A inner join #B B on A.id=B.id and B.tempID%2=0) T2
on T1.tempID+1=T2.tempID
union all
select '合計',
sum(case when tempID%2=1 then sumfee else 0 end),
sum(case when tempID%2=1 then gfsumfee else 0 end),
sum(case when tempID%2=1 then zfsumfe else 0 end),
'合計'
sum(case when tempID%2=0 then sumfee else 0 end),
sum(case when tempID%2=0 then gfsumfee else 0 end),
sum(case when tempID%2=0 then zfsumfe else 0 end)
from #Border by case when T1.name='合計' then 1 else 0 end
select IDENTITY(int,1,1) as id,费用,总金额,公费金额,自费金额
into #tmep
from table1declare @colsnum int --要求的列倍数
set @colsnum=3declare @sql varchar(4000) --动态sql
declare @t_sql varchar(4000) --标题SQL
declare @b_sql varchar(4000) --表SQL
declare @tt_sql varchar(4000) --合计行标题sql
set @sql =''
set @t_sql =''
set @b_sql =''
set @tt_sql =''declare @i int
set @i=1
while @i<=@colsnum
begin
if @i=@colsnum
begin
set @t_sql =@t_sql+
'X'+cast(@i as varchar(20))+'.费用,'+
'X'+cast(@i as varchar(20))+'.总金额,'+
'X'+cast(@i as varchar(20))+'.公费金额,'+
'X'+cast(@i as varchar(20))+'.自费金额'
set @tt_sql=@tt_sql+
'费用=''合计'','+
'总金额=sum(X'+cast(@i as varchar(20))+'.总金额),'+
'公费金额=sum(X'+cast(@i as varchar(20))+'.公费金额),'+
'自费金额=sum(X'+cast(@i as varchar(20))+'.自费金额)'
if @colsnum =1
set @b_sql=@b_sql+
' ( select line_id=id/'+cast(@i as varchar(20))+', * from #temp where id%'+cast(@i as varchar(20)) +'=0 ) X'+cast(@i as varchar(20))
else
set @b_sql=@b_sql+
' ( select line_id=id/'+cast(@i as varchar(20))+',* from #temp where id%'+cast(@i as varchar(20)) +'=0 ) X'+cast(@i as varchar(20))+
' on X1.line_id =X'+cast(@i as varchar(20))
end
else
begin
set @t_sql =@t_sql+
'X'+cast(@i as varchar(20))+'.费用,'+
'X'+cast(@i as varchar(20))+'.总金额,'+
'X'+cast(@i as varchar(20))+'.公费金额,'+
'X'+cast(@i as varchar(20))+'.自费金额,'
set @tt_sql=@tt_sql+
'费用=''合计'','+
'总金额=sum(X'+cast(@i as varchar(20))+'.总金额),'+
'公费金额=sum(X'+cast(@i as varchar(20))+'.公费金额),'+
'自费金额=sum(X'+cast(@i as varchar(20))+'.自费金额),'
if @i=1
set @b_sql=@b_sql+
' ( select line_id=id/'+cast(@i as varchar(20))+',* from #temp where id%'+cast(@i as varchar(20)) +'=0 ) X'+cast(@i as varchar(20))+
' left join '
else
set @b_sql=@b_sql+
' ( select line_id=id/'+cast(@i as varchar(20))+',* from #temp where id%'+cast(@i as varchar(20)) +'=0 ) X'+cast(@i as varchar(20))+
' on X1.line_id=X'+cast(@i as varchar(20))+'.line_id left join '
end
set @i=@i+1
end
set @sql =' select '+@t_sql +' from '+@b_sql +
' union all '+
' select '+@tt_sql +' from '+@b_sql exec(@sql)
根据你的得到的结果是:
处置费 560.00 400.00 160.00 NULL NULL NULL NULL NULL NULL NULL NULL
挂号费 50.00 10.00 40.00 NULL NULL NULL NULL NULL NULL NULL NULL
化验费 200.00 10.00 190.00 NULL NULL NULL NULL NULL NULL NULL NULL
西药费 6000.00 30.00 5970.00 NULL NULL NULL NULL NULL NULL NULL NULL