看到的sql面试题
自己分析要拆开查询逻辑清楚点：
--先根据班级分组查到各班总分
Select class as N’班级’, sum(amount )as N’总分’ from T_a group by class
--查出总参考人数
Select count(*) as N’参考总人数’ from T_a group by username
--查出语文参考人数
Select count(*) as N’语文参考人数’ from T_a where Name=’语文’ group by username
--然后想办法把select组合起来
-----解决方案--------------------
create table #tb([ID] int,[Class] varchar(4),[UserName] varchar(4),[Name] varchar(4),[Amount] int)
insert into #tb
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 考试总人数,
SUM(case when Name='语文' then 1 else 0 end) as 语文参考人数
from #tb
group by class
drop table #tb
