一条语句简单解决“每个Y的最新X”的经典sql语句

复制代码 代码如下:
/******创建表******/
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)NOTNULL,
[Y][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[X][smalldatetime]NOTNULL
)ON[PRIMARY]
GO
--插入数据
InsertINTO[Table](Y,X)values('CCC','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('AAA','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('AAA','2007-03-0303:03:03')
InsertINTO[Table](Y,X)values('BBB','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('BBB','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('BBB','2007-03-0303:03:03')
InsertINTO[Table](Y,X)values('CCC','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('AAA','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('CCC','2007-03-0303:03:03')
InsertINTO[Table](Y,X)values('DDD','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('DDD','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('DDD','2007-03-0303:03:03')
InsertINTO[Table](Y,X)values('EEE','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('EEE','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('EEE','2007-03-0303:03:03')
GO

/******创建表******/
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)NOTNULL,
[Y][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[X][smalldatetime]NOTNULL
)ON[PRIMARY]
GO
--插入数据
InsertINTO[Table](Y,X)values('CCC','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('AAA','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('AAA','2007-03-0303:03:03')
InsertINTO[Table](Y,X)values('BBB','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('BBB','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('BBB','2007-03-0303:03:03')
InsertINTO[Table](Y,X)values('CCC','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('AAA','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('CCC','2007-03-0303:03:03')
InsertINTO[Table](Y,X)values('DDD','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('DDD','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('DDD','2007-03-0303:03:03')
InsertINTO[Table](Y,X)values('EEE','2007-01-0101:01:01')
InsertINTO[Table](Y,X)values('EEE','2007-02-0202:02:02')
InsertINTO[Table](Y,X)values('EEE','2007-03-0303:03:03')

GO解决“每个Y的最新X”经典SQL问题：以下几种方法真是八仙过海
复制代码 代码如下:

SelectID,Y,X
FROM[Table]T1
Where(NOTEXISTS
(Select1
FROM[Table]T2
Where(T2.Y=T1.Y)AND(T2.X>T1.Xor
T2.X=T1.XANDT2.ID>T1.ID)))
/*****************************************************************************/
Select*
FROM[Table]
WhereIDIN
(SelectMAX(T1.ID)
FROM[Table]T1JOIN
(Selecty,MAX(x)x
FROM[Table]
GROUPBYy)T2ONT1.Y=T2.YANDT1.x=T2.x
GROUPBYT1.y)
/*****************************************************************************/
SelectT.ID,T.Y,T.X
FROM[Table]TINNERJOIN
(SelectMAX(T1.ID)ASID
FROM[Table]T1JOIN
(Selecty,MAX(x)x
FROM[Table]
GROUPBYy)T2ONT1.Y=T2.YANDT1.x=T2.x
GROUPBYT1.y)T2ONT.ID=T2.ID
/*****************************************************************************/
Select*
FROM[Table]T1
WhereIDIN
(SelectTOP1ID
FROM[Table]
WhereY=T1.Y
orDERBYXDESC)
/*****************************************************************************/
Select*
FROM[Table]T1
Where(ID=
(SelectTOP1ID
FROM[Table]
WhereY=T1.Y
orDERBYXDESC,IDDESC))
/*****************************************************************************/

/*****************************************************************************/
SelectID,Y,X
FROM[Table]T1
Where(NOTEXISTS
(Select1
FROM[Table]T2
Where(T2.Y=T1.Y)AND(T2.X>T1.Xor
T2.X=T1.XANDT2.ID>T1.ID)))
/*****************************************************************************/
Select*
FROM[Table]
WhereIDIN
(SelectMAX(T1.ID)
FROM[Table]T1JOIN
(Selecty,MAX(x)x
FROM[Table]
GROUPBYy)T2ONT1.Y=T2.YANDT1.x=T2.x
GROUPBYT1.y)
/*****************************************************************************/
SelectT.ID,T.Y,T.X
FROM[Table]TINNERJOIN
(SelectMAX(T1.ID)ASID
FROM[Table]T1JOIN
(Selecty,MAX(x)x
FROM[Table]
GROUPBYy)T2ONT1.Y=T2.YANDT1.x=T2.x
GROUPBYT1.y)T2ONT.ID=T2.ID
/*****************************************************************************/
Select*
FROM[Table]T1
WhereIDIN
(SelectTOP1ID
FROM[Table]
WhereY=T1.Y
orDERBYXDESC)
/*****************************************************************************/
Select*
FROM[Table]T1
Where(ID=
(SelectTOP1ID
FROM[Table]
WhereY=T1.Y
orDERBYXDESC,IDDESC))

/*****************************************************************************
/效率嘛,在不同的字段建立索引速度都不尽相同,使用者见仁见智了.
第一种方法速度在各方面都不错,而且在Y列在建立索引，可以大大优化查询速度。