首页新闻找找看学习计划

sql面试题,有点复杂!!!!!!!!!!!!!!!!!!!!!!!!!!!

0
悬赏园豆:10 [待解决问题]

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

hslgmq的主页 hslgmq | 初学一级 | 园豆:45
提问于:2013-10-02 13:51
< >
分享
所有回答(6)
0
SELECT  class AS 班级 ,
        SUM(Amount) AS 考试分数 ,
        COUNT(DISTINCT UserName) AS 考试人数 ,
        COUNT(Name) AS 语文分数
FROM    dbo.[t-A]
GROUP BY Class ,
        Name
HAVING  Name = '语文'
秋壶冰月 | 园豆:5443 (大侠五级) | 2013-10-02 16:04

好像有点不对

支持(3) 反对(0) hslgmq | 园豆:45 (初学一级) | 2013-10-02 21:17
0
SELECT  A.class AS 班级 ,SUM(Amount) AS 总分 ,COUNT(DISTINCT UserName) AS 考试总人数 ,quantity AS 语文参考人数
FROM A left join (select class,count(username) AS quantity from A where name='语文' group by class) AS B
on A.class=B.class
group by A.class,quantity
order by A.class desc
苦B程序员 | 园豆:77 (初学一级) | 2013-10-03 09:06

支持(5) 反对(0) hslgmq | 园豆:45 (初学一级) | 2013-10-03 13:14
0

 赞同楼上

鹰击长空123 | 园豆:320 (菜鸟二级) | 2013-10-08 15:51
0

楼主要的不是语文总成绩  是语文参加考试的人数

mushishi | 园豆:230 (菜鸟二级) | 2013-11-22 12:25
0
if object_id('tempdb..#a') is not null
drop table  #a 
go
create table #a
(
  id  int ,
  Class varchar(20),
  UserName varchar(20),
  Name     varchar(20),
  Amount    int
)
insert into #a 
select 1 ,'一班','张三','数学',90 union all
select 2 ,'一班','张三','语文',89 union all
select 3 ,'一班','李四','数学',26 union all
select 4 ,'一班','李四','语文',31 union all
select 5 ,'二班','王五','数学',90 union all
select 6 ,'二班','王五','语文',80 union all
select 7 ,'二班','钟伟','数学',70  

 

select Class as 班级, sum(Amount)   as 总分,
 count( DISTINCT UserName)   as 考试总人数,
 (select count(UserName) from #a b where b.Class=a.class and b.Name='语文' )as 语文参考人数
 from #a  a group by  class  order by 班级 desc

/*****************************/
班级   总分  考试总人数     语文参考人数
一班    236       2            2
二班    240        2            1
t101lian | 园豆:18 (初学一级) | 2013-12-18 15:24
0

select Class as 班级, sum(Amount) as 总分,
count( DISTINCT UserName) as 考试总人数,
count(distinct(case when Name='语文' then UserName else null end)) as 语文考试人数
from tableName a
group by class
order by 班级 desc

空紫竹 | 园豆:204 (菜鸟二级) | 2013-12-31 12:51
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册