declare @today datetime
set @today ='2014/12/19' --今天declare @weekstart datetime
declare @weekend datetime
set @weekstart ='2014/12/14'
set @weekend = '2014/12/20'CREATE TABLE #a(id int ,name CHAR(4),dt datetime)
INSERT #a
SELECT 1,'A','2014/12/19 11:20:20' UNION ALL
SELECT 1,'A','2014/12/19 11:21:22' UNION ALL
SELECT 1,'A','2014/12/11 10:30:30' UNION ALL
SELECT 1,'A','2014/12/18 09:20:20' UNION ALL
SELECT 1,'A','2014/12/17 12:10:20' UNION ALL
SELECT 2,'B','2014/12/19 10:20:00' UNION ALL
SELECT 2,'B','2014/12/19 12:20:22' UNION ALL
SELECT 2,'B','2014/12/18 08:40:20' select * from (
select id,name, MIN(dt) todaymindate
from #a
where CONVERT(varchar(10),dt,111) = @today
group by id,name ) a
inner join
(
select id,name, MIN(CONVERT(varchar(10),dt,108)) weekmindate --只看时间,不看日期
from #a
where CONVERT(varchar(10),dt,111) between @weekstart and @weekend
group by id,name
) b
on a.id = b.id
and a.name = b.name drop table #a
set @today ='2014/12/19' --今天declare @weekstart datetime
declare @weekend datetime
set @weekstart ='2014/12/14'
set @weekend = '2014/12/20'CREATE TABLE #a(id int ,name CHAR(4),dt datetime)
INSERT #a
SELECT 1,'A','2014/12/19 11:20:20' UNION ALL
SELECT 1,'A','2014/12/19 11:21:22' UNION ALL
SELECT 1,'A','2014/12/11 10:30:30' UNION ALL
SELECT 1,'A','2014/12/18 09:20:20' UNION ALL
SELECT 1,'A','2014/12/17 12:10:20' UNION ALL
SELECT 2,'B','2014/12/19 10:20:00' UNION ALL
SELECT 2,'B','2014/12/19 12:20:22' UNION ALL
SELECT 2,'B','2014/12/18 08:40:20' select * from (
select id,name, MIN(dt) todaymindate
from #a
where CONVERT(varchar(10),dt,111) = @today
group by id,name ) a
inner join
(
select id,name, MIN(CONVERT(varchar(10),dt,108)) weekmindate --只看时间,不看日期
from #a
where CONVERT(varchar(10),dt,111) between @weekstart and @weekend
group by id,name
) b
on a.id = b.id
and a.name = b.name drop table #a
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货