场景:Tab1里是一个班的成绩,成绩最高3分,最低1分,都是整数表结构如下Tab1
学号 语文 数学 英语
001 2 1 2
002 1 2 2
003 2 1 1Tab2
分数段
3
2
1想得到的查询结果科目 分数段 科目数(有多少科在这个分数段里)
语文 3 0
语文 2 2
语文 1 1
数学 3 0
数学 2 1
数学 1 2
英语 3 0
英语 2 2
英语 1 1
学号 语文 数学 英语
001 2 1 2
002 1 2 2
003 2 1 1Tab2
分数段
3
2
1想得到的查询结果科目 分数段 科目数(有多少科在这个分数段里)
语文 3 0
语文 2 2
语文 1 1
数学 3 0
数学 2 1
数学 1 2
英语 3 0
英语 2 2
英语 1 1
语文 文科
数学 理科
英语 文科最后的查询结果也要变一下科目 分数段 科目数(有多少科在这个分数段里) 类别
语文 3 0 文科
语文 2 2 文科
语文 1 1 文科
数学 3 0 理科
数学 2 1 理科
数学 1 2 理科
英语 3 0 文科
英语 2 2 文科
英语 1 1 文科
SELECT
A.科目, B.分数段, 人数 = COUNT(A.学号)
FROM(
SELECT 学号, 科目 = N'语文', 分数 = 语文 FROM Tab1
UNION ALL
SELECT 学号, 科目 = N'数学', 分数 = 数学 FROM Tab1
UNION ALL
SELECT 学号, 科目 = N'英语', 分数 = 英语 FROM Tab1
)A
RIGHT JOIN Tab2 B
ON A.分数段 = B.分数段
GROUP BY A.科目, B.分数段
insert into tab1 select '001' ,2 ,1, 2
union select '002' ,1 ,2, 2
union select '003' ,2 ,1, 1 create table Tab2 (分数段 int )
insert into tab2 select 3
union select 2
union select 1
select a.name as 科目,tab2.分数段,Count(学号) as 科目数
from
(
select name from syscolumns where object_id('tab1')=ID and name<>N'学号')a
cross join tab2 left outer join (
select 学号, 语文 as Score,N'语文' as Class from tab1
union
select 学号, 数学,N'数学' from tab1
union
select 学号, 英语,N'英语' from tab1)b
on a.name=b.class and tab2.分数段=b.Score
group by a.name,tab2.分数段
order by a.name,tab2.分数段 desc
--result
数学
3 0 数学
2 1 数学
1 2 英语
3 0 英语
2 2 英语
1 1 语文
3 0 语文
2 2 语文
1 1
declare @T1 table(学号 varchar(3),语文 int,数学 int,英语 int)
insert @T1
select '001',2,1,2 union all
select '002',1,2,2 union all
select '003',2,1,1
--原始数据:@T2
declare @T2 table(分数段 int)
insert @T2
select 3 union all
select 2 union all
select 1select b.科目,a.分数段, 科数目=sum(case when c.分数段 is null then 0 else 1 end) from @T2 a
cross join
(
select 科目='语文' union all
select 科目='数学' union all
select 科目='英语'
) b
left join
(
select 科目='语文',分数段=语文 from @T1 union all
select 科目='数学',分数段=数学 from @T1 union all
select 科目='英语',分数段=英语 from @T1
) c
on a.分数段=c.分数段 and b.科目=c.科目
group by b.科目,a.分数段
order by b.科目,a.分数段 desc/*
科目 分数段 科目数
数学 3 0
数学 2 1
数学 1 2
英语 3 0
英语 2 2
英语 1 1
语文 3 0
语文 2 2
语文 1 1
*/
寒泉
的也正解。
union all
select 科目= '数学',分数段= b.分数段 ,科目数=sum(case when a.数学= b.分数段 then 1 else 0 end) from #tab1 a,#tab2 b group by 分数段
union all
select 科目= '英语',分数段= b.分数段 ,科目数=sum(case when a.英语= b.分数段 then 1 else 0 end) from #tab1 a,#tab2 b group by 分数段