6,129
社区成员
发帖
与我相关
我的任务
分享
create table tbscore
(
schoolname varchar(20) ,
stuname varchar(20),
coursename varchar(10),
score int,
testname varchar(60),
termname varchar(10)
)
insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'
--------------------------------------------------------------------------------------------
分数段 社会 语文 数学 科学 英语
0-29 0 0 0 0 0
30-39 0 1 1 0 0
40-49 0 1 1 0 0
50-59 0 0 0 0 0
60-69 0 0 0 0 0
70-79 0 0 0 0 0
80-89 0 0 0 0 0
90-99 0 8 8 0 0
100-109 0 0 0 0 0
110-119 0 0 0 0 0
120-129 0 0 0 0 0
130-139 以下全是0
140-149
150-159
160-169
170-179
180-189
190-200
合计 0 10 10 0 0
-------------------------------------------------------------
↑
笔算正确结果
每个分数段都是算所有学校的学生加在一起,高手帮忙。
--中间部分自己补上
select
'[0-29]'分数段,
sum(case when coursename=N'社会' and score between 0 and 29 then 1 else 0 end)社会,
sum(case when coursename=N'语文' and score between 0 and 29 then 1 else 0 end)语文,
sum(case when coursename=N'科学' and score between 0 and 29 then 1 else 0 end)科学,
sum(case when coursename=N'数学' and score between 0 and 29 then 1 else 0 end)数学,
sum(case when coursename=N'英语' and score between 0 and 29 then 1 else 0 end)英语
from tbscore
union all
select
'[30-39]'分数段,
sum(case when coursename=N'社会' and score between 30 and 39 then 1 else 0 end)社会,
sum(case when coursename=N'语文' and score between 30 and 39 then 1 else 0 end)语文,
sum(case when coursename=N'科学' and score between 30 and 39 then 1 else 0 end)科学,
sum(case when coursename=N'数学' and score between 30 and 39 then 1 else 0 end)数学,
sum(case when coursename=N'英语' and score between 30 and 39 then 1 else 0 end)英语
from tbscore
union all
select
'[40-49]'分数段,
sum(case when coursename=N'社会' and score between 40 and 49 then 1 else 0 end)社会,
sum(case when coursename=N'语文' and score between 40 and 49 then 1 else 0 end)语文,
sum(case when coursename=N'科学' and score between 40 and 49 then 1 else 0 end)科学,
sum(case when coursename=N'数学' and score between 40 and 49 then 1 else 0 end)数学,
sum(case when coursename=N'英语' and score between 40 and 49 then 1 else 0 end)英语
from tbscore
.........
union all
select
'[合计]'分数段,
sum(case when coursename=N'社会' then 1 else 0 end)社会,
sum(case when coursename=N'语文' then 1 else 0 end)语文,
sum(case when coursename=N'科学' then 1 else 0 end)科学,
sum(case when coursename=N'数学' then 1 else 0 end)数学,
sum(case when coursename=N'英语' then 1 else 0 end)英语
from tbscore
select
'[0-29]'分数段,
sum(case when coursename=N'社会' and score between 0 and 29 then 1 else 0 end)社会,
sum(case when coursename=N'语文' and score between 0 and 29 then 1 else 0 end)语文,
sum(case when coursename=N'科学' and score between 0 and 29 then 1 else 0 end)科学,
sum(case when coursename=N'数学' and score between 0 and 29 then 1 else 0 end)数学,
sum(case when coursename=N'英语' and score between 0 and 29 then 1 else 0 end)英语
from tbscore
union all
select
'[30-39]'分数段,
sum(case when coursename=N'社会' and score between 30 and 39 then 1 else 0 end)社会,
sum(case when coursename=N'语文' and score between 30 and 39 then 1 else 0 end)语文,
sum(case when coursename=N'科学' and score between 30 and 39 then 1 else 0 end)科学,
sum(case when coursename=N'数学' and score between 30 and 39 then 1 else 0 end)数学,
sum(case when coursename=N'英语' and score between 30 and 39 then 1 else 0 end)英语
from tbscore
union all
select
'[40-49]'分数段,
sum(case when coursename=N'社会' and score between 40 and 49 then 1 else 0 end)社会,
sum(case when coursename=N'语文' and score between 40 and 49 then 1 else 0 end)语文,
sum(case when coursename=N'科学' and score between 40 and 49 then 1 else 0 end)科学,
sum(case when coursename=N'数学' and score between 40 and 49 then 1 else 0 end)数学,
sum(case when coursename=N'英语' and score between 40 and 49 then 1 else 0 end)英语
from tbscore
/*
分数段 社会 语文 科学 数学 英语
------- ----------- ----------- ----------- ----------- -----------
[0-29] 0 0 0 0 0
[30-39] 0 1 0 1 0
[40-49] 0 1 0 1 0
(3 個資料列受到影響)
*/
select
[0-29]分数段,
sum(case when coursename='社会' and score between 0 and 29 then 1 else 0 end)社会,
sum(case when coursename='语文' and score between 0 and 29 then 1 else 0 end)语文,
sum(case when coursename='科学' and score between 0 and 29 then 1 else 0 end)科学,
sum(case when coursename='数学' and score between 0 and 29 then 1 else 0 end)数学,
sum(case when coursename='英语' and score between 0 and 29 then 1 else 0 end)英语
from tbscore
union all
select
[30-39]分数段,
sum(case when coursename='社会' and score between 30 and 39 then 1 else 0 end)社会,
sum(case when coursename='语文' and score between 30 and 39 then 1 else 0 end)语文,
sum(case when coursename='科学' and score between 30 and 39 then 1 else 0 end)科学,
sum(case when coursename='数学' and score between 30 and 39 then 1 else 0 end)数学,
sum(case when coursename='英语' and score between 30 and 39 then 1 else 0 end)英语
from tbscore
.....
select case when score/10<3 then 2 else score/10 end as Id,
case when coursename='社会' then score else 0 end as 社会,
case when coursename='语文' then score else 0 end as 语文,
case when coursename='数学' then score else 0 end as 数学,
case when coursename='科学' then score else 0 end as 科学,
case when coursename='英语' then score else 0 end as 英语
into #temp
from tbscore
with t1 as
(
select top 18 Id=(row_number() over(order by getdate()))+1 from tbscore
)
select case when a.Id=2 then '0~29'
when a.Id=19 then '190~200'
else ltrim(a.Id*10)+'~'+ltrim((a.Id+1)*10-1) end as fen,isnull(b.社会,0) as 社会,
isnull(c.语文,0) as 语文,isnull(d.数学,0) as 数学,isnull(e.科学,0) as 科学,isnull(f.英语,0) as 英语
from t1 a
left join (select Id,count(社会) as 社会 from #temp where isnull(社会,0)<>0 group by Id) b on a.Id=b.Id
left join (select Id,count(语文) as 语文 from #temp where isnull(语文,0)<>0 group by Id) c on a.Id=c.Id
left join (select Id,count(数学) as 数学 from #temp where isnull(数学,0)<>0 group by Id) d on a.Id=d.Id
left join (select Id,count(科学) as 科学 from #temp where isnull(科学,0)<>0 group by Id) e on a.Id=e.Id
left join (select Id,count(英语) as 英语 from #temp where isnull(英语,0)<>0 group by Id) f on a.Id=f.Id
/*结果
----------------------------------
fen 社会 语文 数学 科学 英语
0~29 0 0 0 0 0
30~39 0 1 1 0 0
40~49 0 1 1 0 0
50~59 0 0 0 0 0
60~69 0 0 0 0 0
70~79 0 0 0 0 0
80~89 0 0 0 0 0
90~99 0 8 8 0 0
100~109 0 0 0 0 0
110~119 0 0 0 0 0
120~129 0 0 0 0 0
130~139 0 0 0 0 0
140~149 0 0 0 0 0
150~159 0 0 0 0 0
160~169 0 0 0 0 0
170~179 0 0 0 0 0
180~189 0 0 0 0 0
190~200 0 0 0 0 0
*/
select score/10 as Id,
case when coursename='社会' then score else 0 end as 社会,
case when coursename='语文' then score else 0 end as 语文,
case when coursename='数学' then score else 0 end as 数学,
case when coursename='科学' then score else 0 end as 科学,
case when coursename='英语' then score else 0 end as 英语
into #temp
from tbscore
with t1 as
(
select Id=row_number()over(order by getdate()) from tbscore
)
select a.Id,ltrim(a.Id*10)+'~'+ltrim((a.Id+1)*10-1) as fen,isnull(b.社会,0) as 社会,
isnull(c.语文,0) as 语文,isnull(d.数学,0) as 数学,isnull(e.科学,0) as 科学,isnull(f.英语,0) as 英语
from t1 a
left join (select Id,count(社会) as 社会 from #temp where isnull(社会,0)<>0 group by Id) b on a.Id=b.Id
left join (select Id,count(语文) as 语文 from #temp where isnull(语文,0)<>0 group by Id) c on a.Id=c.Id
left join (select Id,count(数学) as 数学 from #temp where isnull(数学,0)<>0 group by Id) d on a.Id=d.Id
left join (select Id,count(科学) as 科学 from #temp where isnull(科学,0)<>0 group by Id) e on a.Id=e.Id
left join (select Id,count(英语) as 英语 from #temp where isnull(英语,0)<>0 group by Id) f on a.Id=f.Id