select * from tb a where (select sum(int) from tb where date<a.date)<100 abd (select sum(int) from tb where date<a.date)+a.int>100
select * from tb a where (select sum(int) from tb where date<a.date)<100 and (select sum(int) from tb where date<a.date)+a.int>100 -- 如果按name分组求 select * from tb a where (select sum(int) from tb where name=a.name and date<a.date)<100 and (select sum(int) from tb where name=a.name and date<a.date)+a.int>100
--求sum(int)>=100的最小时间 select min(date) from ( select name,date,int,flag=case when (select sum(int) from table where name<=a.name)>=100 then 1 else 0 end from table a ) new where flag=1 --sum(int)>=120的最小时间 select min(date) from ( select name,date,int,flag=case when (select sum(int) from table where name<=a.name)>=120 then 1 else 0 end from table a ) new where flag=1--sum(int)>=100时候的时间是多少select date from ( select name,date,int,flag=case when (select sum(int) from table where name<=a.name)<100 then 0 else case when (select sum(int) from table where name<a.name)>=100 then 2 else 1 end end from table a ) new where flag=1
------------ select * from [table] group by [name],[date],[int] having sum([int])>=100
DECLARE @t TABLE(id int,bh char(2), date datetime, [int] int) INSERT @t SELECT '240','BB','2005-10-1','100' UNION ALL SELECT '241','BB','2005-10-2','18' UNION ALL SELECT '242','BB','2005-10-3','3' UNION ALL SELECT '243','BB','2005-10-4','2'select * from @t a where isnull((select sum(int) from @t where date<a.date),0)<100 and isnull((select sum(int) from @t where date<a.date),0)+a.int>=100 -- 结果 id bh date int ----------- ---- ------------------------------------------------------ ----------- 240 BB 2005-10-01 00:00:00.000 100(所影响的行数为 1 行)
where (select sum(int) from tb where date<a.date)<100
abd (select sum(int) from tb where date<a.date)+a.int>100
where (select sum(int) from tb where date<a.date)<100
and (select sum(int) from tb where date<a.date)+a.int>100
-- 如果按name分组求
select * from tb a
where (select sum(int) from tb where name=a.name and date<a.date)<100
and (select sum(int) from tb where name=a.name and date<a.date)+a.int>100
select min(date)
from (
select name,date,int,flag=case when (select sum(int) from table where name<=a.name)>=100 then 1
else 0 end
from table a
) new
where flag=1
--sum(int)>=120的最小时间
select min(date)
from (
select name,date,int,flag=case when (select sum(int) from table where name<=a.name)>=120 then 1
else 0 end
from table a
) new
where flag=1--sum(int)>=100时候的时间是多少select date
from (
select name,date,int,flag=case when (select sum(int) from table where name<=a.name)<100 then 0
else case when (select sum(int) from table where name<a.name)>=100 then 2
else 1 end
end
from table a
) new
where flag=1
select * from [table] group by [name],[date],[int] having sum([int])>=100
还有就是sum(int)+int=100的这种情况好象也有问题,改为>=100好象就对了
where (select sum(int) from tb where date<a.date)<100
and (select sum(int) from tb where date<a.date)+a.int>=100 -- 包含100的这种情况
这个怎么办
而第2个表达式的计算结果为100, 满足>=100的条件
241 BB 2005-10-2 18 10
242 BB 2005-10-3 3 3
243 BB 2005-10-4 2 5我怎么不能求出BB的时间呀
INSERT @t SELECT '240','BB','2005-10-1','100'
UNION ALL SELECT '241','BB','2005-10-2','18'
UNION ALL SELECT '242','BB','2005-10-3','3'
UNION ALL SELECT '243','BB','2005-10-4','2'select *
from @t a
where isnull((select sum(int) from @t where date<a.date),0)<100
and isnull((select sum(int) from @t where date<a.date),0)+a.int>=100
-- 结果
id bh date int
----------- ---- ------------------------------------------------------ -----------
240 BB 2005-10-01 00:00:00.000 100(所影响的行数为 1 行)