需建立两个VIEW
1、第一个view(view_1)如下:
SELECT DATENAME(YY, DATE) + DATENAME(MM, DATE) + DATENAME(DD,
DATE) AS AA, DATE
FROM table_date GROUP BY DATE
2、第二个view(view_2)如下:
SELECT AA, BB = MAX(DATENAME(HH, DATE) + ':' + DATENAME(MI, DATE)
+ ':' + DATENAME(SS, DATE))
FROM view_1
GROUP BY AA
那就可以了~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~试试吧!!!
1、第一个view(view_1)如下:
SELECT DATENAME(YY, DATE) + DATENAME(MM, DATE) + DATENAME(DD,
DATE) AS AA, DATE
FROM table_date GROUP BY DATE
2、第二个view(view_2)如下:
SELECT AA, BB = MAX(DATENAME(HH, DATE) + ':' + DATENAME(MI, DATE)
+ ':' + DATENAME(SS, DATE))
FROM view_1
GROUP BY AA
那就可以了~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~试试吧!!!
create table test (id int,time datetime)
插入数据:
insert into test values(1,'2001-01-01 00:00:01')
insert into test values(2,'2001-01-01 00:00:03')
insert into test values(3,'2001-01-02 00:00:03')
insert into test values(4,'2001-01-02 00:00:05')
insert into test values(5,'2001-01-03 00:00:03')
筛选
select * from test where time in(select min(time) from test group by convert
(varchar(10),time,20))
结果:
id time
----------- ---------------------------
1 2001-01-01 00:00:01.000
3 2001-01-02 00:00:03.000
5 2001-01-03 00:00:03.000
select * from sysobjects
where crdate in (select min(crdate) from sysobjects group by convert(varchar(10),crdate,121))
返回的结果显然是错误的:
id time
----------- ---------------------------------
1 2001-01-01 00:00:01.000
3 2001-01-02 00:00:03.000 *
2 2001-01-02 00:00:03.000 *
5 2001-01-03 00:00:03.000
考虑到效率建议使用:
select test.* from test,(select min(time) mt from test group by convert
(varchar(10),time,20)) b where test.time=b.mt