求查询语句

create table #time  (manno varchar(10), bshtime datetime)

--写数据

insert into #time

select 'A00258','2011-02-27 07:43:00.000' union all

select 'A00258','2011-02-27 07:52:00.000' union all

select 'A00258','2011-02-27 12:03:00.000' union all

select 'A00258','2011-02-27 12:21:00.000' union all

select 'A00258','2011-02-27 13:23:00.000'

--SQL

select b.* from

(select distinct manno, bshtime = convert(varchar(13),bshtime,121) from #time) a

cross apply

(select top(1) * from #time where manno = a.manno and convert(varchar(13),bshtime,121) = a.bshtime order by bshtime) b

order by b.bshtime

--RESULT

/*

manno	bshtime

A00258	2011-02-27 07:43:00.000

A00258	2011-02-27 12:03:00.000

A00258	2011-02-27 13:23:00.000

*/

--建表

create table #time  (manno varchar(10), bshtime datetime)

--写数据

insert into #time

select 'A00258','2011-02-27 07:43:00.000' union all

select 'A00258','2011-02-27 07:52:00.000' union all

select 'A00258','2011-02-27 12:03:00.000' union all

select 'A00258','2011-02-27 12:21:00.000' union all

select 'A00258','13:23:00.000'

--

select manno, min(bshtime) as bshtime from #time group by manno, datepart(hour,bshtime)





manno      bshtime

---------- -----------------------

A00258     2011-02-27 07:43:00.000

A00258     2011-02-27 12:03:00.000

A00258     1900-01-01 13:23:00.000



(3 行受影响)

create table #tb1

(manno varchar(10),bshtime datetime);

insert into #tb1 

select 'A00258','2011-02-27 07:43:00.000' union all

select 'A00258','2011-02-27 07:52:00.000' union all

select 'A00258','2011-02-27 12:03:00.000' union all

select 'A00258','2011-02-27 12:21:00.000' union all

select 'A00258','2011-02-27 13:23:00.000'



select * from #tb1 where bshtime=

any(select min(bshtime) from #tb1 group by manno,substring(cast(bshtime as nvarchar(30)),1,14))

order by bshtime

create table tb(manno varchar(10),bshtime datetime)

insert into tb select 'A00258','2011-02-27 07:43:00.000'

insert into tb select 'A00258','2011-02-27 07:52:00.000'

insert into tb select 'A00258','2011-02-27 12:03:00.000'

insert into tb select 'A00258','2011-02-27 12:21:00.000'

insert into tb select 'A00258','2011-02-27 13:23:00.000'

go

select * from tb a

where not exists(

select 1 from tb 

where convert(varchar(13),bshtime,120)=convert(varchar(13),a.bshtime,120) and bshtime<a.bshtime)

go

drop table tb

/*

manno      bshtime

---------- -----------------------

A00258     2011-02-27 07:43:00.000

A00258     2011-02-27 12:03:00.000

A00258     2011-02-27 13:23:00.000



(3 行受影响)



*/

with cte_tb as

(select ROW_NUMBER() over(prattion left(bshtime,13)) order by bshtime) id,* from #tb1)



select manno,bshtime from cte_tb where id=1

--2008

create table #tb1

(manno varchar(10),bshtime datetime);



insert into #tb1 values

('A00258','2011-02-27 07:43:00.000'),

('A00258','2011-02-27 07:52:00.000'),

('A00258','2011-02-27 12:03:00.000'),

('A00258','2011-02-27 12:21:00.000'),

('A00258','2011-02-27 13:23:00.000');



;with cte_tb as

(select ROW_NUMBER() over(order by bshtime) id,* from #tb1)



select manno,bshtime from cte_tb where id%2=1



/*

A00258	2011-02-27 07:43:00.000

A00258	2011-02-27 12:03:00.000

A00258	2011-02-27 13:23:00.000

*/

select *

from tb t

where not exists (select 1 from tb where manno = t.manno and convert(varchar(13),bshtime,120) = convert(varchar(13),t.bshtime,120) and bshtime < t.bshtime)