火车案例

注：第5题用游标，其它题不能使用游标，所有不能使用临时表
1.建表
列车(车次,起始站,终点站,最大运载量)
车站(车站名,建站日期，所属城市,最大吞吐量)
列车运行记录(编号,列车名,到达时间,到达站,上车人数,下车人数)
1.1 建表要有主外键
1.2 建站日期、到达时间为datetime类型
1.3 各种人数大于等于0,列车名、到达时间不能为空
2.插入数据
列车 (Z1,天津站,济南站,1000) (T8,北京站,天津站,1000) (T104,天津,上海,2000) (T5,济南,上海,2000)
车站 (北京站,’1988-3-21’,北京,30000)(北京南站,’2002-4-20’,北京,20000) (北京西站,’1990-6-20’,北京,30000) (上海站,’1990-6-20’,上海,25000)(天津站,’1990-6-20’,天津,10000) (济南站,’1990-6-20’,济南,10000) (南京站,’1990-6-20’,南京,20000)
运行记录(1,T104,5:03,天津站,700,0) (2,T104,8:03,济南站,200,200) (3,T104,11:03,南京站,300,200) (4,T104,15:10,上海站,0,800) (5,Z1,10:00,天津站,600,0) (6,Z1,15:00,济南站,0,600) (7,T8,9:00,北京站,800,0) (8,T8,9:20,天津站,0,800) (9,T5,7:30,济南站,2000,0) (10,T5,10:30,南京站,300,200) (11,T5,14:30,上海站,0,1900)
3.1哪个城市的车站最多
3.2停靠站数第二多的车次和它所停靠车站名
3.3实际运载人数最多的车次
4.增加一个车次T3，从北京直达上海，6：00出发，13：50到达，最大运载人数为1000人，实际乘坐400人
5.1假设Z1是从天津开往济南方向的列车，则返程车车次即为Z2，根据现有的列车表里的记录，补返程列车车次
用如下函数取出车次中的数字部分：convert(integer,substring(‘T3’,2,len(‘T3’)-1))
5.2列车到达终点后休息一个小时后，原路返程，假设速度不变，补全所有的运行记录
6.北京到上海最多换乘一次的所有乘车方案
1.建表
drop table s888Record
go
drop table s888Train
go
drop table s888Station
go
—列车(车次,起始站,终点站,最大运载量)
create table s888Train(Tname char(30),Bname char(30),Ename char(30),Mcarry integer,primary key(Tname),check (Mcarry >= 0))
go
—车站(车站名,建站日期，所属城市,最大吞吐量)
create table s888Station(Sname char(30),Cdate datetime,Bcity char(100),Mthruput integer,primary key(Sname),check(Mthruput >= 0))
go
—列车运行记录(编号,列车名,到达时间,到达站,上车人数,下车人数)
create table s888Record(no integer,Tname char(30) not null,Atime datetime not null,Sname char(30),Upamount integer,Offamount integer,primary key(no),
foreign key(Tname) references s888Train(Tname),foreign key(Sname) references s888Station(Sname),
check(Upamount >=0 and Offamount >= 0))
go

—

2.
insert into s888Train values (‘Z1’,’天津站’,’济南站’,1000)
go
insert into s888Train values (‘T8’,’北京站’,’天津站’,1000)
go
insert into s888Train values (‘T104’,’天津站’,’上海站’,2000)
go
insert into s888Train values (‘T5’,’济南站’,’上海站’,2000)
go
insert into s888Station values (‘北京站’,’1988-3-21’,’北京’,30000)
go
insert into s888Station values (‘北京南站’,’2002-4-20’,’北京’,20000)
go
insert into s888Station values (‘北京西站’,’1990-6-20’,’北京’,30000)
go
insert into s888Station values (‘上海站’,’1990-6-20’,’上海’,25000)
go
insert into s888Station values (‘天津站’,’1990-6-20’,’天津’,10000)
go
insert into s888Station values (‘济南站’,’1990-6-20’,’济南’,10000)
go
insert into s888Station values (‘南京站’,’1990-6-20’,’南京’,20000)
go
insert into s888Record values(1, ‘T104’,’5:03’,’天津站’,700,0)
go
insert into s888Record values(2, ‘T104’,’8:03’,’济南站’,200,200)
go
insert into s888Record values(3, ‘T104’,’11:03’,’南京站’,300,200)
go
insert into s888Record values(4, ‘T104’,’15:10’,’上海站’,0,800)
go
insert into s888Record values(5, ‘Z1’,’10:00’,’天津站’,600,0)
go
insert into s888Record values(6, ‘Z1’,’15:00’,’济南站’,0,600)
go
insert into s888Record values(7, ‘T8’,’9:00’,’北京站’,800,0)
go
insert into s888Record values(8, ‘T8’,’9:20’,’天津站’,0,800)
go
insert into s888Record values(9, ‘T5’,’7:30’,’济南站’,2000,0)
go
insert into s888Record values(10,’T5’,’10:30’,’南京站’,300,200)
go
insert into s888Record values(11,’T5’,’14:30’,’上海站’,0,1900)
go

