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
....
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
....
classId 班级
subjectid 科目
timecode 时间代码(如A20) ABCDEFG表示星期1-7,2表示3-4节,0表示所有周
怎样写SQL语句可以排出课程表的样子
横行是星期1-7
竖行是节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
(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
insert #course values(1,91,'A10')
insert #course values(1,92,'A20')
insert #course values(1,93,'A30')
insert #course values(1,94,'A40')
insert #course values(1,91,'B10')
insert #course values(1,92,'B20')
insert #course values(1,93,'B30')
insert #course values(1,94,'B40')
insert #course values(1,91,'C10')
insert #course values(1,92,'C20')
insert #course values(1,93,'C30')
insert #course values(1,94,'C40')
insert #course values(1,91,'D10')
insert #course values(1,92,'D20')
insert #course values(1,93,'D30')
insert #course values(1,94,'D40')
insert #course values(1,91,'E10')
insert #course values(1,92,'E20')
insert #course values(1,93,'E30')
insert #course values(1,94,'E40')
insert #course values(1,91,'F10')
insert #course values(1,92,'F20')
insert #course values(1,93,'F30')
insert #course values(1,94,'F40')
insert #course values(1,91,'G10')
insert #course values(1,92,'G20')
insert #course values(1,93,'G30')
insert #course values(1,94,'G40')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
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)
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)
如果在星期三第3,4节课分单双周上不同的课,
你的SQL语句就只能找到一个课,应该怎样写才行呢?
请帮忙。