declare @t table(id int,name varchar(10)) insert @t select 1,'西药' union all select 2,'中药' union all select 3,'治疗费' create table m(ks varchar(8),ym varchar(20),lbid int,rq datetime,je int) insert m select '外科','安神补心',1,'2005-1-1',12 union all select '外科','白芷',2,'2005-2-2',11 union all select '外科','胃肠减压',3,'2005-3-3',19 union all select '外科','健脑补肾丸',1,'2005-1-1',23 union all select '内科','安神补心',1,'2005-3-3',34 union all select '内科','白芷',2,'2005-9-2',45 union all select '内科','胃肠减压',3,'2005-1-1',55 union all select '内科','健脑补肾丸',1,'2005-2-1',65declare @sql varchar(8000) set @sql = '' declare @s datetime declare @e datetime set @s = '2001-01-01' set @e = '2009-01-01' select @sql = @sql + ',['+name+']=sum( case lbid when '+ convert(varchar(10),id) + ' then je else 0 end) ' from @t set @sql = 'select ks '+ @sql + ' from m '+ 'where datediff(d,rq,'''+convert(char(10),@s,120)+''') <=0 and datediff(d,rq,'''+convert(char(10),@e,120)+''') >=0 '+'group by ks'exec (@sql) drop table m/*ks 西药 中药 治疗费 -------- ----------- ----------- ----------- 内科 99 45 55 外科 35 11 19 */
declare @t table(id int,name varchar(10)) insert @t select 1,'西药' union all select 2,'中药' union all select 3,'治疗费' union all select 4,'化验费' create table m(ks varchar(8),ym varchar(20),lbid int,rq datetime,je int) insert m select '外科','安神补心',1,'2005-1-1',12 union all select '外科','白芷',2,'2005-2-2',11 union all select '外科','胃肠减压',3,'2005-3-3',19 union all select '外科','健脑补肾丸',1,'2005-1-1',23 union all select '内科','安神补心',1,'2005-3-3',34 union all select '内科','白芷',2,'2005-9-2',45 union all select '内科','胃肠减压',3,'2005-1-1',55 union all select '内科','健脑补肾丸',4,'2005-2-1',65declare @sql varchar(8000) set @sql = '' declare @s datetime declare @e datetime set @s = '2001-01-01' set @e = '2009-01-01' select @sql = @sql + ',['+name+']=sum( case lbid when '+ convert(varchar(10),id) + ' then je else 0 end) ' from @t set @sql = 'select ks '+ @sql + ' from m '+ 'where datediff(d,rq,'''+convert(char(10),@s,120)+''') <=0 and datediff(d,rq,'''+convert(char(10),@e,120)+''') >=0 '+'group by ks'exec (@sql) drop table m/* ks 西药 中药 治疗费 化验费 -------- ----------- ----------- ----------- ----------- 内科 34 45 55 65 外科 35 11 19 0 */
insert @t
select 1,'西药' union all
select 2,'中药' union all
select 3,'治疗费'
create table m(ks varchar(8),ym varchar(20),lbid int,rq datetime,je int)
insert m
select '外科','安神补心',1,'2005-1-1',12 union all
select '外科','白芷',2,'2005-2-2',11 union all
select '外科','胃肠减压',3,'2005-3-3',19 union all
select '外科','健脑补肾丸',1,'2005-1-1',23 union all
select '内科','安神补心',1,'2005-3-3',34 union all
select '内科','白芷',2,'2005-9-2',45 union all
select '内科','胃肠减压',3,'2005-1-1',55 union all
select '内科','健脑补肾丸',1,'2005-2-1',65declare @sql varchar(8000)
set @sql = ''
declare @s datetime
declare @e datetime
set @s = '2001-01-01'
set @e = '2009-01-01'
select @sql = @sql + ',['+name+']=sum( case lbid when '+ convert(varchar(10),id) +
' then je else 0 end) ' from @t
set @sql = 'select ks '+ @sql + ' from m '+ 'where datediff(d,rq,'''+convert(char(10),@s,120)+''') <=0 and datediff(d,rq,'''+convert(char(10),@e,120)+''') >=0 '+'group by ks'exec (@sql)
drop table m/*ks 西药 中药 治疗费
-------- ----------- ----------- -----------
内科 99 45 55
外科 35 11 19
*/
insert @t
select 1,'西药' union all
select 2,'中药' union all
select 3,'治疗费' union all
select 4,'化验费'
create table m(ks varchar(8),ym varchar(20),lbid int,rq datetime,je int)
insert m
select '外科','安神补心',1,'2005-1-1',12 union all
select '外科','白芷',2,'2005-2-2',11 union all
select '外科','胃肠减压',3,'2005-3-3',19 union all
select '外科','健脑补肾丸',1,'2005-1-1',23 union all
select '内科','安神补心',1,'2005-3-3',34 union all
select '内科','白芷',2,'2005-9-2',45 union all
select '内科','胃肠减压',3,'2005-1-1',55 union all
select '内科','健脑补肾丸',4,'2005-2-1',65declare @sql varchar(8000)
set @sql = ''
declare @s datetime
declare @e datetime
set @s = '2001-01-01'
set @e = '2009-01-01'
select @sql = @sql + ',['+name+']=sum( case lbid when '+ convert(varchar(10),id) +
' then je else 0 end) ' from @t
set @sql = 'select ks '+ @sql + ' from m '+ 'where datediff(d,rq,'''+convert(char(10),@s,120)+''') <=0 and datediff(d,rq,'''+convert(char(10),@e,120)+''') >=0 '+'group by ks'exec (@sql)
drop table m/*
ks 西药 中药 治疗费 化验费
-------- ----------- ----------- ----------- -----------
内科 34 45 55 65
外科 35 11 19 0
*/