—

3.1
select Bcity
from s888Station
group by Bcity
having count(Sname) >= all(select count(Sname)
from s888Station group by Bcity
)
go
—3.2
select Tname,Sname
from s888Record
where Tname = (
select top 1 Tname from (
select top 2 Tname,count(Sname) as ct
from s888Record
group by Tname
order by count(Sname) desc
) tbl
order by ct asc
)
go
—3.3
select Tname
from s888Record
group by Tname
having sum(Upamount) >= all(select sum(upamount)
from s888Record
group by Tname)
go

—

4.
insert into s888Train values (‘T3’,’北京站’,’上海站’,1000)
go
insert into s888Record values(12, ‘T3’,’6:00’,’北京站’,400,0)
go
insert into s888Record values(13, ‘T3’,’15:50’,’上海站’,0,400)
go

—

5.
declare @tname char(30)
declare @bname char(30)
declare @ename char(30)
declare @mcarry integer

declare @no integer
declare @atime datetime
declare @sname char(30)

declare @newname char(30)

declare @diff integer
declare @preold datetime —如何定义初始值
declare @prenew datetime
declare @newtime datetime

declare cur1 insensitive cursor for
select Tname,Bname,Ename,Mcarry
from s888Train
for read only

open cur1
while(0=0)
begin
fetch next from cur1 into @tname,@bname,@ename,@mcarry
if(@@fetch_status<>0) break
set @newname=substring(@tname,1,1) + convert(char(29),(convert(integer,substring(@tname,2,len(@tname)-1)) + 1))

insert into s888Train values(@newname,@ename,@bname,@mcarry)

set @preold = (select max(atime) from s888Record where tname=@tname)
set @prenew = dateadd(minute,60,@preold)

declare cur2 cursor for
select atime,sname
from s888Record
where Tname=@tname
order by atime desc
for read only

open cur2
while(0=0)
begin
fetch next from cur2 into @atime,@sname
if(@@fetch_status<>0) break

set @no = (select max(no) from s888Record)
set @no = @no+1

set @diff = datediff(minute,@atime,@preold)—得到两个站之间的时间分钟差
set @newtime=dateadd(minute,@diff,@prenew)

insert into s888Record values(@no, @newname,@newtime,@sname,0,0)

set @preold = @atime
set @prenew = @newtime

end
close cur2
deallocate cur2
end
close cur1
deallocate cur1

go

select * from s888Record where tname in(‘T3’,’T4’)
go

—

6.(考虑到’北京站’和’上海站’都可能为中途车站的情况)
— 直达的情况
select t1.Tname 车次,t6.sname 起始站,t6.Atime 发车时间,’直达’ 换乘,t7.sname 到达站,t7.atime 到过时间
from s888Record t1,s888Record t6,s888Record t7
where t1.Sname=’北京站’
and exists (
select 1 from s888Record t2 where t2.Tname = t1.Tname and t2.sname=’上海站’
)
and
(select no from s888Record t4 where t4.Tname = t1.Tname and t4.sname=’北京站’)
<
(select no from s888Record t5 where t5.Tname = t1.Tname and t5.sname=’上海站’)
and t1.Tname = t6.Tname
and t6.sname = ‘北京站’
and t1.Tname = t7.Tname
and t7.sname = ‘上海站’
—go
union
—换乘的情况

select tbl.Tname 车次,tbl.Bname 起始站,tbl.Atime 发车时间,rtrim(tbl.Extname)+’ 换乘 ‘+rtrim(t3.tname) 换乘,t7.sname 到达站,t7.atime 到过时间
from s888Record t3,(
—2.经过如下车站的所有车次
select t1.Tname,t1.Sname Bname,t1.Atime,t2.sname Extname—1.所有的经过北京的车次所经过的其它的车站：这个子查询是入口
from s888Record t1,s888Record t2
where t1.Sname=’北京站’
and t1.Tname = t2.Tname
and t2.sname<>’北京站’
and t2.sname<>’上海站’
and (select atime from s888record tbl1 where tbl1.Tname=t1.Tname and tbl1.sname=t1.sname)—定位从北京出发的火车，而不是返程车
<(select atime from s888record tbl2 where tbl2.Tname=t2.Tname and tbl2.sname=t2.sname)
) tbl,
s888record t7
where tbl.Extname = t3.sname
and exists(—3.判断这些车次有没有经过上海
select 1 from s888Record t4 where t3.Tname = t4.Tname and t4.sname = ‘上海站’
)
and (select atime from s888Record t5 where t5.sname = t3.sname and t5.tname=t3.tname) —定位从中途站去往上海的车次，而不是返程的车
< (select atime from s888Record t6 where t6.sname = ‘上海站’ and t6.tname=t3.tname)
and t3.tname = t7.tname
and t7.sname = ‘上海站’