求查询语句

[wuyuwangcom]

如何把以下表查询
manno bshtime
-------- ------------------------------------------------------
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

（所影响的行数为 5 行）
得到：
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

[AcHerat]

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)

[vivai2010]

--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
*/

[快溜]

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

[-晴天]

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 行受影响)

*/

[小叔]

顶二楼的！

[liang145]

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

是不是分组求最小的时间？

[ljking0731]

--建表
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 行受影响)

[Shawn]

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
*/

[Linares]

[Quote=引用 9 楼 wwwwgou 的回复:]

SQL code
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
sel……
[/Quote]

apply 被你滥用了