if Object_id('student') is not null
drop table student
create table student
(
id int primary key identity(1,1),
class nvarchar(50),
Ftime date,
age int,
workID int
)
insert student (class,Ftime,age,workID)
select 'sdf','2010-07-23',12,1 union all
select 'sdfg','2010-07-21',12,1 union all
select 'dfgfgd','2010-07-22',12,1 union all
select 'dfg','2010-07-21',12,1 union all
select 'dfg','2010-07-24',12,1 union all
select 'sdfg','2010-07-21',12,2 union all
select 'dfgfgd','2010-07-22',12,2 union all
select 'dfg','2010-07-21',12,2 union all
select 'dfg','2010-07-24',12,2 union all
select 'hfgh','2010-07-23',12,2
if OBJECT_ID('work') is not null
drop table work
create table work
(
id int primary key identity(1,1),
name nvarchar(50)
)
insert work(name)
select 'sdfs' union all
select 'sdfs' union all
select 'sdfs' union all
select 'sdfs' 我要选出student表中的最新的数据
选出的结果应该是'dfg','2010-07-24',12,1
'dfg','2010-07-24',12,2
这两条数据
where ftime=(select max(ftime) from student where workID=t.workID)
drop table student
create table student
(
id int primary key identity(1,1),
class nvarchar(50),
Ftime datetime, ----->datetime
age int,
workID int
)
insert student (class,Ftime,age,workID)
select 'sdf','2010-07-23',12,1 union all
select 'sdfg','2010-07-21',12,1 union all
select 'dfgfgd','2010-07-22',12,1 union all
select 'dfg','2010-07-21',12,1 union all
select 'dfg','2010-07-24',12,1 union all
select 'sdfg','2010-07-21',12,2 union all
select 'dfgfgd','2010-07-22',12,2 union all
select 'dfg','2010-07-21',12,2 union all
select 'dfg','2010-07-24',12,2 union all
select 'hfgh','2010-07-23',12,2 select * from student t where (select count(*) from student where Ftime>t.ftime)<1id class Ftime age workID
----------- -------------------------------------------------- ----------------------- ----------- -----------
5 dfg 2010-07-24 00:00:00.000 12 1
9 dfg 2010-07-24 00:00:00.000 12 2(2 行受影响)
if Object_id('student') is not null
drop table student
go
create table student
(
id int primary key identity(1,1),
class nvarchar(50),
Ftime date,
age int,
workID int
)
insert student (class,Ftime,age,workID)
select 'sdf','2010-07-23',12,1 union all
select 'sdfg','2010-07-21',12,1 union all
select 'dfgfgd','2010-07-22',12,1 union all
select 'dfg','2010-07-21',12,1 union all
select 'dfg','2010-07-24',12,1 union all
select 'sdfg','2010-07-21',12,2 union all
select 'dfgfgd','2010-07-22',12,2 union all
select 'dfg','2010-07-21',12,2 union all
select 'dfg','2010-07-24',12,2 union all
select 'hfgh','2010-07-23',12,2 select *
from student a
where Ftime= (select MAX(Ftime) from student )id class Ftime age workID
5 dfg 2010-07-24 12 1
9 dfg 2010-07-24 12 2
因为student表中的WorkID=1和WorkID=2的Ftime正好都是7-24 所以另两位的语法也能选出结果,
但是当这两个的Ftime不是相同时就不对了。