22,209
社区成员
发帖
与我相关
我的任务
分享
--我想做个缴费历史记录的统计
--我想查询六月份到期但没有付费的记录该怎么写这条sql语句啊?
--记录Id 用户名 付费开始日期 付费结束日期
LogId UserName StartDate EndDate
1 zhangsan 2011-01-01 00:00:00 2011-03-31 00:00:00
2 zhangsan 2011-04-01 00:00:00 2011-06-30 00:00:00
3 zhangsan 2011-08-01 00:00:00 2011-09-30 00:00:00
4 lisi 2011-02-08 00:00:00 2011-06-06 00:00:00
5 lisi 2011-08-01 00:00:00 2011-10-31 00:00:00
6 zhaoliu 2011-05-01 00:00:00 2011-06-30 00:00:00
select * from tb a where convert(varchar(7),enddate,120)='2011-06' and
not exists(select 1 from tb where username=a.username and convert(varchar(7),startDate,120)='2011-06' and startDate>a.enddate)