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 也就是只要每个日期的第一条数据,希望各位高手帮忙!
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 也就是只要每个日期的第一条数据,希望各位高手帮忙!
我假设你表中的字段分别为 ID,Date,timeselect * from [table] a
where not exists(select 1 from table where ID=a.ID and [date]<a.[date])
order by ID
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)
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 行)
select ID,Date,time from t where Bank=1
drop table tb
gocreate 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.dtdrop 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 行)*/