一个求合格率的语句,该如何解决_Sql Server

一个求合格率的语句
表ST
id name PASS Mdate
1 张毛 1 2011-10-5
2 黎明 1 2011-10-5
3 张毛 0 2011-10-12
4 张毛 1 2011-11-6

我要得到的结果有名字合格率月份如下
姓名月份合格率
张毛 10 50%
黎明 10 100%
张毛 11 100%

------解决方案--------------------

SQL code

create table #tb(id int, name varchar(10), pass int, mdate date)insert into #tbselect 1, '张毛', 1, '2011-10-5'union allselect 2, '黎明', 1, '2011-10-5'union allselect 3, '张毛', 0, '2011-10-12'union allselect 4, '张毛', 1, '2011-11-6'goselect name,月份,合格率=convert(varchar(30),(SUM(pass)*1.0)/COUNT(*)*100 )+'%' from (select name,月份=MONTH(mdate),pass from #tb) a group by name,月份
------解决方案--------------------
SQL codedeclare @表ST table (id int,name varchar(4),PASS int,Mdate datetime)insert into @表STselect 1,'张毛',1,'2011-10-5' union allselect 2,'黎明',1,'2011-10-5' union allselect 3,'张毛',0,'2011-10-12' union allselect 4,'张毛',1,'2011-11-6'select 姓名=name,月份=month(mdate),合格率=ltrim(sum(pass)*100/count(1))+'%' from @表st group by name,month(mdate)/*姓名   月份          合格率---- ----------- -------------黎明   10          100%张毛   10          50%张毛   11          100%*/
------解决方案--------------------
探讨
SELECT NAME,MONTH(MDATE) AS 月分,
SUM(CASE WHEN PASS=1 THEN 1.00 ELSE 0 END)/COUNT(1) 
FROM TB GROUP BY NAME,CONVERT(CHAR(6),MDATE,112)

------解决方案--------------------
SQL codecreate table st(id int,name  varchar(20),pass int,mdate date)insert into STselect 1,'张毛',1,'2011-10-5' union allselect 2,'黎明',1,'2011-10-5' union allselect 3,'张毛',0,'2011-10-12' union allselect 4,'张毛',1,'2011-11-6'select name, DATEPART(mm,mdate) months ,cast(Round((cast(SUM(case when pass=1 then 1 else 0 end) AS float)/cast(COUNT(name) as float)),2)*100 as varchar)+'%' passPercentfrom stgroup by name,DATEPART(mm,mdate);