22,210
社区成员
发帖
与我相关
我的任务
分享
create table customer
(
mobile varchar(20),
dealtime datetime,
)
insert into customer values('123','2012-01-01 00:01:00')
insert into customer values('456','2012-01-01 00:02:00')
insert into customer values('123','2012-01-02 00:01:00')
insert into customer values('456','2012-01-02 00:02:00')
insert into customer values('789','2012-01-02 00:03:00')
with t as(select CONVERT(date,dealtime) dealdate,mobile from customer)
select o.dealdate,COUNT(*) count
from t o where o.mobile not in (select i.mobile from t i where i.dealdate < o.dealdate)
group by o.dealdate
order by o.dealdate
dealdate count
---------- -----------
2012-01-01 2
2012-01-02 1
(2 row(s) affected)