22,300
社区成员




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)