日期:2014-05-18  浏览次数:20586 次

求一较难的SQL语句
场景:Tab1里是一个班的成绩,成绩最高3分,最低1分,都是整数

表结构如下

Tab1
学号 语文 数学 英语
001 2 1 2
002 1 2 2
003 2 1 1

Tab2
分数段
 3
 2
 1

想得到的查询结果

科目 分数段 科目数(有多少科在这个分数段里)
语文 3 0
语文 2 2
语文 1 1
数学 3 0
数学 2 1
数学 1 2
英语 3 0
英语 2 2
英语 1 1

------解决方案--------------------
看结果,应该是人数吧?

SQL code

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.分数段

------解决方案--------------------
create table Tab1 (学号 nvarchar(10), 语文 int , 数学 int , 英语 int )
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
------解决方案--------------------
SQL code
--原始数据:@T1
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 1

select 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
*/