select top 1 *
from test t
where
(select sum(col2) from test where col1<=t.col1)<=580
order by
col1 desc/**
col1 col2
----------- -----------
5 20(所影响的行数为 1 行)
**/
from test t
where
(select sum(col2) from test where col1<=t.col1)<=580
order by
col1 desc/**
col1 col2
----------- -----------
5 20(所影响的行数为 1 行)
**/
from (
select *,total=(select sum(col2) from test where col1<=t.col1)
from test t
) tt
where total>580
order by col1
from [table] t where
(select sum(col2) from [table] where col1<=t.col1)<=580
order by
col1 desc
from test t
where
(select sum(col2) from test where col1<=t.col1)>=580
order by
col1 /**
col1 col2
----------- -----------
6 60(所影响的行数为 1 行)
**/
go
create table test(col1 int,col2 int)
insert test select 1,10
union all select 2,12
union all select 3,20
union all select 4,500
union all select 5,20
union all select 6,60
union all select 7,5
union all select 8,80
select * from test where col1=( select top 1 col1 from(
select col1,(select sum(col2) col2 from test where col1<=t.col1)col2 from test t) t where col2>580)
/*col1 col2
----------- -----------
6 60(影響 1 個資料列)
*/
insert test select 1,10
union all select 2,12
union all select 3,20
union all select 4,500
union all select 5,20
union all select 6,60
union all select 7,5
union all select 8,80
;with hgo as
(
select col1,Tcol=isnull((select sum(col2) from test where col1<t.col1),0)
from test t
)
select top 1 * from hgo where Tcol>580
insert test1 select 1,10
union all select 2,12
union all select 3,20
union all select 4,500
union all select 5,20
union all select 6,60
union all select 7,5
union all select 8,80SELECT TOP 1 * FROM test1 a WHERE (SELECT SUM(col2) FROM test1 WHERE col1<=a.col1)>508 ORDER BY col1--result
/*col1 col2
----------- -----------
4 500(所影响的行数为 1 行)
*/
速度慢了很多create table test(col1 int,col2 int)
insert test select 1,10
union all select 2,12
union all select 3,20
union all select 4,500
union all select 5,20
union all select 6,60
union all select 7,5
union all select 8,80;with hgo as
(
select col1,Tcol=isnull((select sum(col2) from test where col1<=t.col1),0)
from test t
)
select top 1 t.* from hgo h join test t on t.col1=h.col1 where Tcol>580
col1 col2
----------- -----------
6 60(1 行受影响)