我有三张表：
hospital表：hospitalid hospitalname
illType表： illtypeID illTypeName
patient表： id hospitalid illtypeID……
我想出报表统计每家医院中每个病种的人数，要是某家医院中没有某种病人，则人数为0。怎么写呢？多谢了
希望查出的结果集如下：
医院1 医院2。。。医院n （医院名可以是查询结果的别名）
病种1 3 2 4
病种2 2 8 0
病种n 6 8 1
------解决方案--------------------
- SQL code
create table hospital(hospitalid int,hospitalname varchar(10))create table illType(illtypeID int,illTypeName varchar(10))create table patient(id int, hospitalid int,illtypeID int)insert into hospital values(1 , '医院1')insert into hospital values(2 , '医院2')insert into illType values(1,'病种1')insert into illType values(2,'病种2')insert into illType values(3,'病种3')insert into patient values(1,1,1)insert into patient values(2,1,2)insert into patient values(3,1,3)insert into patient values(4,2,1)insert into patient values(5,2,2)godeclare @sql varchar(8000)set @sql = 'select i.illTypeName 'select @sql = @sql + ',sum(case h.hospitalname when ''' + hospitalname + ''' then 1 else 0 end) [' + hospitalname + ']'from (select h.hospitalname from hospital h) as aset @sql = @sql + ' from hospital h ,illType i , patient p where h.hospitalid = p.hospitalid and p.illtypeID = i.illtypeID group by i.illTypeName'exec(@sql) drop table hospital , illType,patient/*illTypeName 医院1 医院2 ----------- ----------- ----------- 病种1 1 1病种2 1 1病种3 1 0*/
------解决方案--------------------
- SQL code
create table hospital(hospitalid int,hospitalname nvarchar(10))insert into hospital select 1,'中心医院'insert into hospital select 2,'郊区医院'create table illType(illtypeID int,illTypeName nvarchar(10))insert into illType select 1,'疾病A'insert into illType select 2,'疾病B'insert into illType select 3,'疾病C'create table patient(id int,hospitalid int,illtypeID int)insert into patient select 1,1,2insert into patient select 2,1,3insert into patient select 3,1,2insert into patient select 4,2,1insert into patient select 5,2,2insert into patient select 6,2,2godeclare @str1 nvarchar(4000),@str2 nvarchar(4000) select @str1=ISNULL(@str1+', ','')+'['+ltrim(hospitalid)+']',@str2=ISNULL(@str2+', ','')+'['+LTRIM(hospitalid)+']['+hospitalname+']' from hospitalexec('select illTypeName as 疾病种类,[email protected]+' from (select f.illTypeName,b.hospitalid from patient b inner join illType f on f.illtypeID=b.illtypeID)apivot(count(hospitalid) for hospitalid in([email protected]+'))t')/*疾病种类 中心医院 郊区医院---------- ----------- -----------疾病A 0 1疾病B 2 2疾病C 1 0(3 行受影响)*/godrop table patient,illType,hospital