CREATE TABLE T_20161004( ID INT IDENTITY(1,1) NOT NULL , uPrice INT , Name VARCHAR (10) ) INSERT INTO T_20161004 VALUES (60, '哈尔滨' ) INSERT INTO T_20161004 VALUES (70, '长春' ) INSERT INTO T_20161004 VALUES (80, '沈阳' ) INSERT INTO T_20161004 VALUES (50, '北京' ) INSERT INTO T_20161004 VALUES (90, '郑州' ) INSERT INTO T_20161004 VALUES (75, '武汉' ) INSERT INTO T_20161004 VALUES (80, '长沙' ) INSERT INTO T_20161004 VALUES (90, '广州' ) -- 方法一 交叉连接 不等值连接 不容易理解 SELECT TOP 1 b.id, b. NAME , SUM (a.uprice) s_sum FROM T_20161004 a , T_20161004 b WHERE a.id<=b.id GROUP BY b.id,b. name HAVING SUM (a.uprice)<=500 ORDER BY b.id DESC -- 方法二 自连接 SELECT TOP 1 b.id,b. NAME , SUM (a.uprice) s_sum FROM T_20161004 a INNER JOIN T_20161004 b ON a.id<=b.id --不等值连接 GROUP BY b.id,b. name HAVING SUM (a.uprice)<=500 ORDER BY b.id DESC -- 方法三 子查询 比较容易理解 SELECT TOP 1 ID, NAME , M_SUM FROM ( SELECT *, ( SELECT SUM (uprice) from T_20161004 WHERE ID <= A.ID) AS M_SUM FROM T_20161004 A ) B WHERE M_SUM<=500 ORDER BY ID DESC /* 子查询步骤 1. 外部查询获得一条记录并将其传入内部查询; 2. 基于传入的值进行内部查询 3. 内部查询将自己返回的结果值传给外部查询,外部查询利用这些值完成自己的处理。 */ SELECT *, ( SELECT SUM (uprice) from T_20161004 WHERE ID <= A.ID) AS M_SUM FROM T_20161004 A |