我的数据表有3个字段,第1个为自动增长,第2个为日期时间,第3个为数字
如下数据:
1 2008-06-30 17:30:47.300 701
2 2008-06-30 17:32:05.520 698
3 2008-06-30 17:32:07.707 700
4 2008-06-30 17:32:22.860 699
5 2008-06-30 17:32:24.720 699
8 2008-06-30 17:32:30.190 702
10 2008-06-30 17:33:00.580 705
11 2008-06-30 17:33:47.720 712
13 2008-06-30 17:34:08.720 709
15 2008-06-30 17:35:01.360 717
18 2008-06-30 17:38:17.627 734
19 2008-06-30 17:38:26.520 706
20 2008-06-30 17:38:26.690 707
21 2008-06-30 17:38:42.643 710
22 2008-06-30 17:38:50.893 712
23 2008-06-30 17:39:12.533 713
24 2008-06-30 17:40:53.393 714
25 2008-06-30 17:41:10.503 712
26 2008-06-30 17:41:20.770 707我现在,要用一个SQL语句列出以小时为分钟为单位的数据,取数字最大值
比如id为18,19,20,21,22五条数据都是 17:38分,而我只要获取id为18的数据,因为他的数字最大
如下数据:
1 2008-06-30 17:30:47.300 701
2 2008-06-30 17:32:05.520 698
3 2008-06-30 17:32:07.707 700
4 2008-06-30 17:32:22.860 699
5 2008-06-30 17:32:24.720 699
8 2008-06-30 17:32:30.190 702
10 2008-06-30 17:33:00.580 705
11 2008-06-30 17:33:47.720 712
13 2008-06-30 17:34:08.720 709
15 2008-06-30 17:35:01.360 717
18 2008-06-30 17:38:17.627 734
19 2008-06-30 17:38:26.520 706
20 2008-06-30 17:38:26.690 707
21 2008-06-30 17:38:42.643 710
22 2008-06-30 17:38:50.893 712
23 2008-06-30 17:39:12.533 713
24 2008-06-30 17:40:53.393 714
25 2008-06-30 17:41:10.503 712
26 2008-06-30 17:41:20.770 707我现在,要用一个SQL语句列出以小时为分钟为单位的数据,取数字最大值
比如id为18,19,20,21,22五条数据都是 17:38分,而我只要获取id为18的数据,因为他的数字最大
select convert(varchar(12),fld2,112)+convert(varchar(5),fld3,108),max(fld3)
from table
group by convert(varchar(12),fld2,112)+convert(varchar(5),fld3,108)
insert into #T
select 1,'2008-06-30 17:30:47.300',701 union all
select 2,'2008-06-30 17:32:05.520',698 union all
select 3,'2008-06-30 17:32:07.707',700 union all
select 4,'2008-06-30 17:32:22.860',699 union all
select 5,'2008-06-30 17:32:24.720',699 union all
select 8,'2008-06-30 17:32:30.190',702 union all
select 11,'2008-06-30 17:33:00.580',705 union all
select 10,'2008-06-30 17:33:47.720',712 union all
select isnull((Select AID
From #T #T1
Where #T1.NoInfo=max(#T.NoInfo) And Convert(Varchar(16),#T1.TimeInfo,121)=Convert(Varchar(16),#T.TimeInfo,121)),'') AID,Convert(Varchar(16),TimeInfo,121),max(NoInfo)
From #T
Group by Convert(Varchar(16),TimeInfo,121)Drop table #T
(id int,times datetime,num int)insert into tb2 select 1,'2008-06-30 17:30:47.300' ,701
insert into tb2 select 2,'2008-06-30 17:32:05.520' ,698
insert into tb2 select 3, '2008-06-30 17:32:07.707', 700
insert into tb2 select 4, '2008-06-30 17:32:22.860', 699
insert into tb2 select 5, '2008-06-30 17:32:24.720', 699
insert into tb2 select 8, '2008-06-30 17:32:30.190', 702
insert into tb2 select 10, '2008-06-30 17:33:00.580' ,705
insert into tb2 select 11, '2008-06-30 17:33:47.720' ,712
insert into tb2 select 13, '2008-06-30 17:34:08.720' ,709
insert into tb2 select 15, '2008-06-30 17:35:01.360' ,717
insert into tb2 select 18, '2008-06-30 17:38:17.627' ,734
insert into tb2 select 19, '2008-06-30 17:38:26.520' ,706
insert into tb2 select 20, '2008-06-30 17:38:26.690' ,707
insert into tb2 select 21, '2008-06-30 17:38:42.643' ,710
insert into tb2 select 22, '2008-06-30 17:38:50.893' ,712
insert into tb2 select 23, '2008-06-30 17:39:12.533' ,713
insert into tb2 select 24, '2008-06-30 17:40:53.393' ,714
insert into tb2 select 25, '2008-06-30 17:41:10.503' ,712
insert into tb2 select 26, '2008-06-30 17:41:20.770' ,707 select * from tb2 a
where not exists(select 1 from tb2 b where convert(varchar(16),a.times,120)=convert(varchar(16),b.times,120) and a.num<b.num)
datepart(hour,dtime)= datepart(hour,a.dtime) and a.id<id )
(select 日期 ,max(数字) as 数字 from (
select substring(cast(times as varchar(50)),1,17) as 日期,max(num) as 数字 from TableTemp
group by times)a
group by 日期)b
where substring(cast(a.times as varchar(50)),1,17)=b.日期 and a.num=b.数字
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Col1] int,[Col2] Datetime,[Col3] int)
Insert #T
select 1,'2008-06-30 17:30:47.300',701 union all
select 2,'2008-06-30 17:32:05.520',698 union all
select 3,'2008-06-30 17:32:07.707',700 union all
select 4,'2008-06-30 17:32:22.860',699 union all
select 5,'2008-06-30 17:32:24.720',699 union all
select 8,'2008-06-30 17:32:30.190',702 union all
select 10,'2008-06-30 17:33:00.580',705 union all
select 11,'2008-06-30 17:33:47.720',712 union all
select 13,'2008-06-30 17:34:08.720',709 union all
select 15,'2008-06-30 17:35:01.360',717 union all
select 18,'2008-06-30 17:38:17.627',734 union all
select 19,'2008-06-30 17:38:26.520',706 union all
select 20,'2008-06-30 17:38:26.690',707 union all
select 21,'2008-06-30 17:38:42.643',710 union all
select 22,'2008-06-30 17:38:50.893',712 union all
select 23,'2008-06-30 17:39:12.533',713 union all
select 24,'2008-06-30 17:40:53.393',714 union all
select 25,'2008-06-30 17:41:10.503',712 union all
select 26,'2008-06-30 17:41:20.770',707
Go
Select * from #T t where not exists(select 1 from #T where convert(varchar(5),[Col2],8)=convert(varchar(5),t.[Col2],8) and [Col3]>t.[Col3])
(19 行受影响)
Col1 Col2 Col3
----------- ----------------------- -----------
1 2008-06-30 17:30:47.300 701
8 2008-06-30 17:32:30.190 702
11 2008-06-30 17:33:47.720 712
13 2008-06-30 17:34:08.720 709
15 2008-06-30 17:35:01.360 717
18 2008-06-30 17:38:17.627 734
23 2008-06-30 17:39:12.533 713
24 2008-06-30 17:40:53.393 714
25 2008-06-30 17:41:10.503 712(9 行受影响)
insert into #T
select 1,'2008-06-30 17:30:47',701 union all
select 2,'2008-06-30 17:32:05',698 union all
select 3,'2008-06-30 17:32:07',700 union all
select 4,'2008-06-30 17:32:22',699 union all
select 5,'2008-06-30 17:32:24',699 union all
select 8,'2008-06-30 17:32:30',702 union all
select 11,'2008-06-30 17:33:00',705 union all
select 10,'2008-06-30 17:33:47',712 select AID,TimeInfo,NoInfo from #T TT where not exists (select * from #T where left(convert(nvarchar,TimeInfo,120),16)=left(convert(nvarchar,TT.TimeInfo,120),16) and NoInfo<TT.NoInfo)