求查询语句

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

...全文
121 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
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)

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