一张表一列同时满足两个条件的查询(列车时刻查询)

 --创建数据库

create database Train
on primary
(
 name='Train_data',
 filename='D:\练习\TrainDB\Train_data.mdf'
)
log on
(
 name='Train_log',
 filename='D:\练习\TrainDB\Train_log.ldf'
)
go
use Train
go
create table TrainInfo
(
 trainId int primary key identity(1,1) not null,--主键
 trainNum varchar(20) not null, --车次
 station varchar(20) not null, --车站
 startTime datetime not null, --到站时间
 trainPrice float not null  --票价 
)
go

insert into TrainInfo
values ('t339','北京','08:00',0)
insert into TrainInfo
values ('t339','天津西','08:45',30)
insert into TrainInfo
values ('t339','河北','10:20',60)
insert into TrainInfo
values ('t339','山东','12:00',80)

insert into TrainInfo
values ('k101','北京','08:00',0)
insert into TrainInfo
values ('k101','山东','12:00',70)
insert into TrainInfo
values ('k101','江苏','15:00',90)
insert into TrainInfo
values ('k101','河北','00:20',130)

select * from TrainInfo

 --从TrainInfo表查出:始发站北京,到站河北的所有车次(注:始发站和到站都在一列里)

--主要就是from TrainInfo t1,TrainInfo t2  虚拟t2这张表,具体代码如下

select '车次'=t1.trainNum,'始发站'=t1.station,'到站'=t2.station,'出发时间'=t1.startTime,'到站时间'=t2.startTime,'价格'=t2.trainPrice-t1.trainPrice
 from TrainInfo t1,TrainInfo t2 
where t1.station='北京' and t2.station='河北' and t1.trainNum=t2.trainNum

 

 

posted @ 2008-11-23 20:48  华仔2008  阅读(1040)  评论(1编辑  收藏  举报