一张表一列同时满足两个条件的查询(列车时刻查询)
--创建数据库
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