紧急求助！！请问这个查询语句怎么写，从多条相同的数据选取第一条？
01 2005-4-20 12:01:43 3.57
01 2005-4-20 13:01:43 3.36
01 2005-4-20 13:01:44 3.57
01 2005-4-20 14:01:43 3.29
01 2005-4-21 0:01:44 3.55
01 2005-4-21 0:01:45 3.45
01 2005-4-21 1:01:44 3.49
01 2005-4-21 1:01:45 3.55
02 2005-4-22 0:01:45 3.96
02 2005-4-22 0:01:46 4.16
02 2005-4-22 1:01:45 3.93
02 2005-4-22 1:01:46 3.96
02 2005-4-23 17:01:47 4.95
02 2005-4-23 18:01:46 5.18
02 2005-4-23 18:01:47 4.86
02 2005-4-23 19:01:46 5.16
我想要的查询结果是：
01 2005-4-20 12:01:43 3.57
01 2005-4-21 0:01:44 3.55
02 2005-4-22 0:01:45 3.96
02 2005-4-23 17:01:47 4.95
也就是只要每个日期的第一条数据，希望各位高手帮忙！
------解决方案--------------------
Try:
我假设你表中的字段分别为 ID，Date,time
select * from [table] a
where not exists(select 1 from table where ID=a.ID and [date] <a.[date])
order by ID
[解决办法]
create table T(A varchar(10), B datetime, C decimal(10,2))
insert T select '01 ', '2005-4-20 12:01:43 ', 3.57
union all select '01 ', '2005-4-20 13:01:43 ', 3.36
union all select '01 ', '2005-4-20 13:01:44 ', 3.57
union all select '01 ', '2005-4-20 14:01:43 ', 3.29
union all select '01 ', '2005-4-21 0:01:44 ', 3.55
union all select '01 ', '2005-4-21 0:01:45 ', 3.45
union all select '01 ', '2005-4-21 1:01:44 ', 3.49
union all select '01 ', '2005-4-21 1:01:45 ', 3.55
union all select '02 ', '2005-4-22 0:01:45 ', 3.96
union all select '02 ', '2005-4-22 0:01:46 ', 4.16
union all select '02 ', '2005-4-22 1:01:45 ', 3.93
union all select '02 ', '2005-4-22 1:01:46 ', 3.96
union all select '02 ', '2005-4-23 17:01:47 ', 4.95
union all select '02 ', '2005-4-23 18:01:46 ', 5.18
union all select '02 ', '2005-4-23 18:01:47 ', 4.86
union all select '02 ', '2005-4-23 19:01:46 ', 5.16
select * from T as tmp
where not exists(select 1 from T where
convert(char(10), B, 120)=convert(char(10), tmp.B, 120) and B <tmp.B)
--result
A B C
---------- ------------------------------------------------------ ------------
01 2005-04-20 12:01:43.000 3.57
01 2005-04-21 00:01:44.000 3.55
02 2005-04-22 00:01:45.000 3.96
02 2005-04-23 17:01:47.000 4.95
(4 row(s) affected)
[解决办法]
create table T(A varchar(10), B datetime, C decimal(10,2))
insert T select '01 ', '2005-4-20 12:01:43 ', 3.57
union all select '01 ', '2005-4-20 13:01:43 ', 3.36
union all select '01 ', '2005-4-20 13:01:44 ', 3.57
union all select '01 ', '2005-4-20 14:01:43 ', 3.29
union all select '01 ', '2005-4-21 0:01:44 ', 3.55
union all select '01 ', '2005-4-21 0:01:45 ', 3.45
union all select '01 ', '2005-4-21 1:01:44 ', 3.49
union all select '01 ', '2005-4-21 1:01:45 ', 3.55
union all select '02 ', '2005-4-22 0:01:45 ', 3.96
union all select '02 ', '2005-4-22 0:01:46 ', 4.16
union all select '02 ', '2005-4-22 1:01:45 ', 3.93
union all select '02 ', '2005-4-22 1:01:46 ', 3.96
union all select '02 ', '2005-4-23 17:01:47 ', 4.95
union all select '02 ', '2005-4-23 18:01:46 ', 5.18
union all select '02 ', '2005-4-23 18:01:47 ', 4.86
union all select '02 ', '2005-4-23 19:01:46 ', 5.16
select A,B,C from T a
where B= ( select top 1 B from T b
where substring(cast(a.B as varchar),1,9)=substring(cast(b.B as varchar),1,9) )
结果
（所影响的行数为 16 行）
A B C
---------- ------------------------------------------------------ ------------
01 2005-04-20 12:01:43.000 3.57
01 2005-04-21 00:01:44.000 3.55
02 2005-04-22 00:01:45.000 3.96
02 2005-04-23 17:01:47.000 4.95
（所影响的行数为 4 行）
[解决办法]
假设你表中的字段分别为 ID，Date,time,表名是table
with t as(select *,row_number() over(partition by convert(varchar,Date,112) order by Date ) Bank from table)
select ID，Date,time from t where Bank=1
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id varchar(10),dt datetime,value decimal(18,2))
insert into tb(id,dt,value) values( '01 ', '2005-4-20 12:01:43 ', 3.57)
insert into tb(id,dt,value) values( '01 ', '2005-4-20 13:01:43 ', 3.36)
insert into tb(id,dt,value) values( '01 ', '2005-4-20 13:01:44 ', 3.57)
insert into tb(id,dt,value) values( '01 ', '2005-4-20 14:01:43 ', 3.29)
insert into tb(id,dt,value) values( '01 ', '2005-4-21 0:01:44 ', 3.55)
insert into tb(id,dt,value) values( '01 ', '2005-4-21 0:01:45 ', 3.45)
insert into tb(id,dt,value) values( '01 ', '2005-4-21 1:01:44 ', 3.49)
insert into tb(id,dt,value) values( '01 ', '2005-4-21 1:01:45 ', 3.55)
insert into tb(id,dt,value) values( '02 ', '2005-4-22 0:01:45 ', 3.96)
insert into tb(id,dt,value) values( '02 ', '2005-4-22 0:01:46 ', 4.16)
insert into tb(id,dt,value) values( '02 ', '2005-4-22 1:01:45 ', 3.93)
insert into tb(id,dt,value) values( '02 ', '2005-4-22 1:01:46 ', 3.96)
insert into tb(id,dt,value) values( '02 ', '2005-4-23 17:01:47 ', 4.95)
insert into tb(id,dt,value) values( '02 ', '2005-4-23 18:01:47 ', 4.86)
insert into tb(id,dt,value) values( '02 ', '2005-4-23 19:01:46 ', 5.16)
go
select a.* from tb a,
(select id,convert(varchar(10),dt,120) rq,min(dt) dt from tb group by id,convert(varchar(10),dt,120)) b
where a.id = b.id and a.dt = b.dt
drop table tb
/*
id dt value
---------- ------------------------------------------------------ --------------------
01 2005-04-20 12:01:43.000 3.57
01 2005-04-21 00:01:44.000 3.55
02 2005-04-22 00:01:45.000 3.96
02 2005-04-23 17:01:47.000 4.95
（所影响的行数为 4 行）
*/
