快速查询表T1，字段B=0、字段A和值<=5000 按字段ID顺序排列最前面的记录， 并得到这些记录的最大ID和最小ID？？？

[yphy]

快速查询表T1，字段B=0、字段A和值<=5000 按字段ID顺序排列最前面的记录， 并得到这些记录的最大ID和最小ID？？？

比如工资表T1：

Name Sex Age Class Paid Salary ID

a f 22 1 0 1000 1
aa m 20 2 0 1500 2
b m 23 2 0 1500 3
cs f 21 2 1 1200 4
aw f 22 1 0 1200 5
add f 22 1 1 1000 6
......

Paid 0: 未付， 1: 已付

要求快速查出，没有支付的且排在最前的，Salary的和值<=5000的记录，也就是再加上其后的一条记录和值就超出了100000

例如上表，应该是
Name Sex Age Class Paid Salary ID

a f 22 1 0 1000 1
aa m 20 2 0 1500 2
b m 23 2 0 1500 3


目的是找到还没发工资的人员，但目前资金有限，最多只能发一小部分。一定要“快速”，越快越好。

[fcuandy]

看了半天看不明.
难道又是过滤后每个class取最小id的一条记录这种东东?

[dawugui]

select * from t1 where 字段B=0、字段A<=5000 order by id

select top 1 id as 最大ID from t1 where 字段B=0、字段A<=5000 order by id desc
select top 1 id as 最小ID from t1 where 字段B=0、字段A<=5000 order by id 

[dawugui]

我也没看明白,先写个所谓的字段A,B什么的.

[fwacky]

有点晕！

[dobear_0922]

--try
select * from T1
where Paid=0 and (select sum(Salary) from T1 t where ID<=T1.ID and Paid=0)<=5000

[playwarcraft]

目的是找到还没发工资的人员，但目前资金有限，最多只能发一小部分。

===================
強烈要求先發完工資！

[dobear_0922]

create table T1(Name nvarchar(32), Sex char(1), Age int, Class int, Paid int, Salary int, ID int)
insert T1 select 'a', 'f', 22, 1, 0, 1000, 1 
union all select 'aa', 'm', 20, 2, 0, 1500, 2 
union all select 'b', 'm', 23, 2, 0, 1500, 3 
union all select 'cs', 'f', 21, 2, 1, 1200, 4 
union all select 'aw', 'f', 22, 1, 0, 1200, 5 
union all select 'add', 'f', 22, 1, 1, 1000, 6 

--try
select * from T1
where Paid=0 and (select sum(Salary) from T1 t where ID<=T1.ID and Paid=0)<=5000

drop table T1

/*
Name                             Sex  Age         Class       Paid        Salary      ID
-------------------------------- ---- ----------- ----------- ----------- ----------- -----------
a                                f    22          1           0           1000        1
aa                               m    20          2           0           1500        2
b                                m    23          2           0           1500        3

(3 row(s) affected)
*/

[dobear_0922]

create table T1(Name nvarchar(32), Sex char(1), Age int, Class int, Paid int, Salary int, ID int)
insert T1 select 'a', 'f', 22, 1, 0, 1000, 1 
union all select 'aa', 'm', 20, 2, 0, 1500, 2 
union all select 'b', 'm', 23, 2, 0, 1500, 3 
union all select 'cs', 'f', 21, 2, 1, 1200, 4 
union all select 'aw', 'f', 22, 1, 0, 1200, 5 
union all select 'add', 'f', 22, 1, 1, 1000, 6 

--查没有发工资，且和计不超过5000的
select * from T1
where Paid=0 and (select sum(Salary) from T1 t where ID<=T1.ID and Paid=0)<=5000

--查最小、最大ID
select MinID=min(ID), MaxID=max(ID) from T1
where Paid=0 and (select sum(Salary) from T1 t where ID<=T1.ID and Paid=0)<=5000

drop table T1

/*
Name                             Sex  Age         Class       Paid        Salary      ID
-------------------------------- ---- ----------- ----------- ----------- ----------- -----------
a                                f    22          1           0           1000        1
aa                               m    20          2           0           1500        2
b                                m    23          2           0           1500        3

(3 row(s) affected)

MinID       MaxID
----------- -----------
1           3

(1 row(s) affected)
*/

[lwl0606]

select *,(select  sum( Salary ) from t1 where Paid=0 and ID<=A.id ) AS A
from t1 A
where Paid=0 AND (select  sum( Salary ) from t1 where Paid=0 and ID<=A.id )<=500
order by ID

[-狙击手-]

--try
select * 
from T1 t
where Paid=0 and (select sum(Salary) from T1 where id<=t.id and Paid=0)<=5000

[dobear_0922]

如果要提高效率，可以分两句来执行，，，

create table T1(Name nvarchar(32), Sex char(1), Age int, Class int, Paid int, Salary int, ID int)
insert T1 select 'a', 'f', 22, 1, 0, 1000, 1 
union all select 'aa', 'm', 20, 2, 0, 1500, 2 
union all select 'b', 'm', 23, 2, 0, 1500, 3 
union all select 'cs', 'f', 21, 2, 1, 1200, 4 
union all select 'aw', 'f', 22, 1, 0, 1200, 5 
union all select 'add', 'f', 22, 1, 1, 1000, 6 

--定义一个@MaxID以提高效率
declare @MaxID int
set @MaxID=(select TOP 1 ID from T1
			where Paid=0 and (select sum(Salary) from T1 t where ID<=T1.ID and Paid=0)>5000
			order by ID)

--查没有发工资，且和计不超过5000的
select * from T1
where ID<@MaxID and Paid=0 


drop table T1

/*
Name                             Sex  Age         Class       Paid        Salary      ID
-------------------------------- ---- ----------- ----------- ----------- ----------- -----------
a                                f    22          1           0           1000        1
aa                               m    20          2           0           1500        2
b                                m    23          2           0           1500        3

(3 row(s) affected)
*/