这个SQL怎么写?按某个字段分组求最后面的值.

SQL code

F1                          F2       F32010-01-23 00:00:00    128525    3380.02010-01-23 00:00:00    128526    3381.02010-01-23 00:00:00    128529    3384.02010-01-25 00:00:00    128530    3383.02010-01-25 00:00:00    128531    3382.02010-01-25 00:00:00    128534    3379.02010-03-04 00:00:00    128535    3378.02010-03-04 00:00:00    128536    3377.02010-03-04 00:00:00    128539    3374.02010-03-25 00:00:00    128540    3373.02010-03-25 00:00:00    128541    3372.02010-03-25 00:00:00    128542    3371.02010-03-25 00:00:00    128565    3372.02010-03-25 00:00:00    128566    3373.0

是按F2排序的同时F1肯定也是排好的,可以看出F2是随F1渐渐变大的
需要求的结果如下:

SQL code

2010-01-23 00:00:00    128529    3384.02010-01-25 00:00:00    128534    3379.02010-03-04 00:00:00    128539    3374.02010-03-25 00:00:00    128566    3373.0

[解决办法]

SQL code

select * from tb t where F2=(select max(F2) from tb where f1=t.f1)
[解决办法]
SQL code
select f1, max(f2),max(f3)from tab group by f1
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb] (F1 datetime,F2 int,F3 numeric(5,1))insert into [tb]select '2010-01-23 00:00:00',128525,3380.0 union allselect '2010-01-23 00:00:00',128526,3381.0 union allselect '2010-01-23 00:00:00',128529,3384.0 union allselect '2010-01-25 00:00:00',128530,3383.0 union allselect '2010-01-25 00:00:00',128531,3382.0 union allselect '2010-01-25 00:00:00',128534,3379.0 union allselect '2010-03-04 00:00:00',128535,3378.0 union allselect '2010-03-04 00:00:00',128536,3377.0 union allselect '2010-03-04 00:00:00',128539,3374.0 union allselect '2010-03-25 00:00:00',128540,3373.0 union allselect '2010-03-25 00:00:00',128541,3372.0 union allselect '2010-03-25 00:00:00',128542,3371.0 union allselect '2010-03-25 00:00:00',128565,3372.0 union allselect '2010-03-25 00:00:00',128566,3373.0select * from tb t where F2=(select max(F2) from tb where f1=t.f1)/*F1                      F2          F3----------------------- ----------- ---------------------------------------2010-03-25 00:00:00.000 128566      3373.02010-03-04 00:00:00.000 128539      3374.02010-01-25 00:00:00.000 128534      3379.02010-01-23 00:00:00.000 128529      3384.0*/
[解决办法]
SQL code
SELECT * FROM TB T WHERE F2=(SELECT MAX(F2) FROM TB WHERE DATEDIFF(DD,F1,T.F1)=0)
[解决办法]
SQL code
select *from tb twhere not exsits(select 1 from tb where  f1=t.f1 and F3>t.F3)
[解决办法]
SQL code
--处理表重复记录(查询和删除)/******************************************************************************************************************************************************1、Num、Name相同的重复值记录,没有大小关系只保留一条2、Name相同,ID有大小关系时,保留大或小其中一个记录整理人：中国风(Roy)日期:2008.06.06******************************************************************************************************************************************************/--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)--> --> (Roy)生成 if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))Insert #Tselect 1,N'A',N'A1' union allselect 2,N'A',N'A2' union allselect 3,N'A',N'A3' union allselect 4,N'B',N'B1' union allselect 5,N'B',N'B2'Go--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2方法1:Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2:select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID方法3:select * from #T a where ID=(select min(ID) from #T where Name=a.Name)方法4:select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)方法6:select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0方法7:select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)方法8:select * from #T a where ID!>all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用):select * from #T a where ID in(select min(ID) from #T group by Name)--SQL2005:方法10:select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1生成结果:/*ID          Name Memo----------- ---- ----1           A    A14           B    B1(2 行受影响)*/--II、Name相同ID最大的记录,与min相反:方法1:Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)方法2:select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID方法3:select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID方法4:select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)方法6:select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0方法7:select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)方法8:select * from #T a where ID!<all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用):select * from #T a where ID in(select max(ID) from #T group by Name)--SQL2005:方法10:select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1生成结果2:/*ID          Name Memo----------- ---- ----3           A    A35           B    B2(2 行受影响)*/ 
 
