sql问题，全问大家了。

Question

create table score
(
schoolname varchar(20) ,
stuname varchar(20),
coursename varchar(10),
score int,
testname varchar(60),
termname varchar(10)
)insert into tscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','数学',94,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s1','语文',85,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',87,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','语文',82,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','语文',75,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','语文',76,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','数学',57,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',95,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','数学',94,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','数学',87,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','语文',63,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',88,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','语文',94,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','语文',87,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'
--------------------------------------结果样式------------------------------------
  学校      考试人数       全科合格人数       比率
金华四中       5                5             100%（全科合格人数/考试人数）
金华五中       5                4             80%
----------------------------------------------------------------------------------
各科合格分数都是60就成了

Answer 1

select schoolname,count(1) 考试人数,
sum(case when score>=60 then 1 else 0 end) 全科合格人数,
ltrim(sum(case when score>=60 then 1 else 0 end)*100/count(1))+'%' 比率
from score
group by schoolname
--
/*
schoolname           考试人数        全科合格人数      比率            
-------------------- ----------- ----------- ------------- 
金华四中                 10          10          100%
金华五中                 10          9           90%（所影响的行数为 2 行）
*/

Answer 2

select a.*,全科合格人数,ltrim(cast(全科合格人数*100.0/考试人数  as decimal(9,2)))+'%' as 比率
from (select schoolname,count(distinct stuname) as 考试人数   from  #score group by schoolname) a
left join (select schoolname,count(distinct stuname) as 全科合格人数 from #score  a 
where not exists(select 1 from #score where a.schoolname=schoolname and a.stuname=stuname and score<60)
group by schoolname
) b
on a.schoolname=b.schoolname
schoolname           考试人数        全科合格人数      比率
-------------------- ----------- ----------- ------------------------------------------
金华四中                 5           5           100.00%
金华五中                 5           4           80.00%(2 行受影响)

Answer 3

select 
a.schoolname as 学校,
count(distinct a.stuname) as 考试人数,
count(distinct a.stuname)-count(distinct b.stuname) as 全科合格人数,
ltrim(cast((count(distinct a.stuname)-count(distinct b.stuname))*100.0/count(distinct a.stuname) as dec(18,2)))+'%' as 比率
from tscore a
left join
(select * from tscore where score<60) b
on a.schoolname=b.schoolname and a.stuname=b.stuname
group by a.schoolname/**
学校                   考试人数        全科合格人数      比率
-------------------- ----------- ----------- ------------------------------------------
金华四中                 5           5           100.00%
金华五中                 5           4           80.00%
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)
**/

Answer 4

USE tempdb
GO
create table score
    (
    schoolname nvarchar(20) ,
    stuname nvarchar(20),
    coursename nvarchar(10),
    score int,
    testname nvarchar(60),
    termname nvarchar(10)
)insert into score
select N'金华四中',N's1',N'数学',99,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一' union
select N'金华四中',N's2',N'数学',98,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's3',N'数学',96,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's4',N'数学',94,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's5',N'数学',99,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's1',N'语文',85,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一' union
select N'金华四中',N's2',N'语文',87,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's3',N'语文',82,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's4',N'语文',75,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's5',N'语文',76,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w1',N'数学',57,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一' union
select N'金华五中',N'w2',N'数学',95,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w3',N'数学',94,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w4',N'数学',87,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w5',N'数学',98,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w1',N'语文',63,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一' union
select N'金华五中',N'w2',N'语文',88,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w3',N'语文',94,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w4',N'语文',87,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w5',N'语文',98,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'GO
SELECT 
schoolname AS 学校,
SUM(1) AS 考试人数,
SUM(con)AS 全科合格人数,
LTRIM(STR(SUM(con)*100./SUM(1),18,2))+'%' AS 比率
FROM 
(SELECT schoolname,stuname,CASE WHEN MIN(score)>=60 THEN 1 ELSE 0 END AS con 
FROM score
GROUP BY schoolname,stuname)t
GROUP BY schoolname
ORDER BY 1 desc--DROP TABLE score

Answer 5

select a.*,全科合格人数,ltrim(cast(全科合格人数*100.0/考试人数  as decimal(9,2)))+'%' as 比率
from (select schoolname,count(distinct stuname) as 考试人数   from  #score group by schoolname) a
left join (select schoolname,count(distinct stuname) as 全科合格人数 from #score  a 
            where not exists(select 1 from #score where a.schoolname=schoolname and a.stuname=stuname 
and((coursename ='语文' and score<60) or (coursename ='数学' and score<72)))
            group by schoolname
            ) b
on a.schoolname=b.schoolname

Answer 6

create table score
    (
    schoolname varchar(20) ,
    stuname varchar(20),
    coursename varchar(10),
    score int,
    testname varchar(60),
    termname varchar(10)
)insert into tscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','数学',94,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s1','语文',85,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',87,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','语文',82,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','语文',75,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','语文',76,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','数学',57,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',95,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','数学',94,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','数学',87,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','语文',63,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',88,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','语文',94,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','语文',87,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'
select a.*,全科合格人数,ltrim(cast(全科合格人数*100.0/考试人数 as decimal(9,2)))+'%' as 比率 from (select schoolname,count(distinct stuname) as 考试人数 from #score group by schoolname) a left join (select schoolname,count(distinct stuname) as 全科合格人数 from #score a where not exists(select 1 from #score where a.schoolname=schoolname and a.stuname=stuname and((coursename ='语文' and score<60) or (coursename ='数学' and score<72))) group by schoolname ) b on a.schoolname=b.schoolname

Answer 7

select schoolname,count( distinct stuname) 考试人数,
sum(case when score>=60 then 1 else 0 end) 全科合格人数,
ltrim(sum(case when score>=60 then 1 else 0 end)*100/count(1))+'%' 比率
from score
group by schoolname
--
/*
schoolname           考试人数        全科合格人数      比率            
-------------------- ----------- ----------- ------------- 
金华四中                 10          10          100%
金华五中                 10          9           90%（所影响的行数为 2 行）
*/

Answer 8

USE tempdb
GO
create table score
    (
    schoolname nvarchar(20) ,
    stuname nvarchar(20),
    coursename nvarchar(10),
    score int,
    testname nvarchar(60),
    termname nvarchar(10)
)insert into score
select N'金华四中',N's1',N'数学',99,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一' union
select N'金华四中',N's2',N'数学',98,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's3',N'数学',96,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's4',N'数学',94,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's5',N'数学',99,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's1',N'语文',85,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一' union
select N'金华四中',N's2',N'语文',87,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's3',N'语文',82,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's4',N'语文',75,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华四中',N's5',N'语文',76,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w1',N'数学',57,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一' union
select N'金华五中',N'w2',N'数学',95,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w3',N'数学',94,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w4',N'数学',87,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w5',N'数学',98,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w1',N'语文',63,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一' union
select N'金华五中',N'w2',N'语文',88,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w3',N'语文',94,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w4',N'语文',87,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'    union
select N'金华五中',N'w5',N'语文',71,N'2009年6月份婺城区初一年级各学校摸底考试',N'初一'
--改最后一條記錄為71，看效果
GO
SELECT 
    schoolname AS 学校,
    SUM(1) AS 考试人数,
    SUM(con)AS 全科合格人数,
    LTRIM(STR(SUM(con)*100./SUM(1),18,2))+'%' AS 比率
FROM 
(SELECT schoolname,stuname,
min(CASE WHEN (coursename=N'语文' AND score>=72) OR (coursename!=N'语文' AND score>=60) THEN 1  
ELSE 0 END) AS con 
FROM score
GROUP BY schoolname,stuname)t
GROUP BY schoolname
ORDER BY 1 desc学校 考试人数 全科合格人数 比率
金华四中 5 5 100.00%
金华五中 5 3 60.00%
--DROP TABLE score

Answer 9

select schoolname,count( distinct t.stuname) 考试人数,
(select count(distinct b.stuname) 
from score b
where b.schoolname=t.schoolname 
and   not exists (select 1 from score where  schoolname=b.schoolname and stuname=b.stuname and score<60 )
) 全科合格人数,
ltrim((select count(distinct b.stuname) 
from score b
where b.schoolname=t.schoolname 
and   not exists (select 1 from score where  schoolname=b.schoolname and stuname=b.stuname and score<60 )
)*100/count( distinct t.stuname) )+'%' 比率
from score t
group by schoolname
--
/*
schoolname           考试人数        全科合格人数      比率            
-------------------- ----------- ----------- ------------- 
金华四中                 5           5           100%
金华五中                 5           4           80%（所影响的行数为 2 行）
*/

Answer 10

Ok,Ok,Ok,谢谢大家。都太帅了,本人的分浪费没了,要不肯定多给点。

Answer 11

select schoolname,count(stuname),sum(case  when score>60 then 1 end),convert(varchar,sum(case  when score>60 then 1 end)*100/count(stuname))+'%' from score group by schoolname