百分求助！！！！！！！！！！！

[sljz]

table a:

iddatetime idname
2006-4-27 15:00:00 Miller
2006-4-27 16:00:00 Miller
2006-4-27 15:00:00 Jerry
2006-4-27 16:00:00 Jerry
2006-4-28 16:00:00 Miller
2006-4-28 16:00:00 Jerry

我现在希望新建一个表，表中内容为每天小于16:30 并且最接近的一条数据，应该是这样：

table b：

iddatetime idname
2006-4-27 16:00:00 Miller
2006-4-27 16:00:00 Jerry
2006-4-28 16:00:00 Miller
2006-4-28 16:00:00 Jerry

请问应该如何写？谢谢

[xeqtr1982]

--这样？
declare @t table(iddatetime datetime,idname varchar(10))
insert into @t select '2006-4-27 15:00:00' , 'Miller'
union all select '2006-4-27 16:00:00' , 'Miller'
union all select '2006-4-27 15:00:00' , 'Jerry'
union all select '2006-4-27 16:00:00' , 'Jerry'
union all select '2006-4-28 16:00:00' , 'Miller'
union all select '2006-4-28 16:00:00' , 'Jerry'

select * from @t a where exists(select 1 from @t where datediff(second,convert(varchar,iddatetime,108),'16:30:00')>datediff(second,convert(varchar,a.iddatetime,108),'16:30:00'))

[淡蓝冰]

select max(iddatetime) iddatetime ,idname from tablea where hour(iddatetime)<=16 and minute(iddatetime)<30 group by iddatetime into tableb

[sljz]

找不到hour函数

[xeqtr1982]

declare @t table(iddatetime datetime,idname varchar(10))
insert into @t select '2006-4-27 15:00:00' , 'Miller'
union all select '2006-4-27 16:00:00' , 'Miller'
union all select '2006-4-27 15:00:00' , 'Jerry'
union all select '2006-4-27 16:00:00' , 'Jerry'
union all select '2006-4-28 16:00:00' , 'Miller'
union all select '2006-4-28 16:00:00' , 'Jerry'

select * into tb from @t a where exists(select 1 from @t where datediff(second,convert(varchar,iddatetime,108),'16:30:00')>datediff(second,convert(varchar,a.iddatetime,108),'16:30:00'))

select * from tb

drop table tb

[xeqtr1982]

没有hour函数，想取小时，分钟的话，用datepart(hour,date),datepart(minute,date)

[数字化从业者老高]

正解了！

[rivery]

declare @a table(iddatetime datetime,idname varchar(20))
insert into @a
select '2006-4-27 15:00:00','Miller' union
select '2006-4-27 16:00:00','Miller' union
select '2006-4-27 15:00:00','Jerry' union
select '2006-4-27 16:00:00','Jerry' union
select '2006-4-28 16:00:00','Miller' union
select '2006-4-28 16:00:00','Jerry'

select *
from @a a
where not exists (select 1 from @a where convert(varchar(10),iddatetime,120)= convert(varchar(10),a.iddatetime,120) and convert(varchar,iddatetime,108)<'16:30:00' and iddatetime>a.iddatetime and idname=a.idname)
and convert(varchar,iddatetime,108)<'16:30:00'
/*
2006-04-27 16:00:00.000 Jerry
2006-04-27 16:00:00.000 Miller
2006-04-28 16:00:00.000 Jerry
2006-04-28 16:00:00.000 Miller

*/

[拓狼]

select * from A a where convert(varchar,iddatetime,114)<'16:30' and exists(select 1 from A b where convert(varchar(10),a.iddatetime,120)=convert(varchar(10),b.iddatetime,120) and a.iddatetime>b.iddatetime)

[baggio785]

写了一个sql语句,你参考一下

declare @y varchar(10)
declare @m varchar(10)
declare @d varchar(10)
declare @t varchar(20)

