From 9CBS Title http://community.9cbs.net/expert/topic/3616/3616067.xml?temp=.2638361 The contents of the topic are as follows: The TBID field is numbered, increasing is not necessarily continuous. M field is a segment road fee For example, from home to Harbin is 60 yuan, from Harbin to Changchun is 70 yuan, numerical type. S field site name. There are 500 yuan, from Jiajing, Harbin, how far? (Example: 60 70 80 50 90 75 = 425 60 70 80 50 90 75 80 = 505 So the answer should be 6 Wuhan)
ID M S1 60 Harbin 2 70 Changchun 3 80 Shenyang 4 50 Beijing 5 90 Zhengzhou 6 75 Wuhan 7 80 Changsha 8 90 Guangdong
Require, please use a SQL statement * / - Create Table Create Table TB (ID Int Id Id, S Varchar (50)) - Test Data INTO TB VALUES (60, ' Harbin ') Insert Into TB Values (70,' Changchun ') Insert Into TB Values (80,' Shenyang ') Insert Into TB Values (50,' Beijing ') Insert Into TB Values (90,' Zhengzhou ') Insert Into TB VALUES (75, 'Wuhan') Insert Into Tb Values (80, 'Changsha) Insert Into TB Values (90,' Guangdong ')
-------------------------------------------------- -------------------------------------------------- ---
Two types of implementation 1.Select Top 1 B.ID, BS, SUM (AM) S_Sum from TB A, TB B Where A. ID <= B.ID Group by B.ID, BS Having Sum (AM) <= 500 Order by B.id Desc2.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 I'mwork is too bad, so the analysis is not written.
