SQL语句 (300分)

数据库是SQL Server 2000
History表：
id Personid Action Date
--- -------- ------ ----
1 5 a 2002-1-1
2 6 a ...
3 6 b ...
4 5 a ...
5 4 b ...
Person表：
Personid PersonName PersonType
1 1 1
2 2 2
3 3 3
4 4 1
5 5 2
6 6 3

要求特定时间段内找出Person表中PersonType = x的Person在history表中的各项Action的计数
返回信息应为;
PersonName_A Action_A Person_A_Action_A_Count
PersonName_A Action_B Person_A_Action_B_Count
PersonName_A Action_C Person_A_Action_C_Count
PersonName_B Action_A Person_B_Action_A_Count
PersonName_B Action_B Person_B_Action_B_Count
PersonName_C Action_B Person_C_Action_B_Count

set @runs=@runs +' select dz,count(*) as ts,sum(gsl) as tgsl,sum(gzl) as tgzl ,snr into #temp from #kjb group by dz,snr
union
(select dz ,count(*) as ts,sum(gsl) as tgsl,sum(gzl) as tgzl ,'+''''+'合计'+''''+' as snr from #kjb group by dz)
union
(select '+''''+'全部'+''''+' as dz ,count(*) as ts,sum(gsl) as tgsl,sum(gzl) as tgzl , snr from #kjb group by snr)
union
(select '+''''+'全部'+''''+' as dz ,count(*) as ts,sum(gsl) as tgsl,sum(gzl) as tgzl ,'+''''+'合计'+''''+' as snr from #kjb ) '
set @runs=@runs+' select * from #temp order by dz,snr '
exec (@runs)
你自己Group by person,action

SELECT Person.PersonName,History.Action,COUNT(*)
WHERE Person.Personid=History.Personid
GROUP BY Person.PersonName,History.Action

OK ?

SELECT A.PersonName,B.Action,Count(*)
From Person A,History B
WHERE A.Personid=B.Personid and PersonType=x

樓上的沒有考慮為0的情況
select Person.PersonName,History.Action,Count(History.Action)
From Person Left Join History on Person.PersonID=History.PersonID
where Person.PersonType= x and History.Date Between Date1 and Date2
Group By Person.PersonName,History.Action

多人接受答案了。