再次求助！！！！！！！

[sljz]

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

现在需要列出每天最大记录和最小记录存成两张表

table A（最小记录）
iddatetime idname
2006-4-27 15:00:00 Miller
2006-4-27 15:00:00 Jerry
2006-4-28 16:00:00 Miller
2006-4-28 16:00:00 Jerry

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

谢谢

[mugua604]

select top 1 iddatetime,idname into #tableA from 表1
group by iddatetime,idname
order by idname

select top 1 iddatetime,idname into #tableB from 表1
group by iddatetime,idname
order by idname dese

[WangZWang]

--取小
Insert into A(iddatetime,idname)
select iddatetime,idname
from tbl as a
where iddatetime=(select top 1 iddatetime from tbl where idname=a.idname
and datediff(day,a.iddatetime,iddatetime)=0 order by iddatetime)

--取大
Insert into B(iddatetime,idname)
select iddatetime,idname
from tbl as a
where iddatetime=(select top 1 iddatetime from tbl where idname=a.idname
and datediff(day,a.iddatetime,iddatetime)=0 order by iddatetime desc)

[mugua604]

desc

[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 not exists(select 1 from @t where convert(varchar(10),iddatetime,120)=convert(varchar(10),a.iddatetime,120) and convert(varchar,iddatetime,108)>convert(varchar,a.iddatetime,108))


select * from @t a where not exists(select 1 from @t where convert(varchar(10),iddatetime,120)=convert(varchar(10),a.iddatetime,120) and convert(varchar,iddatetime,108)<convert(varchar,a.iddatetime,108))