select from,to,min(cost) as cost from 表一 group by from,to
是第一问答案
是第一问答案
解决方案 »
- 请教一个问题!
- 求助!有关T-SOL语句
- 〓百分〓求一SQL查询,有点难度~请大虾赐教>>>>>在线等>>>>>>>>>>>>>>>>>>>
- SQL SERVER 2000会话级别设置
- 如何写这样的存储过程?
- 请问修改机器名后要对SQL server做什么相应的处理才能让SQL继续跑啊
- 数据库操作忽然变得很慢
- 急啊!!!!!如何用SQl语句启动跟踪
- sql server7.0的数据向sql server2000转入问题
- FOXpro打印与临时表的冲突? VFP高手请进。。。
- 能否用SQL能否实现加权平均法的成本计算?
- 我用了TEXT类型来做为存字符,我需要将字段里面的字符再加上新增加的字符。要怎么办?
rom Flight a join(
select [from],[to],cost=min(cost)
from Flight
group by [from],[to]
)b on a.[from]=b.[from] and a.[to]=b.[to] and a.cost=b.cost
from Flight a join(
select [from],[to],cost=min(cost)
from Flight
group by [from],[to]
)b on a.[from]=b.[from] and a.[to]=b.[to] and a.cost=b.cost
select a.[From],b.[to],cost=min(a.cost+b.cost)
from Flight a join Flight b on a.[to]=b.[from]
where a.[From]<>b.[to]
group by a.[From],b.[to]
select [from],[to],cost=min(cost)
from(
select [from],[to],cost=min(cost)
from Flight
group by [from],[to]
union all
select a.[From],b.[to],cost=min(a.cost+b.cost)
from Flight a join Flight b on a.[to]=b.[from]
where a.[From]<>b.[to]
group by a.[From],b.[to]
)a group by [from],[to]
create table Flight([from] varchar(10),[to] varchar(10),cost int,airline varchar(10))
insert into Flight
select 'SF','Denver',300,'Frontier'
union all select 'SF','Denver',350,'United'
union all select 'Denver','SF',250,'United'
union all select 'Denver','SF',250,'Frontier'
union all select 'Denver','Chicago',250,'American'
union all select 'Chicago','NY',250,'Delta'
union all select 'Denver','NY',500,'American'
union all select 'Denver','NY',400,'TWA'
union all select 'SF','NY',750,'United'
go--(a)
select a.[From],a.[to],a.cost
from Flight a join(
select [from],[to],cost=min(cost)
from Flight
group by [from],[to]
)b on a.[from]=b.[from] and a.[to]=b.[to] and a.cost=b.cost
go--(b)
select a.[From],b.[to],cost=min(a.cost+b.cost)
from Flight a join Flight b on a.[to]=b.[from]
where a.[From]<>b.[to]
group by a.[From],b.[to]
go--(c)
select [from],[to],cost=min(cost)
from(
select [from],[to],cost=min(cost)
from Flight
group by [from],[to]
union all
select a.[From],b.[to],cost=min(a.cost+b.cost)
from Flight a join Flight b on a.[to]=b.[from]
where a.[From]<>b.[to]
group by a.[From],b.[to]
)a group by [from],[to]
go--删除测试环境
drop table Flight/*--测试结果
From to cost
---------- ---------- -----------
Chicago NY 250
Denver Chicago 250
Denver NY 400
Denver SF 250
Denver SF 250
SF Denver 300
SF NY 750(所影响的行数为 7 行)From to cost
---------- ---------- -----------
SF Chicago 550
Denver NY 500
SF NY 700(所影响的行数为 3 行)from to cost
---------- ---------- -----------
Denver Chicago 250
SF Chicago 550
SF Denver 300
Chicago NY 250
Denver NY 400
SF NY 700
Denver SF 250(所影响的行数为 7 行)
--*/
b)select a.from,b.to,min(a.cost+b.cost) from Flight a,Flight b where a.to = b.from AND a.from <> b.to group by a.from,b.to