select classid 班级,substring(timecode,2,1) 课时,
max(case left(timecode,1) when 'A' then subjectid end) [星期1],
max(case left(timecode,1) when 'B' then subjectid end) [星期2],
max(case left(timecode,1) when 'C' then subjectid end) [星期3],
max(case left(timecode,1) when 'D' then subjectid end) [星期4],
max(case left(timecode,1) when 'E' then subjectid end) [星期5],
max(case left(timecode,1) when 'F' then subjectid end) [星期6],
max(case left(timecode,1) when 'G' then subjectid end) [星期7]
from course group by classid,substring(timecode,2,1)
--每天有多少节课的信息
declare @tb table(id varchar(1),name varchar(10))
insert into @tb
select 1,'第一,二节'
union all select 2,'第三,四节'
union all select 2,'第五,六节'
--查询课程表
select classid 班级,课时=b.name,
max(case left(timecode,1) when 'A' then subjectid end) [星期1],
max(case left(timecode,1) when 'B' then subjectid end) [星期2],
max(case left(timecode,1) when 'C' then subjectid end) [星期3],
max(case left(timecode,1) when 'D' then subjectid end) [星期4],
max(case left(timecode,1) when 'E' then subjectid end) [星期5],
max(case left(timecode,1) when 'F' then subjectid end) [星期6],
max(case left(timecode,1) when 'G' then subjectid end) [星期7]
from course a right join @tb b on substring(a.timecode,2,1)=b.id
group by a.classid,substring(a.timecode,2,1)
select classid 班级,substring(timecode,2,1) 课时,
max(case left(timecode,1) when 'A' then subjectid end) [星期1],
max(case left(timecode,1) when 'B' then subjectid end) [星期2],
max(case left(timecode,1) when 'C' then subjectid end) [星期3],
max(case left(timecode,1) when 'D' then subjectid end) [星期4],
max(case left(timecode,1) when 'E' then subjectid end) [星期5],
max(case left(timecode,1) when 'F' then subjectid end) [星期6],
max(case left(timecode,1) when 'G' then subjectid end) [星期7]
from #course group by classid,substring(timecode,2,1)
go
drop table #course
select classid 班级,subjectid 科目,
(case substring(timecode,1,1)
when 'a' then '星期1'
when 'b' then '星期2'
when 'c' then '星期3'
when 'd' then '星期4'
when 'e' then '星期5'
when 'f' then '星期6'
else '星期7' end) 星期,
(case substring(timecode,2,1)
when '1' then '第一节'
when '2' then '第二节'
else '第三节' end) 节次,
substring(timecode,3,1) 周次
from course
select classid 班级,subjectid 科目,case(substring(timecode,1,1) when 'a' then '星期1' when 'b' then '星期2' when 'c' then '星期3' when 'd' then '星期4' when 'e' then '星期5' when 'f' then '星期6' else '星期7') 星期,case(substring(timecode,2,1) when '1' then '第一节' when '2' then '第二节' ) 节次,substring(timecode,3,1) 周次 from course
select '1-2' as 节,max(case substring(timecode,1,1)
when 'A' then 科目 end) as 星期1,
max(case substring(timecode,1,2)
when 'B' then 科目 end) as 星期2,
...
from course
where classId=1
and timecode like '_1%'
union all
select '3-4' as 节,max(case substring(timecode,1,2)
when 'A' then 科目 end) as 星期1,
max(case substring(timecode,1,2)
when 'B' then 科目 end) as 星期2,
...
from course
where classId=1
and timecode like '_2%'
union all
....