求助：同一张表中按两个不同字段分组问题
现有五月销售表saleinfo201205，表里有：
vipcardno(会员卡号),saleno（流水号）,sstotal（销售金额）,xsdate（销售时间）
现在要会员单笔流水低于1000且是第一次消费的记录
也就是要最小时间，单笔流水低于1000的
怎么样又能按这个vipcardno分组求小xsdate，还能按saleno分组求sstotal总额
我都快哭死了，整了好久，求高手垂怜！！！
[解决办法]
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<1000101 1 600 2012-01-01 00:00:00.000102 1 500 2012-01-02 00:00:00.000103 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)twhere 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 allselect 580874,2012051915037170,'2012-05-19 16:14:32.000',54.00 union allselect 580876,2012052605248185,'2012-05-26 11:28:45.000',0.30 union allselect 580876,2012052605248185,'2012-05-26 11:28:45.000',9.90 union allselect 580876,2012052605248185,'2012-05-26 11:28:45.000',27.20 union allselect 580876,2012052605248185,'2012-05-26 11:28:45.000',6.70 union allselect 580876,2012052605248185,'2012-05-26 11:28:45.000',9.60 union allselect 580876,2012052605248185,'2012-05-26 11:28:45.000',9.60 union allselect 580876,2012052605248185,'2012-05-26 11:28:45.000',6.04 union allselect 580876,2012052605248186,'2012-05-26 11:29:46.000',3.36 union allselect 580876,2012052605248186,'2012-05-26 11:29:46.000',18.03 union allselect 580876,2012052605248186,'2012-05-26 11:29:46.000',22.90 union allselect 580876,2012052605248186,'2012-05-26 11:29:46.000',53.90 union allselect 580883,2012050311015023,'2012-05-03 14:20:11.000',299.00 union allselect 580883,2012050311015032,'2012-05-03 14:33:55.000',150.00select distinct [vipcardno],[SaleNo],[xsdate],SUM([sstotal])over( partition by [SaleNo]) as [sstotal]from test awhere [xsdate]=(select MIN([xsdate]) from test b where a.vipcardno=b.vipcardno)/*vipcardno SaleNo xsdate sstotal580872 2012050115126642 2012-05-01 20:44:08.000 598.00580874 2012051915037170 2012-05-19 16:14:32.000 54.00580876 2012052605248185 2012-05-26 11:28:45.000 69.34580883 2012050311015023 2012-05-03 14:20:11.000 299.00*/--哎，给的测试数据时间都是一样的
[解决办法]
with cte as (select '580872' as vipcardno,'2012050115126642' as SaleNo,'2012-05-01 20:44:08.000' as xsdate,598.00 as sstotal 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 ,min(SaleNo) over(partition by vipcardno) as min_saleNo ,min(xsdate) over(partition by vipcardno) as min_xsdate ,sum(sstotal) over(partition by xsdate) as sum_sstotalfrom cte where sstotal <1000--vipcardno min_saleNo min_xsdate sum_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--580876 2012052605248185 2012-05-26 11:28:45.000 98.19--580883 2012050311015023 2012-05-03 14:20:11.000 150.00--580883 2012050311015023 2012-05-03 14:20:11.000 299.00--(6 row(s) affected)