tableid 字段1 字段2 ... 字段n updatetime
1 .................... 2010-1-29 8:31
2 .................... 2010-1-29 10:31
3 .................... 2010-1-29 12:21
4 .................... 2010-1-30 9:56
5 .................... 2010-1-30 8:42
6 .................... 2010-1-30 7:52
7 .................... 2010-1-30 8:56
8 .................... 2010-1-31 6:30
9 .................... 2010-1-31 8:30
10 .................... 2010-1-31 18:21求一条SQL语句,查询出每天第一条记录的集合结果应该为:
id 字段1 字段2 ... 字段n updatetime
1 .................... 2010-1-29 8:31
4 .................... 2010-1-30 7:52
8 .................... 2010-1-31 6:30
1 .................... 2010-1-29 8:31
2 .................... 2010-1-29 10:31
3 .................... 2010-1-29 12:21
4 .................... 2010-1-30 9:56
5 .................... 2010-1-30 8:42
6 .................... 2010-1-30 7:52
7 .................... 2010-1-30 8:56
8 .................... 2010-1-31 6:30
9 .................... 2010-1-31 8:30
10 .................... 2010-1-31 18:21求一条SQL语句,查询出每天第一条记录的集合结果应该为:
id 字段1 字段2 ... 字段n updatetime
1 .................... 2010-1-29 8:31
4 .................... 2010-1-30 7:52
8 .................... 2010-1-31 6:30
*
from
tb t
where
updatetime=(select min(updatetime) from tb where convert(varchar(10),updatetime,120)=convert(varchar(10),t.updatetime,120))
select T.* from T
inner join
(
select convert(char(10),updatetime,120) as [date],min(updatetime) as updatetime
from T
group by convert(char(10),updatetime,120)
) A
on T.updatetime=A.updatetime
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-29 15:07:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[字段1] varchar(20),[updatetime] datetime)
insert [tb]
select 1,'....................','2010-1-29 8:31' union all
select 2,'....................','2010-1-29 10:31' union all
select 3,'....................','2010-1-29 12:21' union all
select 4,'....................','2010-1-30 9:56' union all
select 5,'....................','2010-1-30 8:42' union all
select 6,'....................','2010-1-30 7:52' union all
select 7,'....................','2010-1-30 8:56' union all
select 8,'....................','2010-1-31 6:30' union all
select 9,'....................','2010-1-31 8:30' union all
select 10,'....................','2010-1-31 18:21'
--------------开始查询--------------------------
select
*
from
tb t
where
updatetime=(select min(updatetime) from tb where convert(varchar(10),updatetime,120)=convert(varchar(10),t.updatetime,120))----------------结果----------------------------
/* id 字段1 updatetime
----------- -------------------- -----------------------
1 .................... 2010-01-29 08:31:00.000
6 .................... 2010-01-30 07:52:00.000
8 .................... 2010-01-31 06:30:00.000(3 行受影响)*/
select T.*
from
(
select * ,cnt=row_number() over (partion by convert(char(10),updatetime,120) order by updatetime)
from T
) A
where cnt=1
select id,字段1,字段2, ..., 字段n, updatetime from
(select *,rid=row_number() over (partition by convert(varchar(10),[updatetime],120) order by [updatetime]) from tb) t
where rid=1
create table #temp
(idx int identity(1,1),updatetime datetime)
go
insert into #temp
select '2010-1-29 8:31'
union all
select '2010-1-29 10:31'
union all
select '2010-1-29 12:21'
union all
select '2010-1-30 9:56'
union all
select '2010-1-30 8:42'
union all
select '2010-1-30 7:52'
union all
select '2010-1-30 8:56'
union all
select '2010-1-31 6:30'
union all
select '2010-1-31 8:30'
union all
select '2010-1-31 18:21'
go
select * from #temp where updatetime in
(
select min(updatetime) from #temp
group by convert(char(10),updatetime,120)
)
go
drop table #temp
go
*
from
tel_Info t
where
updatetime=(select min(updatetime) from tel_Info where convert(varchar(10),updatetime,120)=convert(varchar(10),t.updatetime,120))
insert into @tb select '2010-1-29 10:31'
insert into @tb select '2010-1-29 12:21'
insert into @tb select '2010-1-30 9:56'
insert into @tb select '2010-1-30 8:42'
insert into @tb select '2010-1-30 7:52'
insert into @tb select '2010-1-30 8:56'
insert into @tb select '2010-1-31 6:30'
insert into @tb select '2010-1-31 8:30'
insert into @tb select '2010-1-31 18:21' select t.*
from @tb t,(select min(updatetime) as updatetime
from @tb
group by left(updatetime,10)
) a
where t.updatetime=a.updatetime /*
id updatetime
----------- -----------------------
1 2010-01-29 08:31:00.000
6 2010-01-30 07:52:00.000
8 2010-01-31 06:30:00.000
*/
(select min(id) id from table1 group by convert(char(10),updatetime,120)
FROM 表名
WHERE [ID] IN
(SELECT [ID]
FROM 表名
GROUP BY CAST(updatetime AS VARCHAR(10)) --去掉时分秒
HAVING updatetime = MIN(updatetime) --过滤只取每天的第一条数据
)
得不出你想要的答案可再找我。
from users
where id
in
(
select min(id)
from users
group by convert(nvarchar(10),addtime,120)
)
order by convert(nvarchar(10),addtime,120)
from table a
where not exists (select 1 from table where convert(char(10),updatetime,120)=convert(char(10),a.updatetime,120) and updatetime>a.updatetime)