表结构
开始城市 结束城市 起飞日期 价格 折扣 更新时间
字段 ID begincity endcity begintime price discount updatetime 查询条件
开始城市 + 结束城市 +起飞日期 查询1 北京 上海 2011-12-20 00:00:00.000 700 7.5 2011-12-11 00:00:00.000
2 北京 上海 2011-12-21 00:00:00.000 800 9.5 2011-12-11 00:00:00.000
3 北京 上海 2011-12-21 00:00:00.000 700 9.5 2011-12-12 00:00:00.000
4 北京 上海 2011-12-22 00:00:00.000 600 8.5 2011-12-11 00:00:00.000查询最新的北京到上海
起飞日期为 2011-11-18 -- 2011-11-22
每日最低价的航班
select * from tb a
where convert(varchar(10),egintime,120) between '2011-11-18' and '2011-11-22' and
begincity='北京' and
endcity='上海' and
not exists(select 1 from tb where ID=a.ID and price*discount<a.price*a.discount)
and begintime between '2011-11-18' and '2011-11-22'
AND not exists (select 1 from TB where A.begincity=begincity AND A.endcity=endcity AND A.begintime=begintime AND A.price>price )
select a.id,a.begintime,b.sjprice from tb a inner join
(
select begintime, min( price* discount) as sjprice from tb
where convert(varchar(10), begintime,120) between '2011-11-18' and '2011-11-22' and
begincity='北京' and
endcity='上海'
group by begintime
) b
on a.begintime = b.begintime and (a.price* a.discount) = b.sjPrice and a.begincity='北京' and
a.endcity='上海'
select * from tb a
where convert(varchar(10),egintime,120) between '2011-11-18' and '2011-11-22' and
begincity='北京' and
endcity='上海' and
not exists(select 1 from tb where begintime=a.begintime and price*discount<a.price*a.discount)