sql语句查询问题

[a133152667]

表记录A有字段ID，CLASS,USERNAME,NAME,AMOUNT
A表记录
ID Class UserName Name Amount
1 一班 张三 数学 90
2 一班 张三 语文 89
3 一班 李四 数学 26
4 一班 李四 语文 31
5 二班 王五 数学 90
6 二班 王五 语文 80
7 二班 钟伟 数学 70


写出一个统计语句（mssql），统计一，二班的考试总人数，语文考试人数，总分

最终结果

班级 总分 考试总人数 语文参考人数
一班 236 2 2
二班 240 2 1

[兰色与白色]

补充下： create table grade( id int primary key, class varchar(20), UserName varchar(20), Name varchar(20), Score int ) insert into grade values('1','1','张三','数学','90') insert into grade values('2','1','张三','语文','89') insert into grade values('3','1','李四','数学','26') insert into grade values('4','1','李四','语文','31') insert into grade values('5','2','王五','数学','90') insert into grade values('6','2','王五','语文','80') insert into grade values('7','2','中卫','数学','70') select * from grade

[nali29]

select t1.class,sum(t1.amount),t2.usernum,count(t1.username)
from A as t1
join (select class,count( distinct username) as usernum from A group by class ) as t2 
on t1.class = t2.class
where t1.name = '语文'
group by t1.class,t2.usernum

[a133152667]

出来的总分不对... create table Stu_del ( id int, class varchar(50), username varchar(50), name varchar(50) ,amount int ) insert into Stu_del values(1,'一班','张三','数学',90) insert into Stu_del values(2,'一班','张三','语文',89) insert into Stu_del values(3,'一班','李四','数学',26) insert into Stu_del values(4,'一班','李四','语文',31) insert into Stu_del values(5,'二班','王五','语文',80) insert into Stu_del values(6,'二班','王五','数学',90) insert into Stu_del values(7,'二班','钟伟','数学',70)

[Andy__Huang]

create table Stu_del
 ( 
     id int,     class varchar(50),
      username varchar(50),
      name varchar(50)
      ,amount int
      )

insert into Stu_del values(1,'一班','张三','数学',90)
insert into Stu_del values(2,'一班','张三','语文',89)
insert into Stu_del values(3,'一班','李四','数学',26)
insert into Stu_del values(4,'一班','李四','语文',31)
insert into Stu_del values(5,'二班','王五','语文',80)
insert into Stu_del values(6,'二班','王五','数学',90)
insert into Stu_del values(7,'二班','钟伟','数学',70)


select class as 班级,sum(amount) as 总分,count(distinct username) as 考试总人数,
	sum(case when name='语文' then 1 else 0 end) as 语文参考人数
from Stu_del
group by class
drop table Stu_del

/*
班级     总分     考试总人数     语文参考人数
------------------------------------------
二班		240		2		1
一班		236		2		2
*/

[Landa_Jimmy]

create table grade(
id int primary key,
class varchar(20),
UserName	varchar(20),
Name	varchar(20),
Score int

)
insert into grade values('1','1班','张三','数学','90')
insert into grade values('2','1班','张三','语文','89')
insert into grade values('3','1班','李四','数学','26')
insert into grade values('4','1班','李四','语文','31')
insert into grade values('5','2班','王五','数学','90')
insert into grade values('6','2班','王五','语文','80')
insert into grade values('7','2班','中卫','数学','70')

select a.class,总分,考试总人数,语文参考人数 from (
select class,sum(Score)总分,count(1)考试总人数 from grade
group by class
)a
left join 
(
select class,count(*)语文参考人数 from grade where Name='语文'
group by class
)b on a.class=b.class
---------------------------------------------
class                总分          考试总人数       语文参考人数
-------------------- ----------- ----------- -----------
1班                   236         4           2
2班                   240         3           1

(2 row(s) affected)

[a133152667]

select class,count( distinct username) as 考试总人数 into #t2 from stu_del group by class select class,SUM(amount)as 总成绩 into #t1 from Stu_del group by class select class,COUNT(distinct username) as 语文参考人数 into #t3 from stu_del where name='语文' group by class select #t1.class,#t1.总成绩,#t2.考试总人数,#t3.语文参考人数 from #t1 left join #t2 on #t2.class=#t1.class left join #t3 on #t3.class=#t1.class 做出来 想问下还有没有别的做法?

[Landa_Jimmy]

create table grade(
id int primary key,
class varchar(20),
UserName	varchar(20),
Name	varchar(20),
Score int

)
insert into grade values('1','1班','张三','数学','90')
insert into grade values('2','1班','张三','语文','89')
insert into grade values('3','1班','李四','数学','26')
insert into grade values('4','1班','李四','语文','31')
insert into grade values('5','2班','王五','数学','90')
insert into grade values('6','2班','王五','语文','80')
insert into grade values('7','2班','中卫','数学','70')

select a.class,总分,考试总人数,语文参考人数 from (
select class,sum(Score)总分,count(distinct UserName)考试总人数 from grade
group by class
)a
left join 
(
select class,count(*)语文参考人数 from grade where Name='语文'
group by class
)b on a.class=b.class
----------------------------------------------------------------------
class                总分          考试总人数       语文参考人数
-------------------- ----------- ----------- -----------
1班                   236         2           2
2班                   240         2           1

(2 row(s) affected)

[發糞塗牆]

这个前天不是做过吗？

[LongRui888]

引用 6 楼 u011853202 的回复:

select class,count( distinct username) as 考试总人数 into #t2 from stu_del group by class select class,SUM(amount)as 总成绩 into #t1 from Stu_del group by class select class,COUNT(distinct username) as 语文参考人数 into #t3 from stu_del where name='语文' group by class select #t1.class,#t1.总成绩,#t2.考试总人数,#t3.语文参考人数 from #t1 left join #t2 on #t2.class=#t1.class left join #t3 on #t3.class=#t1.class 做出来 想问下还有没有别的做法?

[LongRui888]

引用 6 楼 u011853202 的回复:

select class,count( distinct username) as 考试总人数 into #t2 from stu_del group by class select class,SUM(amount)as 总成绩 into #t1 from Stu_del group by class select class,COUNT(distinct username) as 语文参考人数 into #t3 from stu_del where name='语文' group by class select #t1.class,#t1.总成绩,#t2.考试总人数,#t3.语文参考人数 from #t1 left join #t2 on #t2.class=#t1.class left join #t3 on #t3.class=#t1.class 做出来 想问下还有没有别的做法?

不用写这么复杂的，还是你的语句，经过合并简化：

--只是把你写的代码,写成了一个sql,进行了简化
select class,
       SUM(amount)as 总成绩,
       
       count(distinct username) as 考试总人数,
       
       count(case when name = '语文' then username else null end) as 语文参考人数 
from Stu_del
group by class
/*
class	总成绩	考试总人数	语文参考人数
二班		240		2			1
一班		236		2			2
*/