以前发过一次 没有太好的结果 再来1贴
表结构如下:ID主键 我要去旅游,假设手里有100元路费
如下表,从我家到A地要15元路费,从A地到B地要26元,以此类推
问题:我这100元最多能溜达到哪呢?(单程只算去的费用)
(如果可以的话,不要用临时表或存储过程啥的,就SQL语句,本人菜存储过程有点看不懂呵呵)ID 地点 花费
1 A地 15
4 B地 26
5 C地 10
6 D地 30
8 E地 12
9 F地 54 下面是建表语句 各位大大辛苦了
create table tb
(
id int identity(1,1),
didian varchar(20),
huafei int
)
insert into tb (didian,huafei)
select 'A地',15 union
select 'B地',26 union
select 'C地',10 union
select 'D地',30 union
select 'E地',12 union
select 'F地',54 union
select 'A地',15
表结构如下:ID主键 我要去旅游,假设手里有100元路费
如下表,从我家到A地要15元路费,从A地到B地要26元,以此类推
问题:我这100元最多能溜达到哪呢?(单程只算去的费用)
(如果可以的话,不要用临时表或存储过程啥的,就SQL语句,本人菜存储过程有点看不懂呵呵)ID 地点 花费
1 A地 15
4 B地 26
5 C地 10
6 D地 30
8 E地 12
9 F地 54 下面是建表语句 各位大大辛苦了
create table tb
(
id int identity(1,1),
didian varchar(20),
huafei int
)
insert into tb (didian,huafei)
select 'A地',15 union
select 'B地',26 union
select 'C地',10 union
select 'D地',30 union
select 'E地',12 union
select 'F地',54 union
select 'A地',15
from tb t
where (select sum(huafei) from tb where id<=t.id)<=100
id didian huafei
----------- -------------------- -----------
1 A地 15
2 B地 26
3 C地 10
4 D地 30
5 E地 12(所影响的行数为 5 行)
**/
DECLARE @a table
(
id int identity(1,1),
didian varchar(20),
huafei int
)
insert into @a (didian,huafei)
select 'A地',15 union
select 'B地',26 union
select 'C地',10 union
select 'D地',30 union
select 'E地',12 union
select 'F地',54 union
select 'A地',15 SELECT * FROM @a a WHERE (SELECT sum(huafei) FROM @a WHERE id<=a.id)<=100--result
/*id didian huafei
----------- -------------------- -----------
1 A地 15
2 B地 26
3 C地 10
4 D地 30
5 E地 12(所影响的行数为 5 行)
*/
(
id int identity(1,1),
didian varchar(20),
huafei int
) --DROP TABLE TB
insert into tb (didian,huafei)
select 'A地',15 union
select 'B地',26 union
select 'C地',10 union
select 'D地',30 union
select 'E地',12 union
select 'F地',54 union
select 'A地',15 SELECT TOP 1 DIDIAN
FROM (
SELECT *,(SELECT SUM(HUAFEI) FROM tb WHERE K.id>=ID) AS KS
FROM tb K
) Z
WHERE KS<=100
ORDER BY KS DESC
/*
DIDIAN
--------------------
E地*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-25 12:04:58
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[地点] varchar(3),[花费] int)
insert [tb]
select 1,'A地',15 union all
select 4,'B地',26 union all
select 5,'C地',10 union all
select 6,'D地',30 union all
select 8,'E地',12 union all
select 9,'F地',54
--------------开始查询--------------------------
select
*
from
tb t
where
(select sum(花费) from tb where id<=t.id)<=100
----------------结果----------------------------
/* ID 地点 花费
----------- ---- -----------
1 A地 15
4 B地 26
5 C地 10
6 D地 30
8 E地 12(5 行受影响)*/
from tb a ,tb b
where a.id<=b.id
group by a.ID,a.didian
having sum(b.huafei)<=100
from tb t
where (select sum(huafei) from tb where id<=t.id)<=100以这个为例
谁给讲讲 思路 好吗