一条sql语句统计多个结果问题

一张表：
学校    学生姓名   语文   数学   物理   化学  英语
a学校     某某某1   68     78        99      41     100
a学校    某某某2    89     87       12       100   87
b学校    某某某3    89     87       12       100   87
b学校    某某某4    89     87       12       100   87
如何用一条SQL语句统计出这个样子：
学校   语文人均分   语文及格率  语文优生率   数学人均分   数学及格率  数学优生率    物理人均分   物理及格率  物理优生率
a学校
b学校
万分感谢！！！！！！！！！！！！！！！！

8 个解决方案

#1

其他的还要写吗？

----------------------------------------------------------------

-- Author  :DBA_HuangZJ(发粪涂墙)

-- Date    :2014-03-12 14:29:22

-- Version:

--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 

--	Apr  2 2010 15:48:46 

--	Copyright (c) Microsoft Corporation

--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

--

----------------------------------------------------------------

--> 测试数据[huang]

if object_id('[huang]') is not null drop table [huang]

go 

create table [huang]([学校] nvarchar(6),[学生姓名] nvarchar(8),[语文] int,[数学] int,[物理] int,[化学] int,[英语] int)

insert [huang]

select N'a学校',N'某某某1',68,78,99,41,100 union all

select N'a学校',N'某某某2',89,87,12,100,87 union all

select N'b学校',N'某某某3',89,87,12,100,87 union all

select N'b学校',N'某某某4',89,87,12,100,87

--------------生成数据--------------------------


select [学校],SUM([语文])/(SELECT COUNT([学生姓名]) FROM HUANG A WHERE HUANG.[学校]=A.[学校] GROUP BY [学校]  )语文人均分

from [huang]

GROUP BY [学校]

----------------结果----------------------------

/* 

学校     语文人均分

------ -----------

a学校    78

b学校    89

*/

#2

select

    学校,

    avg(语文) as 语文人均分,

    ltrim(sum(case when 语文>=60 then 1 else 0 end)*100.0/count(1) )+'%' as 语文及格率,

    ltrim(sum(case when 语文>=80 then 1 else 0 end)*100.0/count(1) )+'%' as 语文优分率,

...

from 

    tb

group by

      学校

#3

只写了语文，其他的你自己补

----------------------------------------------------------------

-- Author  :DBA_HuangZJ(发粪涂墙)

-- Date    :2014-03-12 14:29:22

-- Version:

--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 

--	Apr  2 2010 15:48:46 

--	Copyright (c) Microsoft Corporation

--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

--

----------------------------------------------------------------

--> 测试数据[huang]

if object_id('[huang]') is not null drop table [huang]

go 

create table [huang]([学校] nvarchar(6),[学生姓名] nvarchar(8),[语文] int,[数学] int,[物理] int,[化学] int,[英语] int)

insert [huang]

select N'a学校',N'某某某1',68,78,99,41,100 union all

select N'a学校',N'某某某2',89,87,12,100,87 union all

select N'b学校',N'某某某3',89,87,12,100,87 union all

select N'b学校',N'某某某4',89,87,12,100,87

--------------生成数据--------------------------


select [学校],SUM([语文])/(SELECT COUNT([学生姓名]) FROM HUANG A WHERE HUANG.[学校]=A.[学校] GROUP BY [学校]  )语文人均分,

COUNT(CASE WHEN [语文]>=60 THEN 1 ELSE null END )/(SELECT COUNT([学生姓名]) FROM HUANG A WHERE HUANG.[学校]=A.[学校] GROUP BY [学校]  )*100 语文及格率,

COUNT(CASE WHEN [语文]>=90 THEN 1 ELSE null END )/(SELECT COUNT([学生姓名]) FROM HUANG A WHERE HUANG.[学校]=A.[学校] GROUP BY [学校]  )*100 语文优生率--如果80才算优就把前面的90改为80

from [huang]

GROUP BY [学校]

----------------结果----------------------------

/* 

学校     语文人均分       语文及格率       语文优生率

------ ----------- ----------- -----------

a学校    78          100         0

b学校    89          100         0

*/

#4

我写一个语文的吧，没测试
select 学校,avg(语文) 语文人均分,(select count(学生姓名) from T a where a.学校＝T.学校 and a.语文>=60)/count(学生姓名) 语文及格率,
(select count(学生姓名) from T a where a.学校＝T.学校 and a.语文>=90)/count(学生姓名) 语文优生率
from T group by 学校

#5

靠，版主们不能这样抢答，留点给我们小裤衩升升级

#6




create table test (学校 nvarchar(10),学生姓名 nvarchar(10),语文 int,

					数学 int,物理 int,化学 int,英语 int)

insert into test 

select 'a学校','某某某1',68,78,99,41,100 union all

select 'a学校','某某某2',89,87,12,100,87 union all   

select 'b学校','某某某3',89,87,12,100,87 union all   

select 'b学校','某某某4',89,87,12,100,87  


select 学校,

		语文人均分=cast(SUM(语文) as float)/COUNT(学生姓名),

		语文及格率=convert(varchar,cast(SUM(case when 语文>59 then 1 else 0 end)as float)/COUNT(学生姓名)*100)+'%',

		语文优生率=convert(varchar,cast(SUM(case when 语文>89 then 1 else 0 end) as float)/COUNT(学生姓名)*100)+'%',

		数学人均分=cast(SUM(数学) as float)/COUNT(学生姓名),

		数学及格率=convert(varchar,cast(SUM(case when 数学>59 then 1 else 0 end) as float)/COUNT(学生姓名)*100)+'%',

		数学优生率=convert(varchar,cast(SUM(case when 数学>89 then 1 else 0 end) as float)/COUNT(学生姓名)*100)+'%',

		物理人均分=cast(SUM(物理) as float)/COUNT(学生姓名),

		物理及格率=convert(varchar,cast(SUM(case when 物理>59 then 1 else 0 end) as float)/COUNT(学生姓名)*100)+'%',  

		物理优生率=convert(varchar,cast(SUM(case when 物理>89 then 1 else 0 end) as float)/COUNT(学生姓名)*100)+'%'

from test 

group by 学校

/*

a学校	78.5	100%	0%	82.5	100%	0%	55.5	50%	50%

b学校	89	100%	0%	87	100%	0%	12	0%	0%

*/

#7

我在c#，winform环境下：
string strsql="select bmddm,";
                strsql+="语文人均分=cast(SUM(ywa) as float)/COUNT(bmh),";
                strsql+="语文及格率=convert(varchar,cast(SUM(case when ywa>59 then 1 else 0 end)as float)/COUNT(bmh)*100),";
                strsql+="语文优生率=convert(varchar,cast(SUM(case when ywa>89 then 1 else 0 end) as float)/COUNT(bmh)*100)";
                strsql+=" from [成绩库$] group by bmddm";
这个执行报错，我是直接访问的Excel

#8

用iif解决了，谢谢各位热心的大侠

#1