复制代码 代码如下:
/****** 创建表 ******/
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列在建立索引，可以大大优化查询速度。