题目如下:
教室表tbl_ClassRoom
create table tbl_ClassRoom
(
[ID] tinyint identity(1,1) primary key, --系统编号
RoomName Varchar(50), --教室名称,101 、102
RoomAddress Varchar(50), --地址,如A栋1楼101室，B栋1楼101室
RoomCapacity Int --教室容量
)
时段表:tbl_Time
create table tbl_Time
(
[ID] tinyint identity(1,1) primary key, --系统编号
TimeName varchar(20), --时间段名称，如：11:00-12:00
BeginTime varchar(10), --开始时间，如：11:00
EndTime varchar(10) --结束时间，如：12:00
)
教室 - 时间段表tbl_Room_Time
create table tbl_Room_Time
(
[ID] bigint identity(1,1) primary key, --系统编号
RoomID varchar(30), --教室编号,关联tbl_Room.[ID]
RoomName Varchar(20), --教室名称 ----我认为冗余
[WeekDay] Varchar(10), --周几
TimeID tinyint, --时间段ID,关联tbl_Time.[ID]
TimeName Varchar(20), --时间段名称 --我认为冗余
IsUse bit --是否可用
)
写个sql显示上面这张图的效果
我的变态的解法:
思路:
从时段表中用游标取出各个字段,做为结果集中的字段名.生成动态SQL,然后再查询出结果集.
<主要是觉得麻烦,想找个更好的办法.>
create table tbl_ClassRoom
(
[ID] tinyint identity(1,1) primary key, --系统编号
RoomName Varchar(50), --教室名称,101 、
RoomAddress Varchar(50), --地址,如A栋楼室，B栋楼室
RoomCapacity Int --教室容量
)
go
insert into tbl_classRoom values ('Room1','address1',30)
insert into tbl_classRoom values ('Room2','address2',20)
insert into tbl_classRoom values ('Room3','address3',80)
go
create table tbl_Time
(
[ID] tinyint identity(1,1) primary key, --系统编号
TimeName varchar(20), --时间段名称，如：:00-12:00
BeginTime varchar(10), --开始时间，如：:00
EndTime varchar(10) --结束时间，如：:00
)
go
insert into tbl_time values ('10:00-11:00','10:00','11:00')
insert into tbl_time values ('10:00-11:00','11:00','12:00')
insert into tbl_time values ('10:00-11:00','12:00','13:00')
insert into tbl_time values ('10:00-11:00','13:00','14:00')
insert into tbl_time values ('10:00-11:00','14:00','15:00')
insert into tbl_time values ('10:00-11:00','15:00','16:00')
insert into tbl_time values ('10:00-11:00','16:00','17:00')
update tbl_Time set timeName=BeginTime+'-'+endTime
go
create table tbl_Room_Time
(
[ID] bigint identity(1,1) primary key, --系统编号
RoomID tinyint, --教室编号,关联tbl_Room.[ID] change Type varchar to tinyint
--RoomName Varchar(20), --教室名称----冗余,个人认为没有必要
[WeekDay] Varchar(10), --周几
TimeID tinyint, --时间段ID,关联tbl_Time.[ID]
--TimeName Varchar(20), --时间段名称--冗余,个人认为没有必要
IsUse bit --是否可用
)
go
alter table tbl_Room_Time add
constraint fk_RoomID foreign key (Roomid) references tbl_ClassRoom([ID]),
constraint fk_TimeID foreign key (TimeID) references tbl_Time([id])
go
--第11,12周安排
insert into tbl_Room_Time values (1,'11',7,0)
insert into tbl_Room_Time values (2,'11',7,1)
insert into tbl_Room_Time values (3,'11',7,0)
insert into tbl_Room_Time values (1,'11',1,0)
insert into tbl_Room_Time values (2,'11',1,0)
insert into tbl_Room_Time values (3,'11',1,0)
insert into tbl_Room_Time values (1,'11',2,1)
insert into tbl_Room_Time values (2,'11',2,0)
insert into tbl_Room_Time values (3,'11',2,0)
insert into tbl_Room_Time values (1,'11',3,1)
insert into tbl_Room_Time values (2,'11',3,0)
insert into tbl_Room_Time values (3,'11',3,1)
insert into tbl_Room_Time values (1,'11',4,1)
insert into tbl_Room_Time values (2,'11',4,1)
insert into tbl_Room_Time values (3,'11',4,1)
insert into tbl_Room_Time values (1,'11',5,1)
insert into tbl_Room_Time values (2,'11',5,0)
insert into tbl_Room_Time values (3,'11',5,0)
insert into tbl_Room_Time values (1,'11',6,1)
insert into tbl_Room_Time values (2,'11',6,1)
insert into tbl_Room_Time values (3,'11',6,1)
--第周安排
insert into tbl_Room_Time values (1,'12',5,1)
insert into tbl_Room_Time values (2,'12',5,0)
insert into tbl_Room_Time values (3,'12',5,1)
insert into tbl_Room_Time values (1,'12',4,0)
insert into tbl_Room_Time values (2,'12',4,1)
insert into tbl_Room_Time values (3,'12',4,0)
insert into tbl_Room_Time values (1,'12',3,1)
insert into tbl_Room_Time values (2,'12',3,1)
insert into tbl_Room_Time values (3,'12',3,1)
insert into tbl_Room_Time values (1,'12',2,1)
insert into tbl_Room_Time values (2,'12',2,0)
insert into tbl_Room_Time values (3,'12',2,0)
insert into tbl_Room_Time values (1,'12',1,0)
insert into tbl_Room_Time values (2,'12',1,1)
insert into tbl_Room_Time values (3,'12',1,0)
insert into tbl_Room_Time values (1,'12',6,0)
insert into tbl_Room_Time values (2,'12',6,0)
insert into tbl_Room_Time values (3,'12',6,1)
insert into tbl_Room_Time values (1,'12',7,1)
insert into tbl_Room_Time values (2,'12',7,0)
insert into tbl_Room_Time values (3,'12',7,1)
go
select * from tbl_ClassRoom
select * from tbl_Time order by beginTime
select * from tbl_Room_Time
go
create proc GetRoomTimeByWeekDay( @wkDay int =0)
as
begin
declare @sqlstr nvarchar(4000)
declare @TimeID nvarchar(4)
declare @TimeName varchar(80)
declare @head varchar(200)
declare @foot varchar(2000)
set @sqlstr=''
if (@wkDay=0)
begin
set @head='select roomName,[weekday], '
set @foot=' from (select a.weekday, a.roomid,a.timeid,a.isuse,b.id,c.roomName from tbl_Room_Time A,tbl_Time B,tbl_ClassRoom C
WHERE A.ROOMID=C.ID AND A.TIMEID=B.ID ) abc group by roomName,[WeekDay]'
end
else --按周分组
begin
set @head='select roomName,'
set @foot=' from (select a.roomid,a.timeid,a.isuse,b.id,c.roomName from tbl_Room_Time A,tbl_Time B,tbl_ClassRoom C
WHERE A.ROOMID=C.ID AND A.TIMEID=B.ID and weekday='+convert(varchar(4),@wkday)+' ) abc group by roomName'
end
DECLARE cur CURSOR
READ_ONLY
FOR select id,timeName from tbl_Time order by beginTime
open cur
FETCH NEXT FROM cur INTO @Timeid,@TimeName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @sqlstr=@sqlstr+''''+@timeName+''''+'=max(case when [email=timeid=]timeid='+@Timeid+'[/email] then convert(char(4),isuse) end),'
END
FETCH NEXT FROM cur INTO @Timeid,@TimeName
END
CLOSE cur
DEALLOCATE cur
set @sqlstr=@head+ left (@sqlstr,len(@sqlstr)-1)+@foot
exec (@sqlstr)
end
go
exec GetRoomTimebyWeekday 0
go
--第周的数据
exec GetRoomTimebyWeekday 11
--第周的数据
exec GetRoomTimebyWeekday 12
go
exec GetRoomTimebyWeekday 0
[
本帖最后由 gma 于 2008-7-1 10:57 编辑 ]
