效率不敢保证--测试环境 if OBJECT_ID('tb') is not null drop table tb; go create table tb ( id int identity, username varchar(10), paymentdate datetime, amount numeric(9,2) ); go insert into tb select 'aaa',' 2010-7-1 0:00:00 ',2.00 union all select 'bbb',' 2010-7-2 0:00:00 ',4.00 union all select 'ccc',' 2010-7-3 0:00:00 ',2.00 union all select 'aaa',' 2010-7-4 0:00:00 ',4.00 union all select 'bbb',' 2010-7-5 0:00:00 ',10.00 union all select 'aaa',' 2010-7-6 0:00:00 ',6.00 union all select 'ccc',' 2010-7-7 0:00:00 ',4.00 union all select 'aaa',' 2010-7-8 0:00:00 ',6.00 union all select 'bbb',' 2010-7-9 0:00:00 ',2.00go --查询 select username,max([6])[达到6.00的最早日期], max([10])[达到10.00的最早日期],max([15])[达到15.00的最早日期] from ( select username, case when (select sum(amount) from tb where username=t.username and paymentdate<=t.paymentdate)>=6 and (select sum(amount) from tb where username=t.username and paymentdate<t.paymentdate ) <6 then paymentdate end [6], case when (select sum(amount) from tb where username=t.username and paymentdate<=t.paymentdate ) >=10 and (select sum(amount) from tb where username=t.username and paymentdate<t.paymentdate ) <10 then paymentdate end [10], case when (select sum(amount) from tb where username=t.username and paymentdate<=t.paymentdate ) >=15 and (select sum(amount) from tb where username=t.username and paymentdate<t.paymentdate ) <15 then paymentdate end [15] from tb t )a group by username--结果 /* username 达到6.00的最早日期 达到10.00的最早日期 达到15.00的最早日期 ---------- ----------------------- ----------------------- ----------------------- aaa 2010-07-04 00:00:00.000 2010-07-06 00:00:00.000 2010-07-08 00:00:00.000 bbb 2010-07-05 00:00:00.000 2010-07-05 00:00:00.000 2010-07-09 00:00:00.000 ccc 2010-07-07 00:00:00.000 NULL NULL 警告: 聚合或其他 SET 操作消除了空值。(3 行受影响) */
declare @tb table (id int,username varchar(3),paymentdate datetime,amount numeric(4,2)) insert into @tb select 1,'aaa','2010-7-1 0:00:00',2.00 union all select 2,'bbb','2010-7-2 0:00:00',4.00 union all select 3,'ccc','2010-7-3 0:00:00',2.00 union all select 4,'aaa','2010-7-4 0:00:00',4.00 union all select 5,'bbb','2010-7-5 0:00:00',10.00 union all select 7,'aaa','2010-7-6 0:00:00',6.00 union all select 8,'ccc','2010-7-7 0:00:00',4.00 union all select 9,'aaa','2010-7-8 0:00:00',6.00 union all select 10,'bbb','2010-7-9 0:00:00',2.00 ;with wufeng as( select username, (select sum(amount) from @tb where username=t.username and paymentdate<=t.paymentdate)amount, paymentdate from @tb t ) select username, min(case when amount>=6 then paymentdate end)[达到6.00的最早日期], min(case when amount>=10 then paymentdate end)[达到10.00的最早日期], min(case when amount>=15 then paymentdate end)[达到15.00的最早日期] from wufeng group by username /* username 达到6.00的最早日期 达到10.00的最早日期 达到15.00的最早日期 -------- ----------------------- ----------------------- ----------------------- aaa 2010-07-04 00:00:00.000 2010-07-06 00:00:00.000 2010-07-08 00:00:00.000 bbb 2010-07-05 00:00:00.000 2010-07-05 00:00:00.000 2010-07-09 00:00:00.000 ccc 2010-07-07 00:00:00.000 NULL NULL 警告: 聚合或其他 SET 操作消除了空值。*/
if OBJECT_ID('tb') is not null drop table tb;
go
create table tb
(
id int identity,
username varchar(10),
paymentdate datetime,
amount numeric(9,2)
);
go
insert into tb
select 'aaa',' 2010-7-1 0:00:00 ',2.00 union all
select 'bbb',' 2010-7-2 0:00:00 ',4.00 union all
select 'ccc',' 2010-7-3 0:00:00 ',2.00 union all
select 'aaa',' 2010-7-4 0:00:00 ',4.00 union all
select 'bbb',' 2010-7-5 0:00:00 ',10.00 union all
select 'aaa',' 2010-7-6 0:00:00 ',6.00 union all
select 'ccc',' 2010-7-7 0:00:00 ',4.00 union all
select 'aaa',' 2010-7-8 0:00:00 ',6.00 union all
select 'bbb',' 2010-7-9 0:00:00 ',2.00go
--查询
select username,max([6])[达到6.00的最早日期],
max([10])[达到10.00的最早日期],max([15])[达到15.00的最早日期]
from (
select username,
case when (select sum(amount) from tb where username=t.username and paymentdate<=t.paymentdate)>=6 and (select sum(amount) from tb where username=t.username and paymentdate<t.paymentdate ) <6 then paymentdate end [6],
case when (select sum(amount) from tb where username=t.username and paymentdate<=t.paymentdate ) >=10 and (select sum(amount) from tb where username=t.username and paymentdate<t.paymentdate ) <10 then paymentdate end [10],
case when (select sum(amount) from tb where username=t.username and paymentdate<=t.paymentdate ) >=15 and (select sum(amount) from tb where username=t.username and paymentdate<t.paymentdate ) <15 then paymentdate end [15]
from tb t
)a
group by username--结果
/*
username 达到6.00的最早日期 达到10.00的最早日期 达到15.00的最早日期
---------- ----------------------- ----------------------- -----------------------
aaa 2010-07-04 00:00:00.000 2010-07-06 00:00:00.000 2010-07-08 00:00:00.000
bbb 2010-07-05 00:00:00.000 2010-07-05 00:00:00.000 2010-07-09 00:00:00.000
ccc 2010-07-07 00:00:00.000 NULL NULL
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
*/
insert into @tb
select 1,'aaa','2010-7-1 0:00:00',2.00 union all
select 2,'bbb','2010-7-2 0:00:00',4.00 union all
select 3,'ccc','2010-7-3 0:00:00',2.00 union all
select 4,'aaa','2010-7-4 0:00:00',4.00 union all
select 5,'bbb','2010-7-5 0:00:00',10.00 union all
select 7,'aaa','2010-7-6 0:00:00',6.00 union all
select 8,'ccc','2010-7-7 0:00:00',4.00 union all
select 9,'aaa','2010-7-8 0:00:00',6.00 union all
select 10,'bbb','2010-7-9 0:00:00',2.00
;with wufeng
as(
select username,
(select sum(amount)
from @tb
where username=t.username and paymentdate<=t.paymentdate)amount,
paymentdate
from @tb t )
select username,
min(case when amount>=6 then paymentdate end)[达到6.00的最早日期],
min(case when amount>=10 then paymentdate end)[达到10.00的最早日期],
min(case when amount>=15 then paymentdate end)[达到15.00的最早日期]
from wufeng
group by username
/*
username 达到6.00的最早日期 达到10.00的最早日期 达到15.00的最早日期
-------- ----------------------- ----------------------- -----------------------
aaa 2010-07-04 00:00:00.000 2010-07-06 00:00:00.000 2010-07-08 00:00:00.000
bbb 2010-07-05 00:00:00.000 2010-07-05 00:00:00.000 2010-07-09 00:00:00.000
ccc 2010-07-07 00:00:00.000 NULL NULL
警告: 聚合或其他 SET 操作消除了空值。*/