[解决办法]
SQL code
select * from tb t where F2=(select max(F2) from tb where f1=t.f1)select * from tb twhere not exists(select 1 from tb where f1=t.f1 and F2>t.F2)
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb] (F1 datetime,F2 int,F3 numeric(5,1))insert into [tb]select '2010-01-23 00:00:00',128525,3380.0 union allselect '2010-01-23 00:00:00',128526,3381.0 union allselect '2010-01-23 00:00:00',128529,3384.0 union allselect '2010-01-25 00:00:00',128530,3383.0 union allselect '2010-01-25 00:00:00',128531,3382.0 union allselect '2010-01-25 00:00:00',128534,3379.0 union allselect '2010-03-04 00:00:00',128535,3378.0 union allselect '2010-03-04 00:00:00',128536,3377.0 union allselect '2010-03-04 00:00:00',128539,3374.0 union allselect '2010-03-25 00:00:00',128540,3373.0 union allselect '2010-03-25 00:00:00',128541,3372.0 union allselect '2010-03-25 00:00:00',128542,3371.0 union allselect '2010-03-25 00:00:00',128565,3372.0 union allselect '2010-03-25 00:00:00',128566,3373.0select * from tb t where F2=(select max(F2) from tb where f1=t.f1)select * from tb twhere not exists(select 1 from tb where f1=t.f1 and F2>t.F2)/*F1                      F2          F3----------------------- ----------- ---------------------------------------2010-01-23 00:00:00.000 128529      3384.02010-01-25 00:00:00.000 128534      3379.02010-03-04 00:00:00.000 128539      3374.02010-03-25 00:00:00.000 128566      3373.0(4 行受影响)*/
[解决办法]
SQL code
/*    *************************************    *   T-MAC 小编                      *    *        -->努力成长中              *    *        -->梦想DBA                 *    **************************************/if OBJECT_ID('tb') is not null    drop table tb gocreate table tb (F1 datetime ,F2 INT,F3 DECIMAL(18,1))insert tb select '2010-01-23 00:00:00',    128525  ,  3380.0 UNION SELECT '2010-01-23 00:00:00',    128526  ,  3381.0 UNION SELECT '2010-01-23 00:00:00',    128529  ,  3384.0 UNION SELECT '2010-01-25 00:00:00',    128530  ,  3383.0 UNION SELECT '2010-01-25 00:00:00',    128531  ,  3382.0 UNION SELECT '2010-01-25 00:00:00',    128534  ,  3379.0 UNION SELECT '2010-03-04 00:00:00',    128535  ,  3378.0 UNION SELECT '2010-03-04 00:00:00' ,   128536  ,  3377.0 UNION SELECT '2010-03-04 00:00:00',    128539  ,  3374.0 UNION SELECT '2010-03-25 00:00:00',    128540  ,  3373.0 UNION SELECT '2010-03-25 00:00:00',    128541  ,  3372.0 UNION SELECT '2010-03-25 00:00:00',    128542   , 3371.0 UNION SELECT '2010-03-25 00:00:00',    128565   , 3372.0 UNION SELECT '2010-03-25 00:00:00' ,   128566  ,  3373.0GOSELECT *FROM tb K WHERE NOT EXISTS(SELECT * FROM tb WHERE K.F1=F1 AND K.F2<F2)order by F1/*F1                      F2          F3----------------------- ----------- ---------------------------------------2010-01-23 00:00:00.000 128529      3384.02010-01-25 00:00:00.000 128534      3379.02010-03-04 00:00:00.000 128539      3374.02010-03-25 00:00:00.000 128566      3373.0*/