set @y = cast(year(getdate()) as varchar(20))
set @m = cast(month(getdate()) as varchar(20))
set @d = cast(day(getdate()) as varchar(20))
set @t = @y+'-'+@m+'-'+@d+' 16:00:00'
print @t

select * from a where datediff(hour,iddatetime,cast(@t as datetime))<1

还不是完全符合你的要求，稍微修改一下吧：）

至于每天都导入，可以用作业来实现

[淡蓝冰]

select * from tablea where iddatetime in(select max(iddatetime) as iddatetime from tablea where DATEPART(hour,iddatetime)<=16 and DATEPART(minute,iddatetime)<30 group by year(iddatetime),month(iddatetime),day(iddatetime)) into cursor tableb

[baggio785]

晕，没发之前还一个人都没有，发完之后这麽多人

惭愧～～

[淡蓝冰]

我開始搞錯了，後面的可以，我試了

[拓狼]

select * from A a where convert(varchar,iddatetime,114)<'16:30' and not exists(select 1 from A b where convert(varchar(10),a.iddatetime,120)=convert(varchar(10),b.iddatetime,120) and a.iddatetime<b.iddatetime)

[拓狼]

select * from A a where convert(varchar,iddatetime,114)<'16:30'
and not exists(select 1 from A b where convert(varchar(10),a.iddatetime,120)=convert(varchar(10),b.iddatetime,120) and a.iddatetime<b.iddatetime and convert(varchar,iddatetime,114)<'16:30' )

[huailairen]

declare @t table(iddatetime datetime,idname varchar(10))
insert into @t select '2006-4-27 15:00:00' , 'Miller'
union all select '2006-4-27 16:00:00' , 'Miller'
union all select '2006-4-27 15:00:00' , 'Jerry'
union all select '2006-4-27 16:00:00' , 'Jerry'
union all select '2006-4-28 16:00:00' , 'Miller'
union all select '2006-4-28 16:00:00' , 'Jerry'


select t.idname,max(iddatetime) as iddatetime
from
(select idname,iddatetime,substring(cast(iddatetime as varchar(20)),1,10) as day
from @t ) t
where datepart(hh,t.iddatetime)*100+datepart(mi,t.iddatetime)<1630
group by t.idname,t.day


结果：
（所影响的行数为 6 行）

idname iddatetime
---------- ------------------------------------------------------
Jerry 2006-04-27 16:00:00.000
Miller 2006-04-27 16:00:00.000
Jerry 2006-04-28 16:00:00.000
Miller 2006-04-28 16:00:00.000

（所影响的行数为 4 行）

[liangpei2008]

学习

[sxdoujg]

up

[nekiy]

xeqtr1982(ShaKa) 你是怎么来判断哪天的呢？？

[nekiy]

xeqtr1982(ShaKa)我对你的语句测试结果
我的测试表
2006-04-27 15:00:00.000 Miller
2006-04-27 16:54:00.000 Miller
2006-04-27 17:23:00.000 Jerry
2006-04-27 12:11:00.000 Jerry
2006-04-28 16:48:00.000 Miller
2006-04-28 16:10:00.000 Jerry
2006-04-28 16:28:00.000 Miller
用你的得出的结果
2006-04-27 15:00:00.000 Miller
2006-04-27 16:54:00.000 Miller
2006-04-27 17:23:00.000 Jerry
2006-04-28 16:48:00.000 Miller
2006-04-28 16:10:00.000 Jerry
2006-04-28 16:28:00.000 Miller
开始我看得很晕，因为你一直都没有用到具体的日期，我以为是那个convert(varchar,iddatetime,108)隐式的记录了日期呢，我单独运行了一下也不行！！
哎~~ 高手回答问题也替我们这些菜鸟考虑一下啊，本来我们懂的就少，再由误解~~~ ~~~

[xeqtr1982]

刚才不在，不好意思

我看了一下，应该加上convert(varchar(10),iddatetime,120)=convert(varchar(10),a.iddatetime,120).可能是由于数据原因，刚好结果一样．没有发现．谢谢．