求sql语句,谢谢,在线等(想了半天没结果)

[nk912114]

有两表

表Drug(药物表)

DrugName DrugCode
西药费 a
中药费 b
草药费 c

表DrugFare(药物费用表)

PersonName DrugFare DrugCode
张三 50.0 a
张三 100.0 b
李四 60.0 a

现在要的结果如下:

PersonName 西药费 中药费 草药费
张三 50.0 100.0 0.0
李四 60.0 0.0 0.0

如果表DrugFare中的人相关的药费没记录,则为0.0

请问这sql语句怎么写?谢谢!

[xingxingy0326]

横式表的查询吗？但以前的贴子吧，有很多的

[zjcxc]

declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(rtrim(DrugName))+'=isnull(sum(case DrugCode when '
+quotename(rtrim(DrugCode),'''')+' then DrugFare end),0)'
from Drug
exec('select PersonName'+@s+' from DrugFare group by PersonName')

[zheninchangjiang]

select personname,
[西药费]=sum(case when drugcode='a' then drugfare else 0 end),
[中药费]=sum(case when drugcode='b' then drugfare else 0 end),
[草药费]=sum(case when drugcode='c' then drugfare else 0 end)
from drugfare

[631799]

select PersonName
,西药费=max(case DrugName when '西药费' then DrugFare end)
,中药费=max(case DrugName when '中药费' then DrugFare end)
,草药费=max(case DrugNamec when '草药费' then DrugFare end)
from (select * from drugfare,drug where drugfare.drugcode=drug.drugcode)
group by PersonName

[Softlee81307]

declare @s varchar(4000)
set @s=''
select @s=@s+','+drugName+'=(select sum(drugFare) from DrugFare where DrugCode='''+drugCode+''' And PersonName=a.PersonName )' from Drug

set @s='select PersonName '+@s+' from DrugFare a Group by PersonName '
exec(@s)

[LIHY70]

select distinct PersonName,
[西药费]=(select b.DrugFare from DrugFare b,Drug c where b.PersonName=a.PersonName and b.DrugCode=c.DrugCode and c.DrugName='西药费'),
[中药费]=(select b.DrugFare from DrugFare b,Drug c where b.PersonName=a.PersonName and b.DrugCode=c.DrugCode and c.DrugName='中药费'),
[草药费]=(select b.DrugFare from DrugFare b,Drug c where b.PersonName=a.PersonName and b.DrugCode=c.DrugCode and c.DrugName='草药费') from DrugFare a

[LIHY70]

select distinct PersonName,
[西药费]=(select b.DrugFare from DrugFare b,Drug c where b.PersonName=a.PersonName and b.DrugCode=c.DrugCode and c.DrugName='西药费'),
[中药费]=(select b.DrugFare from DrugFare b,Drug c where b.PersonName=a.PersonName and b.DrugCode=c.DrugCode and c.DrugName='中药费'),
[草药费]=(select b.DrugFare from DrugFare b,Drug c where b.PersonName=a.PersonName and b.DrugCode=c.DrugCode and c.DrugName='草药费') from DrugFare a

[zjcxc]

--测试数据
create table Drug(DrugName nvarchar(10),DrugCode nvarchar(10))
insert Drug select '西药费','a'
union all select '中药费','b'
union all select '草药费','c'

create table DrugFare(PersonName nvarchar(10),DrugFare decimal(10,1),DrugCode nvarchar(10))
insert DrugFare select '张三',50.0, 'a'
union all select '张三',100.0,'b'
union all select '李四',60.0 ,'a'
go

--查询
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(rtrim(DrugName))+'=isnull(sum(case DrugCode when '
+quotename(rtrim(DrugCode),'''')+' then DrugFare end),0)'
from Drug
exec('select PersonName'+@s+' from DrugFare group by PersonName')
go

--删除测试
drop table Drug,DrugFare

/*--结果

PersonName 西药费 中药费 草药费
---------- ---------- -------- ---------
李四 60.0 .0 .0
张三 50.0 100.0 .0
--*/

[631799]

少了个别名：
select PersonName
,西药费=max(case DrugName when '西药费' then DrugFare end)
,中药费=max(case DrugName when '中药费' then DrugFare end)
,草药费=max(case DrugName when '草药费' then DrugFare end)
from (select a.*,b.drugname from drugfare a,drug b where a.drugcode=b.drugcode) aa
group by PersonName

[rfq]

select personname,
isnull(sum (case drugcode when 'a' then drugFare end ),0) as 西药,
isnull(sum (case drugcode when 'b' then drugFare end ),0) as 中药,
isnull(sum (case drugcode when 'c' then drugFare end ),0) as 草药
from drugfare

group by personname

[nk912114]

谢谢各位!
已经解决
呵呵,都是高手呀

[nk912114]

已结贴,给分
怎么没显示出来得分呢?