日期:2014-05-18 浏览次数:20668 次
create table saleinfo201205( vipcardno varchar(20),saleno varchar(20),sstotal float,xsdate datetime) insert into saleinfo201205 values('101','1',600,'2012-01-01') insert into saleinfo201205 values( '102','1',500,'2012-01-02') insert into saleinfo201205 values( '103','1',400,'2012-01-03') insert into saleinfo201205 values( '101','2',600,'2012-01-02') insert into saleinfo201205 values( '101','3',600,'2012-01-03') insert into saleinfo201205 values( '101','4',600,'2012-01-04') insert into saleinfo201205 values( '105','1',1200,'2012-01-01') insert into saleinfo201205 values( '105','2',600,'2012-01-03') select * from saleinfo201205 a where exists(select 1 from ( select vipcardno,min(xsdate) as xsdate from saleinfo201205 group by vipcardno)b where a.xsdate=xsdate and a.vipcardno=vipcardno ) and sstotal<1000 101 1 600 2012-01-01 00:00:00.000 102 1 500 2012-01-02 00:00:00.000 103 1 400 2012-01-03 00:00:00.000
------解决方案--------------------
select vipcardno,saleno,sstotal,xsdate from( select px=ROW_NUMBER()over(partition by vipcardno,saleno order by xsdate asc),* from tbl)t where vipcardno in(select vipcardno from( select vipcardno,COUNT(1) from tbl group by vipcardno having COUNT(1)=1)m) and px=1 and sstotal<=1000
------解决方案--------------------
select vipcardno,min(saleno) as saleno,sum(sstotal) as sstotal,xsdate from saleinfo201205 a where exists(select 1 from ( select vipcardno,min(xsdate) as xsdate from saleinfo201205 group by vipcardno)b where a.xsdate=xsdate and a.vipcardno=vipcardno ) group by vipcardno,xsdate having sum(sstotal)<1000
------解决方案--------------------
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [vipcardno] int, [SaleNo] bigint, [xsdate] datetime, [sstotal] numeric(8,2) ) insert [test] select 580872,2012050115126642,'2012-05-01 20:44:08.000',598.00 union all select 580874,2012051915037170,'2012-05-19 16:14:32.000',54.00 union all select 580876,2012052605248185,'2012-05-26 11:28:45.000',0.30 union all select 580876,2012052605248185,'2012-05-26 11:28:45.000',9.90 union all select 580876,2012052605248185,'2012-05-26 11:28:45.000',27.20 union all select 580876,2012052605248185,'2012-05-26 11:28:45.000',6.70 union all select 580876,2012052605248185,'2012-05-26 11:28:45.000',9.60 union all select 580876,2012052605248185,'2012-05-26 11:28:45.000',9.60 union all select 580876,2012052605248185,'2012-05-26 11:28:45.000',6.04 union all select 580876,2012052605248186,'2012-05-26 11:29:46.000',3.36 union all select 580876,2012052605248186,'2012-05-26 11:29:46.000',18.03 union all select 580876,2012052605248186,'2012-05-26 11:29:46.000',22.90 union all select 580876,2012052605248186,'2012-05-26 11:29:46.000',53.90 union all select 580883,2012050311015023,'2012-05-03 14:20:11.000',299.00 union all select 580883,2012050311015032,'2012-05-03 14:33:55.000',150.00 select distinct [vipcardno],[SaleNo],[xsdate], SUM([sstotal])over( partition by [SaleNo]) as [sstotal] from test a where [xsdate]=(select MIN([xsdate]) from test b where a.vipcardno=b.vipcardno) /* vipcardno SaleNo xsdate sstotal 580872 2012050115126642 2012-05-01 20:44:08.000 598.00 580874 2012051915037170 2012-05-19 16:14:32.000 54.00 580876 2012052605248185 2012-05-26 11:28:45.000 69.34 580883 2012050311015023 2012-05-03 14:20:11.000 299.00 */ --哎,给的