一道csdn上sql语句的题目

gxh973121

引自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

本人文笔实在太差，所以分析就不写了