求查询语句

wuyuwangcom 2011-02-28 06:49:05
如何把以下表查询
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

...全文
69 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Linares 2011-03-01
[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 被你滥用了
回复
Shawn 2011-03-01
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
*/
回复
ljking0731 2011-03-01

--建表
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 行受影响)
回复
liang145 2011-03-01

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

是不是分组求最小的时间?
回复
小叔 2011-02-28
顶二楼的!
回复
-晴天 2011-02-28
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 行受影响)

*/

回复
快溜 2011-02-28
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
回复
vivai2010 2011-02-28

--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
*/
回复
AcHerat 2011-02-28

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)
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-02-28 06:49
社区公告
暂无公告