一道csdn上sql语句的题目
引自csdn题目
http://community.csdn.net/Expert/topic/3616/3616067.xml?temp=.2638361
题目内容如下
*
一表TB
Id 字段为编号,递增不一定连续.
M 字段为区段路费,比如从家到哈尔滨是60元,从哈尔滨到长春是70元,数值类型.
S 字段站点名称.
现有500元钱,从家先经哈尔滨出发,能走多远?
(例:60+70+80+50+90+75=425 60+70+80+50+90+75+80=505 所以答案应该是6武汉)
Id M S
1 60 哈尔滨
2 70 长春
3 80 沈阳
4 50 北京
5 90 郑州
6 75 武汉
7 80 长沙
8 90 广东
要求,请用一句SQL语句实现
*/
--创建表
Create Table TB
(
Id Int Identity(1, 1) Not Null,
M Int,
S varchar(50)
)
--测试数据
Insert Into TB Values(60, '哈尔滨')
Insert Into TB Values(70,'长春')
Insert Into TB Values(80,'沈阳')
Insert Into TB Values(50,'北京')
Insert Into TB Values(90,'郑州')
Insert Into TB Values(75,'武汉')
Insert Into TB Values(80,'长沙')
Insert Into TB Values(90,'广东')
-------------------------------------------------------------------------------------------------------
实现方法两种
1.
SELECT TOP 1 B.id, b.s, sum(A.m) s_sum
FROM TB A,
TB B
WHERE A.ID <= B.ID
GROUP BY b.id,
b.s
HAVING sum(a.m) <= 500
ORDER BY b.id DESC
2.
SELECT TOP 1 ID, S, M_SUM
FROM (SELECT *,
(SELECT SUM(M)
FROM TB
WHERE ID >= 1
AND ID <= A.ID) AS M_SUM
FROM TB A) B
WHERE M_SUM <= 500
ORDER BY ID DESC
本人文笔实在太差，所以分析就不写了
