复制代码 代码如下: /******创建表******/ ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[Table]')andOBJECTPROPERTY(id,N'IsUserTable')=1) droptable[dbo].[Table] GO CreateTABLE[dbo].[Table]( [ID][int]IDENTITY(1,1)NOTNUL
复制代码 代码如下:/****** 创建表 ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table] GO Create TABLE [dbo].[Table] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Y] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [X] [smalldatetime] NOT NULL ) ON [PRIMARY] GO --插入数据 Insert INTO [Table](Y, X) values('CCC', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('AAA', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('AAA', '2007-03-03 03:03:03') Insert INTO [Table](Y, X) values('BBB', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('BBB', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('BBB', '2007-03-03 03:03:03') Insert INTO [Table](Y, X) values('CCC', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('AAA', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('CCC', '2007-03-03 03:03:03') Insert INTO [Table](Y, X) values('DDD', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('DDD', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('DDD', '2007-03-03 03:03:03') Insert INTO [Table](Y, X) values('EEE', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('EEE', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('EEE', '2007-03-03 03:03:03') GO /****** 创建表 ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table] GO Create TABLE [dbo].[Table] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Y] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [X] [smalldatetime] NOT NULL ) ON [PRIMARY] GO --插入数据 Insert INTO [Table](Y, X) values('CCC', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('AAA', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('AAA', '2007-03-03 03:03:03') Insert INTO [Table](Y, X) values('BBB', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('BBB', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('BBB', '2007-03-03 03:03:03') Insert INTO [Table](Y, X) values('CCC', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('AAA', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('CCC', '2007-03-03 03:03:03') Insert INTO [Table](Y, X) values('DDD', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('DDD', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('DDD', '2007-03-03 03:03:03') Insert INTO [Table](Y, X) values('EEE', '2007-01-01 01:01:01') Insert INTO [Table](Y, X) values('EEE', '2007-02-02 02:02:02') Insert INTO [Table](Y, X) values('EEE', '2007-03-03 03:03:03') GO解决“每个Y的最新X”经典SQL问题：以下几种方法真是八仙过海 复制代码 代码如下:Select ID, Y, X FROM [Table] T1 Where (NOT EXISTS (Select 1 FROM [Table] T2 Where (T2.Y = T1 .Y) AND (T2.X > T1 .X or T2.X = T1 .X AND T2.ID > T1 .ID))) /*****************************************************************************/ Select * FROM [Table] Where ID IN (Select MAX(T1.ID) FROM [Table] T1 JOIN (Select y, MAX(x) x FROM [Table] GROUP BY y) T2 ON T1.Y = T2.Y AND T1.x = T2.x GROUP BY T1.y) /*****************************************************************************/ Select T .ID, T .Y, T .X FROM [Table] T INNER JOIN (Select MAX(T1.ID) AS ID FROM [Table] T1 JOIN (Select y, MAX(x) x FROM [Table] GROUP BY y) T2 ON T1.Y = T2.Y AND T1.x = T2.x GROUP BY T1.y) T2 ON T .ID = T2.ID /*****************************************************************************/ Select * FROM [Table] T1 Where ID IN (Select TOP 1 ID FROM [Table] Where Y = T1.Y orDER BY X DESC) /*****************************************************************************/ Select * FROM [Table] T1 Where (ID = (Select TOP 1 ID FROM [Table] Where Y = T1.Y orDER BY X DESC, ID DESC)) /*****************************************************************************/ /*****************************************************************************/ Select ID, Y, X FROM [Table] T1 Where (NOT EXISTS (Select 1 FROM [Table] T2 Where (T2.Y = T1 .Y) AND (T2.X > T1 .X or T2.X = T1 .X AND T2.ID > T1 .ID))) /*****************************************************************************/ Select * FROM [Table] Where ID IN (Select MAX(T1.ID) FROM [Table] T1 JOIN (Select y, MAX(x) x FROM [Table] GROUP BY y) T2 ON T1.Y = T2.Y AND T1.x = T2.x GROUP BY T1.y) /*****************************************************************************/ Select T .ID, T .Y, T .X FROM [Table] T INNER JOIN (Select MAX(T1.ID) AS ID FROM [Table] T1 JOIN (Select y, MAX(x) x FROM [Table] GROUP BY y) T2 ON T1.Y = T2.Y AND T1.x = T2.x GROUP BY T1.y) T2 ON T .ID = T2.ID /*****************************************************************************/ Select * FROM [Table] T1 Where ID IN (Select TOP 1 ID FROM [Table] Where Y = T1.Y orDER BY X DESC) /*****************************************************************************/ Select * FROM [Table] T1 Where (ID = (Select TOP 1 ID FROM [Table] Where Y = T1.Y orDER BY X DESC, ID DESC)) /*****************************************************************************/效率嘛,在不同的字段建立索引速度都不尽相同,使用者见仁见智了. 第一种方法速度在各方面都不错,而且在Y列在建立索引，可以大大优化查询速度。
